summing a query

[Guest]

Experimenting with summing in a query.

Have a 2 simple tables with part number and qty as fields

ie table 1 part1.....qty1
tablw 2 part 2......qty2

the I set up a make new table query using the two tables with a link between
part1 and part2, and a column that sums the 2 qty together.

Now all is ok as long as I have identical part numbers in both tables, it
returns the values correctly and sums them up. But if I enter a unique part
number in one table and not the other it will not return that part number as
part of the query result.

How can I get the query to return all part numbers even if they only appear
in on table.

I'm using this as a test to then set up a query that will search across a
number of tables and return say all the bolts found even if they do not
appear in all the tables and total up all the same part numbers.

Any help appreciated

SR

 

[Guest]

Try an UNION ALL and then sum.

 

[Guest]

Karl,

Thanks for that tip.

So I figured out how to set up a UNION query and then run a make table query
on that but I am at a loss as to where to put the SUM expression.

from my example below I created a union query that looks like this;

SELECT [PART NUMBER1], [QTY1]
FROM [TEST1]

UNION ALL SELECT [PART NUMBER2],[QTY2]
FROM [TEST2];

this returns a result listing all part numbers in one column and all qty in
another

So then I create a new table query in which I can add a selection criteria
for user input.

Now the question, how to I add a sum to group all the same part numbers
together and add the qty in a new total column?

Thanks again for any help.

 

[Guest]

Use the union query as your source in a totals query.

Karl,

Thanks for that tip.

So I figured out how to set up a UNION query and then run a make table query
on that but I am at a loss as to where to put the SUM expression.

from my example below I created a union query that looks like this;

SELECT [PART NUMBER1], [QTY1]
FROM [TEST1]

UNION ALL SELECT [PART NUMBER2],[QTY2]
FROM [TEST2];

this returns a result listing all part numbers in one column and all qty in
another

So then I create a new table query in which I can add a selection criteria
for user input.

Now the question, how to I add a sum to group all the same part numbers
together and add the qty in a new total column?

Thanks again for any help.

Try an UNION ALL and then sum.