Darrell,
Thanks for your help. I am trying to fix old queries and one of the things (like you suggested is taking out the group by case number).
I was just logging back on to say I had fixed the query and the following worked:
Q1Count: Sum(IIf(DatePart("q",[checkMailedDate])=1,[amountReimbursed]))
Here is my new code:
SELECT Department.[Department Name], Sum(IIf(DatePart("q",[checkMailedDate])=1,[amountReimbursed])) AS Q1Count, Sum(IIf(DatePart("q",[checkMailedDate])=2,[amountReimbursed])) AS Q2Count, Sum(IIf(DatePart("q",[checkMailedDate])=3,[amountReimbursed])) AS Q3Count, Sum(IIf(DatePart("q",[checkMailedDate])=4,[amountReimbursed])) AS Q4Count, 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.checkMailedDate) Is Not Null) AND ((checks.amountReimbursed)>0.01) AND ((checks.reimbursementReason)="Loss of Time") AND ((checks.checkMailedDate) Between [Forms]![subfrmDatesForQuarterlyReport]![txtStartDate] And [Forms]![subfrmDatesForQuarterlyReport]![txtEndDate]))
GROUP BY Department.[Department Name];
John F.
--- In MS_Access_Professionals@yahoogroups.com, "Embrey, Darrell" wrote:
>
> 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]
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar