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

Senin, 07 Januari 2013

RE: [MS_AccessPros] Using DatePart to get a Quarterly Sum

John,

Try the following and see if that gets you closer to what you want:

SELECT Department.[Department Name], DatePart("q",[checkMailedDate]) As Qtr, Sum([amountReimbursed]) AS QtrTotal
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] And
[Case Information].[Case Number] Is Not Null
GROUP BY Department.[Department Name], [Case Information].[Case Number], DatePart("q",checks.checkMailedDate)

BTW, why are you grouping on case number if you are not displaying the case number?

Darrell

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of jfakes.rm
Sent: Monday, January 07, 2013 10:02 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [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

The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.

Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.

[Non-text portions of this message have been removed]