If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
summing a query
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 |
#2
|
|||
|
|||
summing a query
Try an UNION ALL and then sum.
"SteveR" wrote: 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 |
#3
|
|||
|
|||
summing a 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. "KARL DEWEY" wrote: Try an UNION ALL and then sum. "SteveR" wrote: 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 |
#4
|
|||
|
|||
summing a query
Use the union query as your source in a totals query.
"SteveR" wrote: 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. "KARL DEWEY" wrote: Try an UNION ALL and then sum. "SteveR" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|