Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Math problem (Office XP)

    Having problems doing a simple math problem. How many sold from how many shipped to show how many remaining.
    I have a master table to record the sales to different clients.

    Table dbtask:
    Record 23 - Client ABC, sold 500 bolts. Date, time, name

    Table Area Data:
    In detail table I record the details of the sale
    Record 23 - Store 1 # to deliver 100 Shipped out 30
    Store 2 # to deliver 100 Shipped out 20
    Store 3 # to deliver 100 Shipped out 10
    Store 4 # to deliver 100 Shipped out 0
    Store 5 # to deliver 100 Shipped out 0

    I have a query that has two tables; 1. Dbtask 2,Area Data
    From dbtask, I use field A (record number) and field ak - amount sold (100)

    From the second table I use field Done (shipped out). Where total: = SUM. The other two total fields are Group by. I want to add another field in Area Data table that will how how many are still left to ship for each store.

    I need a new field in the query that will show me how many I have to ship for each store
    Store 1 # to deliver 100 Shipped out 30 70
    Store 2 # to deliver 100 Shipped out 20 80
    Store 3 # to deliver 100 Shipped out 10 90
    Store 4 # to deliver 100 Shipped out 0 100
    Store 5 # to deliver 100 Shipped out 0 100

    What I want is out of the 900 sold to the Client I have shipped only 60. So I still need to ship 440

    Form to show:
    Client Sold Shipped Still to be shipped
    ABC 900 60 440

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Math problem (Office XP)

    If 60 from 900 leaves 440, you *do* have a problem with math <g>

    Why do you want to store the numbers still to be shipped in a table field? In general, storing calculated values in a table is a recipe for headache. (There are some exceptions, I know...) Usually, it is enough to do the calculations in a query and use that as a record source for forms or reports.

    You can calculate the numbers you want by creating several queries. I have attached a zipped Access 97 database that demonstrates this. And if you insist, you can still create an update query to fill the extra field in the table.

    HTH, Hans
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Math problem (Office XP)

    That 900 should have read 500. Somebody has slippery fingers.

    As for storing the calculations, I agree with you. But, the Boss wants the totals on the table so he can see the numbers without having to do reports or other things.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Math problem (Office XP)

    Have you told your boss that he is asking to look at incorrect totals? If someone goes into the tables in spreadsheet mode to fix an error in one of the fields that the total is calculated from, and doesn't change the total, the total will be WRONG. This is asking for trouble.

    Is your boss going to be looking directly at the tables in Spreadsheet mode? He should be looking at the data on forms based on queries that calculate the totals.
    Legare Coleman

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Math problem (Office XP)

    As you might discover from browsing through this forum, we don't have much patience with bosses who try to tell the developer how to do his job. If you boss made that request, he definitely does NOT belong in the table at all. At the very least, give him a query to look at and build a form to enable him to open the query.
    Charlotte

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Math problem (Office XP)

    Your comments are kinder that what we are saying to each other here. As it stands, he will look into the db at the most is 5 times within a year. This is just like the new project that we started. We have to convert all Access applications into DB2 on the Mainframe and he has only given us two weeks to learn and produce a working application that he wants demo'd this Tuesday.

    No problem you say.... Try having two people working on it. The first has had a "Basic Intro to VB6" class and the other wants to go to the class. THese are the experts. Did I forget to mention that we only have 1 copy of the software on my machine. LOL Corporate American... you just have to love it.

    But to close this up. The sample that Hans sent is just what I need. Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •