Adding values together based on 2 criteria

[Emmie1]

Hello!
I current have a table [ORDERS] that lists customers orders with debits and credits. What I would like to do is for each customer who has the same customer number and order number, add the debit and credit together to give me the remaining value. BOTH criteria must be met before the addition takes place, because the customers "broker" may access at the same order and we charge the "broker" a fee as well.

(Example Current Data [Orders]

Cust# Order# Price
12345 AB698 34.00
12345 AB698 (15.00)
67890 AB698 5.00
98765 AB700 15.00
25879 AB700 5.00

I want to see:
Cust# Order# Price
12345 AB698 19.00
67890 AB698 5.00
98765 AB700 15.00
25879 AB700 5.00

I can get this output if I create a "credit table", a "match credit table" and an update query to add the figures together. But there must be an easier way to do this.

Does anyone have any ideas?

Thanks!
Emmie

 

[PHV]

SELECT [Cust#], [Order#], Sum(Price) As RemainingValue
FROM Orders
GROUP BY [Cust#], [Order#]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

select cust#, order#, sum(price) from tablename group by cust#, order#

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Emmie1]

These suggestions work great!!

However, I left out a peice of info above. The 3 columns listed above are the ones that matter to me right now, but there are about 15 other columns (Customer name, date ordered, type of order, options, delivery method, etc....)that I will need a few steps down the road.

Apparently there is a limit to how many columns you can select (since grouping is used) that will return the values needed with the suggestions above. Is there a way to accomplish what I need and keep show those extra columns?

I apologize for leaving this out, I'm new to posting.... :-(

Thanks again,
Emmie

 

[PHV]

Save the aggregate query I gave you.
Create another query based on the Orders table joined to the aggregate query on Cust# and Order# to get the remaining info you want without the 10 fields limitation of the group by clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886