Combining Date queries

[DArnold]

Ok, being new to Access 2007 I am floundering on how to solve this:

I have a "Patient" table wich contains:
ID
Referral Source
Status
Start Date

I want to produce a report that pulls a count of the Status grouped by
Referral Source, for the month and the year.

So the Report should look like:

Referral
Status 1 1 for the month 5 for the year
Status 2 3 for the month 4 for the year....


I have approached this from several directions and reached a wall.

I query the ID's for the patients with a criteria: Between StartDate and End
Date and this produces a report just fine with a count of the Status. But
now how do I get and append a count of the Status for the year of the
StartDate?

I can create queries of ID's by parameters for the current year, but what if
the report is not for the current year? And how do I merge the data?

Help,
I am new to Access
Thanks!

 

[KenSheridan via AccessMonster.com]

Create a query to count by month, and include within it a subquery to count
by year:

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;

Ken Sheridan
Stafford, England

 

[DArnold]

The query works fine,
I do not need the first two items which is the year - RefYear (Which is
entered at the beginning) and the RefMonth which is the number of the month.

by not showing the field, removing the field, or not selecting the field in
the Report I keep getting the following message, "Multi-level GROUP BY clause
is not supported in a subquery"

If I use "grouping levels" in the report by month I get the same error
message.

Is there a way to make the Report Group by

January
February
March...

Create a query to count by month, and include within it a subquery to count
by year:

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;

Ken Sheridan
Stafford, England

Ok, being new to Access 2007 I am floundering on how to solve this:

I have a "Patient" table wich contains:
ID
Referral Source
Status
Start Date

I want to produce a report that pulls a count of the Status grouped by
Referral Source, for the month and the year.

So the Report should look like:

Referral
Status 1 1 for the month 5 for the year
Status 2 3 for the month 4 for the year....

I have approached this from several directions and reached a wall.

I query the ID's for the patients with a criteria: Between StartDate and End
Date and this produces a report just fine with a count of the Status. But
now how do I get and append a count of the Status for the year of the
StartDate?

I can create queries of ID's by parameters for the current year, but what if
the report is not for the current year? And how do I merge the data?

Help,
I am new to Access
Thanks!

--



.

 

[KenSheridan via AccessMonster.com]

You can remove the computed RefYear column from the query, but not the
RefMonth column as it is this on which the correct sort order of the report
is dependent. So:

SELECT 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 MONTH([Start Date]),
FORMAT([Start Date],"mmmm"),
[Referral Source], Status;

Group the report first by RefMonth, but in its group header include a control
bound to the computed RefMonthName column. Then group it by Referral Source
and include a control bound to that column in its group header. In the
detail section include controls bound to the Status, ReferralsForMonth and
ReferralsForYear columns.

Ken Sheridan
Stafford, England

The query works fine,
I do not need the first two items which is the year - RefYear (Which is
entered at the beginning) and the RefMonth which is the number of the month.

by not showing the field, removing the field, or not selecting the field in
the Report I keep getting the following message, "Multi-level GROUP BY clause
is not supported in a subquery"

If I use "grouping levels" in the report by month I get the same error
message.

Is there a way to make the Report Group by

January
February
March...

Create a query to count by month, and include within it a subquery to count
by year:

[quoted text clipped - 52 lines]