Multi-level GROUP BY

  • Thread starter Thread starter DArnold
  • Start date Start date
D

DArnold

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;
 
Marshall Barton said:
DArnold said:
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;
Click to expand...


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,
Click to expand...

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
 
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;
 
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;
 
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
 
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 said:
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
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;
Click to expand...
.
Click to expand...
 
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
 
Back
Top