Query question

[G Lam]

Hi, I tried cross query and some other action queries but I was unable to
get it done. Please help.
I have a sourec table - Tblsaledet like this:
SlsPerId TranType SalesAmt Tax PPD Frt InvTtl
LYN IN 8.00 0.48 1.5 8.48
LYN CM 2.00 2.00
LYN IN 6.00 6.00
JEB IN 12.00 2.5 12.00
JEB CM 1.5 1.5
LLD IN 20.00 20.00
LLD IN 3.8 3.8

I want to make a result record set like this:

SlsPerID SalesAmt CreditAmt PPD Frt
LYN 14.00 2.00 1.5
JEB 12.00 1.5 2.5
LLD 23.80 0 0

Thank you in advance.
Gary

 

[Marshall Barton]

Hi, I tried cross query and some other action queries but I was unable to
get it done. Please help.
I have a sourec table - Tblsaledet like this:
SlsPerId TranType SalesAmt Tax PPD Frt InvTtl
LYN IN 8.00 0.48 1.5 8.48
LYN CM 2.00 2.00
LYN IN 6.00 6.00
JEB IN 12.00 2.5 12.00
JEB CM 1.5 1.5
LLD IN 20.00 20.00
LLD IN 3.8 3.8

I want to make a result record set like this:

SlsPerID SalesAmt CreditAmt PPD Frt
LYN 14.00 2.00 1.5
JEB 12.00 1.5 2.5
LLD 23.80 0 0

You want to use a Totals (Group By) query. I think it will
look something like:

SELECT SlsPerID,
Sum(IIf(TranType = "IN", SalesAmt, 0)) As SalesAmt,
Sum(IIf(TranType = "CM", SalesAmt, 0)) As CreditAmt.
Sum(IIf(TranType = "IN", PPDFrt, 0)) As PPDFrt
FROM Tblsaledet
GROUP BY SlsPerID