ACCESS FEED: [MS_AccessPros] Using DatePart to get a Quarterly Sum

Senin, 07 Januari 2013

[MS_AccessPros] Using DatePart to get a Quarterly Sum

I need to get quarterly counts of how much money was reimbursed broken down by quarter. Each quarter, I used to update this query and several others so I could produce a complex report. I'm trying to get the date ranges now by opening a form and entering the date range.

I have been trying to use something like the following code which has the input limited to 2012. The query is also grouped by Department so I can tell how much money was reimbursed by dept.:

Q1Count: IIf([checkMailedDate],DatePart("q",[checkMailedDate])=1,Sum([amountReimbursed]))

What I need to get is if the [checkMailedDate] is in the first quarter, then I need a sum of [amountReibursed]. However, when I run the above code, I get a count of how many reimbursments there were on that date, not the sum.

Here is the complete SQL code (complete with old logic):
SELECT Department.[Department Name], Sum(IIf([checkMailedDate] Between #1/1/2012# And #3/31/2012#,[amountReimbursed])) AS 1Qtr2012Total, IIf([checkMailedDate],DatePart("q",[checkMailedDate])=1,Sum([amountReimbursed])) AS Q1Count, Sum(IIf([checkMailedDate] Between #4/1/2012# And #6/30/2012#,[amountReimbursed])) AS 2Qtr2012Total, Sum(IIf([checkMailedDate] Between #7/1/2012# And #9/30/2012#,[amountReimbursed])) AS 3Qtr2012Total, Sum(checks.amountReimbursed) AS SumOfamountReimbursed
FROM ((checks INNER JOIN [Case Information] ON checks.[Case Number] = [Case Information].[Case Number]) LEFT JOIN Physician ON [Case Information].TreatingPhysician = Physician.[Provider Number]) LEFT JOIN Department ON Physician.Specialty = Department.[Department ID]
WHERE (((checks.reimbursementReason)="Loss of Time") AND ((checks.checkMailedDate) Between [Forms]![subfrmDatesForQuarterlyReport]![txtStartDate] And [Forms]![subfrmDatesForQuarterlyReport]![txtEndDate]))
GROUP BY Department.[Department Name], [Case Information].[Case Number], checks.checkMailedDate
HAVING ((([Case Information].[Case Number]) Is Not Null));

Thanks, John F