If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multi-level GROUP BY
I am new to Access so be gentle.
I have a query that pulls from 1 table: Sales by month and sales for the year based on the clients STATUS: When I try to put this into a report I keep geting the following error: "Multi-level GROUP BY clause is not supported in a subquery" The report shows: Referral January Status 1 1 for the month 5 for the year January Status 2 3 for the month 4 for the year.... I would like to just have: January: Status 1 1 for the month 5 for the year Status 2 3 for the month 4 for the year.... February... Status 1 1 for the month 5 for the year Status 2 3 for the month 4 for the year.... but it seems due to the query I can not group the months in the report. Is there anyway around this or does the query need to be changed? Here is the query: SELECT YEAR([Start Date]) AS RefYear, MONTH([Start Date]) AS RefMonth, FORMAT([Start Date],"mmmm") AS RefMonthName, [Referral Source], Status, COUNT(*) AS ReferralsForMonth, (SELECT COUNT(*) FROM Patient AS P2 WHERE YEAR(P2.[Start Date]) = YEAR(P1.[Start Date]) AND P2.[Referral Source] = P1.[Referral Source] AND P2.Status = P1.Status) AS ReferralsForYear FROM Patient AS P1 WHERE YEAR([Start Date]) = [Enter year:] GROUP BY YEAR([Start Date]), MONTH([Start Date]), FORMAT([Start Date],"mmmm"), [Referral Source], Status; |
#2
|
|||
|
|||
Multi-level GROUP BY
DArnold wrote:
I have a query that pulls from 1 table: Sales by month and sales for the year based on the clients STATUS: When I try to put this into a report I keep geting the following error: "Multi-level GROUP BY clause is not supported in a subquery" The report shows: Referral January Status 1 1 for the month 5 for the year January Status 2 3 for the month 4 for the year.... I would like to just have: January: Status 1 1 for the month 5 for the year Status 2 3 for the month 4 for the year.... February... Status 1 1 for the month 5 for the year Status 2 3 for the month 4 for the year.... but it seems due to the query I can not group the months in the report. Is there anyway around this or does the query need to be changed? Here is the query: SELECT YEAR([Start Date]) AS RefYear, MONTH([Start Date]) AS RefMonth, FORMAT([Start Date],"mmmm") AS RefMonthName, [Referral Source], Status, COUNT(*) AS ReferralsForMonth, (SELECT COUNT(*) FROM Patient AS P2 WHERE YEAR(P2.[Start Date]) = YEAR(P1.[Start Date]) AND P2.[Referral Source] = P1.[Referral Source] AND P2.Status = P1.Status) AS ReferralsForYear FROM Patient AS P1 WHERE YEAR([Start Date]) = [Enter year:] GROUP BY YEAR([Start Date]), MONTH([Start Date]), FORMAT([Start Date],"mmmm"), [Referral Source], Status; Are you sure that query works when it is opened by itself? In any case I think you can avoid the error by using DCount instead of a subquery: DCount("*", "Patient", "YEAR([Start Date]) = YEAR(" & RefYear & " AND [Referral Source] = '" & [Referral Source] & "' AND Status = " & Status) AS ReferralsForYear That assumes [Referral Source] is a text field that does not contain any apostrophes and Status is a number type field. If I guessed wrong about that adjust the quotes accordingly, -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Multi-level GROUP BY
"Marshall Barton" wrote:
DArnold wrote: I have a query that pulls from 1 table: Sales by month and sales for the year based on the clients STATUS: When I try to put this into a report I keep geting the following error: "Multi-level GROUP BY clause is not supported in a subquery" The report shows: Referral January Status 1 1 for the month 5 for the year January Status 2 3 for the month 4 for the year.... I would like to just have: January: Status 1 1 for the month 5 for the year Status 2 3 for the month 4 for the year.... February... Status 1 1 for the month 5 for the year Status 2 3 for the month 4 for the year.... but it seems due to the query I can not group the months in the report. Is there anyway around this or does the query need to be changed? Here is the query: SELECT YEAR([Start Date]) AS RefYear, MONTH([Start Date]) AS RefMonth, FORMAT([Start Date],"mmmm") AS RefMonthName, [Referral Source], Status, COUNT(*) AS ReferralsForMonth, (SELECT COUNT(*) FROM Patient AS P2 WHERE YEAR(P2.[Start Date]) = YEAR(P1.[Start Date]) AND P2.[Referral Source] = P1.[Referral Source] AND P2.Status = P1.Status) AS ReferralsForYear FROM Patient AS P1 WHERE YEAR([Start Date]) = [Enter year:] GROUP BY YEAR([Start Date]), MONTH([Start Date]), FORMAT([Start Date],"mmmm"), [Referral Source], Status; Are you sure that query works when it is opened by itself? In any case I think you can avoid the error by using DCount instead of a subquery: DCount("*", "Patient", "YEAR([Start Date]) = YEAR(" & RefYear & " AND [Referral Source] = '" & [Referral Source] & "' AND Status = " & Status) AS ReferralsForYear That assumes [Referral Source] is a text field that does not contain any apostrophes and Status is a number type field. If I guessed wrong about that adjust the quotes accordingly, -- Marsh MVP [MS Access] . Ok, Referral Source and Status are both Text. What I have is: SELECT YEAR([Start Date]) AS RefYear, MONTH([Start Date]) AS RefMonth, FORMAT([Start Date],"mmmm") AS RefMonthName, [Referral Source], Status, COUNT(*) AS ReferralsForMonth, (SELECT COUNT(*) FROM Patient AS P2 WHERE DCount ("*", "Patient", "YEAR([Start Date]) = YEAR (" & RefYear & " AND [Referral Source] = '" & [Referral Source] & "' AND Status = " & Status) AS ReferralsForYear) FROM Patient AS P1 WHERE YEAR([Start Date]) = [Enter year:] GROUP BY YEAR([Start Date]), MONTH([Start Date]), FORMAT([Start Date],"mmmm"), [Referral Source], Status; Which is giving me fits. Help |
#4
|
|||
|
|||
Multi-level GROUP BY
Ok,
Is this getting closer: SELECT YEAR([Start Date]) AS RefYear, MONTH([Start Date]) AS RefMonth, FORMAT([Start Date],"mmmm") AS RefMonthName, [Referral Source], Status, COUNT(*) AS ReferralsForMonth, DCount ( "*", "Patient", "YEAR([Start Date]) = YEAR (" & RefYear & " AND [Referral Source] = '" & [Referral Source] & "' AND Status = '" & Status & "') AS ReferralsForYear FROM Patient AS P2 WHERE YEAR(P2.[Start Date]) = YEAR(P1.[Start Date]) AND P2.[Referral Source] = P1.[Referral Source] AND P2.Status = P1.Status) AS ReferralsForYear FROM Patient AS P1 WHERE YEAR([Start Date]) = [Enter year:] GROUP BY YEAR([Start Date]), MONTH([Start Date]), FORMAT([Start Date],"mmmm"), [Referral Source], Status; |
#5
|
|||
|
|||
Multi-level GROUP BY
DArnold wrote:
Is this getting closer: SELECT YEAR([Start Date]) AS RefYear, MONTH([Start Date]) AS RefMonth, FORMAT([Start Date],"mmmm") AS RefMonthName, [Referral Source], Status, COUNT(*) AS ReferralsForMonth, DCount ( "*", "Patient", "YEAR([Start Date]) = YEAR (" & RefYear & " AND [Referral Source] = '" & [Referral Source] & "' AND Status = '" & Status & "') AS ReferralsForYear FROM Patient AS P2 WHERE YEAR(P2.[Start Date]) = YEAR(P1.[Start Date]) AND P2.[Referral Source] = P1.[Referral Source] AND P2.Status = P1.Status) AS ReferralsForYear FROM Patient AS P1 WHERE YEAR([Start Date]) = [Enter year:] GROUP BY YEAR([Start Date]), MONTH([Start Date]), FORMAT([Start Date],"mmmm"), [Referral Source], Status; You did not remove all of the subquery when you replaced it with DCount. I think you want: SELECT YEAR([Start Date]) AS RefYear, MONTH([Start Date]) AS RefMonth, FORMAT([Start Date],"mmmm") AS RefMonthName, [Referral Source], Status, COUNT(*) AS ReferralsForMonth, DCount ( "*", "Patient", "YEAR([Start Date]) = YEAR (" & RefYear & " AND [Referral Source] = '" & [Referral Source] & "' AND Status = '" & Status & "') AS ReferralsForYear FROM Patient WHERE YEAR([Start Date]) = [Enter year:] GROUP BY YEAR([Start Date]), MONTH([Start Date]), FORMAT([Start Date],"mmmm"), [Referral Source], Status; -- Marsh MVP [MS Access] |
#6
|
|||
|
|||
Multi-level GROUP BY
Marshall,
First of thank you for your patients and help. I have spent 2 hours tring to get past the next error message which is: Syntax error (missing operator) in query expressiong 'DCount("*", "Patient", "YEAR([Start Date]) = YEAR ("& RefYear &" AND [Referral Source] = '"[Referral Source] & "' AND Status = '"Status & "') AS ReferralsForYear FROM So here is where I really feel stupid: 1) Since Status is a text field does Status in line 4 of the DCount need [] around both? 2) Could this error be being caused by spaces somewhere in this equation? Referral Source vs. Referral Source - even though the Field is named Referral Source - Possibly a VISTA problem? 3) The quotation at the beginning of YEAR on line 2 does not have a match. Thanks, Doug Here is the code from the last entry SELECT YEAR([Start Date]) AS RefYear, MONTH([Start Date]) AS RefMonth, FORMAT([Start Date],"mmmm") AS RefMonthName, [Referral Source], Status, COUNT(*) AS ReferralsForMonth, DCount ( "*", "Patient", "YEAR([Start Date]) = YEAR (" & RefYear &" AND [Referral Source] = '" & [Referral Source] &"' AND Status = '" & Status & "') AS ReferralsForYear FROM Patient WHERE YEAR([Start Date]) = [Enter year:] GROUP BY YEAR([Start Date]), MONTH([Start Date]), FORMAT([Start Date],"mmmm"), [Referral Source], Status; |
#7
|
|||
|
|||
Multi-level GROUP BY
Perhaps something like the following (All one line)
DCount("*", "Patient", "YEAR([Start Date]) = YEAR(#" & [Start Date] & "#) AND [Referral Source] = '" & [Referral Source] & "' AND Status = '" & [Status] & "'") AS ReferralsForYear John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County DArnold wrote: Marshall, First of thank you for your patients and help. I have spent 2 hours tring to get past the next error message which is: Syntax error (missing operator) in query expressiong 'DCount("*", "Patient", "YEAR([Start Date]) = YEAR ("& RefYear &" AND [Referral Source] = '"[Referral Source] & "' AND Status = '"Status & "') AS ReferralsForYear FROM So here is where I really feel stupid: 1) Since Status is a text field does Status in line 4 of the DCount need [] around both? 2) Could this error be being caused by spaces somewhere in this equation? Referral Source vs. Referral Source - even though the Field is named Referral Source - Possibly a VISTA problem? 3) The quotation at the beginning of YEAR on line 2 does not have a match. Thanks, Doug Here is the code from the last entry SELECT YEAR([Start Date]) AS RefYear, MONTH([Start Date]) AS RefMonth, FORMAT([Start Date],"mmmm") AS RefMonthName, [Referral Source], Status, COUNT(*) AS ReferralsForMonth, DCount ( "*", "Patient", "YEAR([Start Date]) = YEAR (" & RefYear &" AND [Referral Source] = '" & [Referral Source] &"' AND Status = '" & Status & "') AS ReferralsForYear FROM Patient WHERE YEAR([Start Date]) = [Enter year:] GROUP BY YEAR([Start Date]), MONTH([Start Date]), FORMAT([Start Date],"mmmm"), [Referral Source], Status; |
#8
|
|||
|
|||
Multi-level GROUP BY
Ok,
Inch by inch it is getting closer. It did not like the # as a syntax error so I replace them with (') Still a format error so I removed the (") around the entire YEAR expression and it did go so far as asking me the year when ran. However, when I entered the year it gave me: This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assingning parts of the expression to variables: Here is the code: SELECT YEAR([Start Date]) AS RefYear, MONTH([Start Date]) AS RefMonth, FORMAT([Start Date],"mmmm") AS RefMonthName, [Referral Source], Status, COUNT(*) AS ReferralsForMonth, DCount("*", "Patient", YEAR([Start Date]) = YEAR('" & [Start Date] & "') AND [Referral Source] = '" & [Referral Source] & "' AND Status = '" & [Status] & "') AS ReferralsForYear FROM Patient WHERE YEAR([Start Date]) = [Enter year:] GROUP BY YEAR([Start Date]), MONTH([Start Date]), FORMAT([Start Date],"mmmm"), [Referral Source], Status; "John Spencer" wrote: Perhaps something like the following (All one line) DCount("*", "Patient", "YEAR([Start Date]) = YEAR(#" & [Start Date] & "#) AND [Referral Source] = '" & [Referral Source] & "' AND Status = '" & [Status] & "'") AS ReferralsForYear John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County DArnold wrote: Marshall, First of thank you for your patients and help. I have spent 2 hours tring to get past the next error message which is: Syntax error (missing operator) in query expressiong 'DCount("*", "Patient", "YEAR([Start Date]) = YEAR ("& RefYear &" AND [Referral Source] = '"[Referral Source] & "' AND Status = '"Status & "') AS ReferralsForYear FROM So here is where I really feel stupid: 1) Since Status is a text field does Status in line 4 of the DCount need [] around both? 2) Could this error be being caused by spaces somewhere in this equation? Referral Source vs. Referral Source - even though the Field is named Referral Source - Possibly a VISTA problem? 3) The quotation at the beginning of YEAR on line 2 does not have a match. Thanks, Doug Here is the code from the last entry SELECT YEAR([Start Date]) AS RefYear, MONTH([Start Date]) AS RefMonth, FORMAT([Start Date],"mmmm") AS RefMonthName, [Referral Source], Status, COUNT(*) AS ReferralsForMonth, DCount ( "*", "Patient", "YEAR([Start Date]) = YEAR (" & RefYear &" AND [Referral Source] = '" & [Referral Source] &"' AND Status = '" & Status & "') AS ReferralsForYear FROM Patient WHERE YEAR([Start Date]) = [Enter year:] GROUP BY YEAR([Start Date]), MONTH([Start Date]), FORMAT([Start Date],"mmmm"), [Referral Source], Status; . |
#9
|
|||
|
|||
Multi-level GROUP BY
Brain cramp on my part. Try the following.
DCount("*", "Patient", "YEAR([Start Date]) =" & YEAR([Start Date]) & " AND [Referral Source] = '" & [Referral Source] & "' AND Status = '" & [Status] & "'") AS ReferralsForYear Then the SQL would look like: SELECT YEAR([Start Date]) AS RefYear, MONTH([Start Date]) AS RefMonth, FORMAT([Start Date],"mmmm") AS RefMonthName, [Referral Source], Status, COUNT(*) AS ReferralsForMonth, DCount("*", "Patient", YEAR([Start Date]) =" & YEAR([Start Date]) & " AND [Referral Source] = '" & [Referral Source] & "' AND Status = '" & [Status] & "'") AS ReferralsForYear FROM Patient WHERE YEAR([Start Date]) = [Enter year:] GROUP BY YEAR([Start Date]), MONTH([Start Date]), FORMAT([Start Date],"mmmm"), [Referral Source], Status , DCount("*", "Patient", YEAR([Start Date]) =" & YEAR([Start Date]) & " AND [Referral Source] = '" & [Referral Source] & "' AND Status = '" & [Status] & "'") John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County DArnold wrote: Ok, Inch by inch it is getting closer. It did not like the # as a syntax error so I replace them with (') Still a format error so I removed the (") around the entire YEAR expression and it did go so far as asking me the year when ran. However, when I entered the year it gave me: This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assingning parts of the expression to variables: Here is the code: SELECT YEAR([Start Date]) AS RefYear, MONTH([Start Date]) AS RefMonth, FORMAT([Start Date],"mmmm") AS RefMonthName, [Referral Source], Status, COUNT(*) AS ReferralsForMonth, DCount("*", "Patient", YEAR([Start Date]) = YEAR('" & [Start Date] & "') AND [Referral Source] = '" & [Referral Source] & "' AND Status = '" & [Status] & "') AS ReferralsForYear FROM Patient WHERE YEAR([Start Date]) = [Enter year:] GROUP BY YEAR([Start Date]), MONTH([Start Date]), FORMAT([Start Date],"mmmm"), [Referral Source], Status; |
Thread Tools | |
Display Modes | |
|
|