conditional sum

[LHWC]

I have a query with several fields, two of which are TYPE and QTY. There are several records where TYPE and all other fields are identical, except QTY. I want to create a new query that consolidates these into one record, summiing QTY.
Example:

TYPE ...........QTY .......... indicates the other like
A ............2 fields
A ............3
A ............4
B ............5
B ............6

Result:
TYPE ...........QTY
A ............9
B ...........11

How do I accomplish this? Thanks in advance.

 

[jrbarnett]

Go into Query design mode, go to the View menu and click Totals and choose Sum on the Qty field, and "Group By" on the Type field.

John

 

[amorous]

SELECT TYPE, FIELD1, FIELD2, SUM(QTY) FROM TableName GROUP BY TYPE

-VJ

 

[LHWC]

Thanks to both of you.
When I tested these, I got an error saying I tried to execute a query that does not include the specified expression 'FIELD1'as part of an aggregate function.
Any ideas?

 

[lespaul]

if all you have are those two fields this should work if you change the tablename. If you add new fields to the SELECT statement you MUST add them to the GROUP BY statement.

SELECT TYPE, SUM(QTY) FROM tablename GROUP BY TYPE



Leslie