Combine Like Quantities

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I have a table that certain VBA functions populate when needed. The fields
include PartNumber, Quantity, and JobNumber. I need to write a query that
can use docmd.runSQL to combine quantities if the PartNumber and JobNumber
field match. So if I have:

PartNumber Quantity JobNumber
------------------------------------------
111111 10 ABCD
111111 20 ABCDE
111111 10 ABCD
222222 10 ABCD

I end up with:

PartNumber Quantity JobNumber
------------------------------------------
111111 20 ABCD
111111 20 ABCDE
222222 10 ABCD

Can anyone help with this?
 
Brian said:
I have a table that certain VBA functions populate when needed. The fields
include PartNumber, Quantity, and JobNumber. I need to write a query that
can use docmd.runSQL to combine quantities if the PartNumber and JobNumber
field match. So if I have:

PartNumber Quantity JobNumber
------------------------------------------
111111 10 ABCD
111111 20 ABCDE
111111 10 ABCD
222222 10 ABCD

I end up with:

PartNumber Quantity JobNumber
------------------------------------------
111111 20 ABCD
111111 20 ABCDE
222222 10 ABCD

Can anyone help with this?
Click to expand...


You can run a query to group the data by PartNumber and JobNumber, sum the
Quantity, and display the result.

The SQL view of that query would be:

SELECT PartNumber, Sum(YourTable.Quantity) As Quantity, JobNumber
FROM YourTable
GROUP BY PartNumber, JobNumber;

You can also easily create this query in the query design-view grid, but
it's more complicated to describe how.

This would not be done with RunSQL, though, as that only applies to action
queries -- queries that update data in tables. You probably do not want to
update the source table with the grouped, consolidated rows. Rather, you
would just base reports and other processing on the totals query I described
above.
 
Back
Top