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

  • Thread starter Emelina Bumsquash
  • Start date
E

Emelina Bumsquash

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?!
 
M

Marshall Barton

Emelina said:
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)
 
D

Duane Hookom

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;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top