Simple calculation help

[plasma]

Well, I hope this is simple, but for some strange reason, very difficult for me.

I'm having issues setting up a calculation between 2 tables. for example:

Table1 ID Quantity


Table2 TransID Used


The "Quantity" from Table1 is a set number but the "Used" will change. all I want to do is have a Form and Report that will subtract the "Used" from Table2 from "Quantity" from Table1.

I've tried: =Table1.Quantity - Table2.Used
=Quantity - Used

what am I doing wrong? all my attempts have failed, even though I have setup a relationship between the 2 tables.

any idea?

thx

 

[Dennisk]

you need to use a query to gather the data from the two tables.
then create a calculated field either in the query, or on the form or report.

 

[ajetrumpet]

all I want to do is have a Form and Report that will subtract the "Used" from Table2 from "Quantity" from Table1.

The query you create for this should be the source for your report too.
Type your expression into the name line of the new field in the query grid along with AS "appropriate name for the field"

Something like...

[Quantity]-[Used] AS "new field name"

 

[plasma]

Thank you, that worked. But I have 1 more question. Say that I have multiple entries for "Quantity" and for "Used", for example:

Date Product Used
07/30/07 X 1
07/30/07 X 2
07/30/07 Y 1

and bascially I want it to add up all that was used for Product "X" then subtract it from a starting Quantity.

thx again for the help

 

[ajetrumpet]

I won't ask why you have more than one record for each product, but you shouldn't have. You've probably got it setup in a such a way that it would be painstaking for you to restructure.

If that is the case, assuming you have a DB with multiple records per product, try a new field in your query with a SUM function.
Make sure to specify product criteria.

I would also look up the "DSum" function and the "SUM" function for information on these two actions in the Access help menu....

 

[plasma]

thx, i was just getting ready to post that I was able to get that figured out (fumbling through the settings). i still have a few more issues. once i try and work through them, I'll post here for more help if needed.

thx again.

 

[ajetrumpet]

no problem, good luck to ya...