How would I...

[Question Boy]

How would I build a query that would sum up the total per itemid for 2 table
and then return the substraction value. Let me explain

I have table1 with 2 fields (id and qty) and table2 with 2 fields (id and qty)

I need to perform the summation per id of each and then return the vaule for
each id substracting the value from table2 from table1

Say table1 has
id=1,qty=12
id=1, qty=3

table2 has
id=1,qty=4
id=1,qty=6

I need a query that will return (12+3)-(4+6)
id=1,qty=5
for each id

Thank you for your guidance,

QB

 

[Jeff Boyce]

One approach might be to create one query that sums the qty for table1,
another that sums qty for table2, and a third that you use to take the
difference.

Take a look at Totals queries...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

Here is the SQL of a query to do it ---
SELECT [Table 1].id, Sum([Table 1].QTY) AS SumOfQTY, Sum([Table 2].QTY) AS
SumOfQTY1, Sum([Table 1].[QTY])-Sum([Table 2].[QTY]) AS Results
FROM [Table 1] INNER JOIN [Table 2] ON [Table 1].id = [Table 2].id
GROUP BY [Table 1].id;

Open a new query in design view, click on menu VIEW -SQL View, paste the
post in the window. Remove any added return - There should only be one
before FROM and GROUP BY.