sort by and group by month/year - please help!

[Guest]

I have the following tables/fields relevant to the query I’m after:

Screened.site
Recruitment.DateOfReferral

all I want to do is design a query and subsequent report which shows the
number of referrals received in a given month/year and to group the outcome
by site (to know how many were referred from a given site).

At the moment, I have this query:

SELECT Recruitment.DateOfReferral, Screened.Site,
Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
FROM Screened INNER JOIN Recruitment ON Screened.StudyNumber =
Recruitment.StudyNumber
GROUP BY Recruitment.DateOfReferral, Screened.Site
ORDER BY Recruitment.DateOfReferral, Screened.Site;

I have included the count function as I have found that there are some
occasions when 2 referrals were received from the same site on the same day
and this is overlooked when I don’t include the count function. However, I’m
not interested in the actual day of referral – just the month.

Ideally I’d like to get a report to show the following:

Month: Site: Number of referrals received:
Jan 07 1 6
Jan 07 2 4

On the report, I’ve formatted the date field so it only shows month and year
which is what I want and I tried grouping by month using the grouping /
sorting options but I can’t seem to get it to work. Can anyone help?!

 

[Marshall Barton]

I have the following tables/fields relevant to the query I’m after:

Screened.site
Recruitment.DateOfReferral

all I want to do is design a query and subsequent report which shows the
number of referrals received in a given month/year and to group the outcome
by site (to know how many were referred from a given site).

At the moment, I have this query:

SELECT Recruitment.DateOfReferral, Screened.Site,
Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
FROM Screened INNER JOIN Recruitment ON Screened.StudyNumber =
Recruitment.StudyNumber
GROUP BY Recruitment.DateOfReferral, Screened.Site
ORDER BY Recruitment.DateOfReferral, Screened.Site;

I have included the count function as I have found that there are some
occasions when 2 referrals were received from the same site on the same day
and this is overlooked when I don’t include the count function. However, I’m
not interested in the actual day of referral – just the month.

Ideally I’d like to get a report to show the following:

Month: Site: Number of referrals received:
Jan 07 1 6
Jan 07 2 4

On the report, I’ve formatted the date field so it only shows month and year
which is what I want and I tried grouping by month using the grouping /
sorting options but I can’t seem to get it to work. Can anyone help?!

If you want the count for each month, then you need to group
by something that ignores the day of the month:

SELECT Site,
DateSerial(Year(DateOfReferral),
Month(DateOfReferral), 1) As YrMth,
Count(*) As CountOfDateOfReferral
FROM Screened INNER JOIN Recruitment
ON Screened.StudyNumber = Recruitment.StudyNumber
GROUP BY Site, DateSerial(Year(DateOfReferral),
Month(DateOfReferral), 1)

 

[Guest]

If I understand correctly, try:

SELECT Year(Recruitment.DateOfReferral) as Yr,
Month(Recruitment.DateOfReferral) as Mth,
Screened.Site,
Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
FROM Screened INNER JOIN Recruitment ON Screened.StudyNumber =
Recruitment.StudyNumber
GROUP BY Year(Recruitment.DateOfReferral),
Month(Recruitment.DateOfReferral),
Screened.Site
ORDER BY Year(Recruitment.DateOfReferral),
Month(Recruitment.DateOfReferral),
Screened.Site;