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

[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!

 

[Guest]

SELECT IIf([DIRECTION] = "I",[DollarsPaid],0) As I_Dollars, IIf([DIRECTION] =
"O",[DollarsPaid],0) As O_Dollars FROM 07DATA;

 

[Marshall Barton]

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?

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