If statement and Sum in Access
I have a table that has a list of trip numbers, trip date and trip cost. I want to sum the cost column and group it by month. Basically I want to sum the trip that fall in the currentmonth and name it as currentmonthamount and the trips that happened in the past as previousmonthamount and I want to group it by the company who provided the trips but my query doesn't run at all. HELP!!! SELECT ProviderID, IIF((TripDate>=#7/1/2012#),Sum(AmountPaid),0) As currentmonthamount, IIF((TripDate<#7/1/2012# As previousmonthamount FROM Triptable Group BY ProviderID
July 26th, 2012 4:34pm

Hi gmel ! You may get the desired output using below expression; SELECT ProviderID , SUM(CASE WHEN DATEPART(MM,TripDate) = DATEPART(MM,CURRENT_TIMESTAMP) THEN AmountPaid END) As currentmonthamount , SUM(CASE WHEN DATEPART(MM,TripDate) = DATEPART(MM,DATEADD(MM,-1,CURRENT_TIMESTAMP)) THEN AmountPaid END) As previousmonthamount FROM Triptable Group BY ProviderID Please let me know if this helps. Hopefully i have answered you correctly. Thanks, Hasham
Free Windows Admin Tool Kit Click here and download it now
July 26th, 2012 5:01pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics