1. 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.

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. 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

3. 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. 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.

5. 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.

6. 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
•