Calculations in MS Access

[reuben]

Hi, I have 2 tables each having 3 fields - Product Name, Version and
Qty...but the Products are not the same. I need to Subtract Table 1
from Table 2 ie....If I want to check how much I have left from ITEMX,
then the database much search from table one for product ITEMX take its
QTY and subtract them from the QTY of ITEMX found in table 2...how can
I do this??? Can it be done?

Thanks.

 

[Steve Schapel]

Reuben,

Assuming the Product Name field is what can be used to uniquely identify
each record in both tables, and assuming you only want this process to
apply in cases where a Product appears in both tables, then... Make a
Query that includes both tables, and join them on the Product Name field
of each. Then, in the Field row of a blank column in the query design
grid, type somthing like this...
HowMuchLeft: [Table2].[Qty]-[Table1].[Qty]

Having said that, and without knowing much at all about your data, it
may be worth considering whather combining the data into a single table
may be a more appropriate design.