Two sums on same field in same query based on second field criteri

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table called 07DATA which has two fields in it: DOLLARSPAID and
DIRECTION. Dollarspaid is clearly a currency field which has multiple dollar
values. Direction is a field which contains either an "I" or an "O".

What I'm trying to do is to create a query with two fields that shows the
total dollars paid for both "O" and "I". I can easily do this by creating
separate queries and then a third query that returns the results but there
must be a way to do this in just one query?

Thanks in advance!
 
SELECT IIf([DIRECTION] = "I",[DollarsPaid],0) As I_Dollars, IIf([DIRECTION] =
"O",[DollarsPaid],0) As O_Dollars FROM 07DATA;
 
Carpie said:
I have a table called 07DATA which has two fields in it: DOLLARSPAID and
DIRECTION. Dollarspaid is clearly a currency field which has multiple dollar
values. Direction is a field which contains either an "I" or an "O".

What I'm trying to do is to create a query with two fields that shows the
total dollars paid for both "O" and "I". I can easily do this by creating
separate queries and then a third query that returns the results but there
must be a way to do this in just one query?
Click to expand...


SELECT Sum(IIf(Direction = "I", DOLLARSPAID, 0)) As TotalI,
Sum(IIf(Direction = "O", DOLLARSPAID, 0)) As TotalO
FROM [07DATA]
 
Back
Top