Query question

  • Thread starter Thread starter G Lam
  • Start date Start date
G

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
 
G said:
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
Click to expand...

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
 
Back
Top