EasyCFM.COM ColdFusion Forums / MS Access Related Issues / Need help with conditional sums in a query

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Need help with conditional sums in a query

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

Lyndon
11-18-2010 @ 1:47 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 66
Joined: Jun 2004

I have a receipt table with columns: TransDate, Facility, TransType, Amount

I need a query, to build a menu with, which has one row per date per facility. The distinct part is no problem for me:

Select Distinct TransDate, Facility
From Receipt

That works great; however, they now want two more columns:

1. ACHTotal (sum of amount where TransType='ACH')
2. NonACHTotal (sum of amount where TransType<>'ACH')

Of course these are the sums for that distinct TransDate and Facility.

I know it is probably done by grouping but I don't know how to proceed with the conditions on TransType.

Any help appreciated.


Lyndon

Lyndon
11-19-2010 @ 8:48 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 66
Joined: Jun 2004

Here is the solution...

Select Format(TransactionDate,'yyyy/mm/dd') as mTransactionDate, Track, Sum(Amount) as TotalSales,
Instrument, Sum(IIf(Instrument='ACH',Amount,0)) AS ACHSales
From Receipt
Group By Format(TransactionDate,'yyyy/mm/dd'), Track
Order By Format(TransactionDate,'yyyy/mm/dd') desc, Track  


Lyndon


Website Designed and Developed by Pablo Varando.