SQL Statement to Combine Like Items and Sum Quantities

[Brian]

Imagine I have a table that has records that look like:

Part, Job, Quantity
------------------------
100005, B09016, 25
100005, B09016, 25
100006, B09016, 25
100006, B88888, 25

And I want to write a SQL statement to change the records to look like:

Part, Job, Quantity
------------------------
100005, B09016, 50
100006, B09016, 25
100006, B88888, 25

Of course I'd be dealing with thousands of records and 12 fields, but I
think this briefly explains what I am looking for.

Can anyone help?

Thanks!

 

[vanderghast]

SELECT part, job, SUM(quantity)
FROM table
GROUP BY part, job



In graphical view, instead than in SQL view:

Click on the summation button on the toolbar, a new line, TOTAL, appear in
the grid. Bring the required fields in the grid. Keep the proposed GROUP BY,
except for the Quantity field, you will change it for SUM.


That's all.



Vanderghast, Access MVP

 

[Brian]

I'm sorry. I wasn't very clear. Selecting and combining the records from
the table is pretty easy. What I need to do is to actually modify the data
in the table so that the end result is a table full of records whose like
Part/Job have had the quantities combined.

Sorry 'bout that!

 

[vanderghast]

make a query which creates a table, temp, based from the total query.
Delete every record in your initial table. Append the data from temp to the
now empty initial table.

But...


Question: why do you need to UPDATE the data? cannot you use the TOTAL
QUERY in places where you need the TOTAL ? That is what we generally do,
with databases. DESTROYING your initial data (by keeping only the totals) is
not very friendly when it is time to trace back possible errors. MUCH safer
is to keep all the details in one table, and to use a total query, when and
where the total is required.


Vanderghast, Access MVP