Are there any Microsoft Access gurus in the house?

[Squatch]

I am pretty damned new to Access and have the following problem.

I have a number of tables, each arranged thusly (sample data only):

Table 1:
ID Qty
1 9
2 8
3 14

Table 2:
ID Qty
1 5
3 3
6 3

Table 3:
ID Qty
1 4
6 1
7 2

How do I build a query so that the resultant table sums each value for each ID in each table:

Query results:
ID Qty
1 18
2 8
3 17
6 4
7 2

Any help is greatly appreciated and will earn you a free beer the next time you're in DC (if the query works, that is).

Brian

[mainegreen]

1. You need to do a UNION type sub query and then sum the union.

Example

SELECT ID, Qty FROM Table_1
UNION ALL
SELECT ID, Qty FROM Table_2
UNION ALL
SELECT ID, Qty FROM Table_3

This is your base query. Access can not do this in the visual design mode so you have to switch to SQL view (if you need help doing this, feel free to ask). Save this query as 'source_query' or some sensible name

Now build another query based on the query you just created. This query is a grouped query. Do this by clicking the mathmatical greek letter 'E' thing to get the Totals row in the designer. Then you should group by the ID and SUM the Qty column.

The SQL will look something like this:

Select ID, SUM(Qty) as Qty_Summed
FROM source_query
GROUP BY ID



Does this help?

[Squatch]

2. That makes perfect sense. Thanks a bunch!

[Squatch]

3. It worked like a charm.

I am working with a data set with nearly 600,000 rows of data in about 40 tables and I just needed to sum the unique ids. Thanks a bunch for your help!

[Orrex]

4. What you don't realize

Is that every time you run that query, you transfer $5.00 from your bank account to Mainegreen's.

It's quite clever, really.