group and sort by month / site

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

 

[Gary Walter]

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

Hi Emilina,

There are so many ways...

but, if we are not talking millions of records here,
I might just recast all dates to the first of the month
using DateSerial()

SELECT
DateSerial(Year(R.DateOfReferral),
Month(R.DateOfReferral),
1) AS Month_Year,
S.Site,
Count(R.DateOfReferral) AS CountOfDateOfReferral
FROM
Screened As S
INNER JOIN
Recruitment As R
ON
S.StudyNumber = R.StudyNumber
GROUP BY
DateSerial(Year(R.DateOfReferral),
Month(R.DateOfReferral),
1),
S.Site;

I did not include ORDER BY
since your report grouping will
override it anyway where you
would group by "Month_Year"
and sort by Month_Year, Site.

The report textbox bound to "Month_Year"
could then be formatted to "mmm yy"

That might be one way...

good luck,

gary

 

[Emelina Bumsquash]

thanks so much for your prompt response. however, i can't seem to make the
query work. i'm guessing you were shorthanding some of the code so here's the
SQL i've got based on your help:

SELECT
DateSerial (Year(Recruitment.DateOfReferral),
Month(Recruitment.DateOfReferral), 1) AS Month_Year,Screened.Site,
Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
FROM Screened INNER JOIN Recruitment ON
Screened.StudyNumber=Recruitment.StudyNumber
GROUP BY
DateSerial(Year(Recruitment.DateOfReferral),Month(Recruitment.DateOfReferrall), 1), Screened.Site;

but i get the error message: 'you tried to execute a query that does not
include the specified expression DateSerial
(Year(Recruitment.DateOfReferral), Month(Recruitment.DateOfReferral), 1) as
part of an aggregate function'

any help greatly appreciated as always!

 

[Gary Walter]

Hi Emilina,

That does not make sense...
the expression does not need to be
"part of an aggregate function" because
is part of the "group."

What does the column look like in BDE?

From your SQL, it should look like:
(I won't type out all of function innards)

Field: Month_Year: DateSerial(xxxx)
Table:
Total: Group By
Sort:
Show: <checked>
Criteria:
or:

What happens if you "toggle" the sigma icon
to temporarily "ungroup." Does the expression
return the correct results?

Is the SQL you provided a strict "copy-and-paste?"

It looks perfect to me...

 

[Gary Walter]

Only strange thing I can see is that
I would have expected Access to
automatically put brackets around table
and field names within Year/Month functions
within DateSerial function?

SELECT
DateSerial (Year([Recruitment].[DateOfReferral]),
Month([Recruitment].[DateOfReferral]), 1) AS Month_Year,Screened.Site,
Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
FROM Screened INNER JOIN Recruitment ON
Screened.StudyNumber=Recruitment.StudyNumber
GROUP BY
DateSerial(Year([Recruitment].[DateOfReferral]),
Month([Recruitment].[DateOfReferrall]), 1), Screened.Site;

 

[Emelina Bumsquash]

yep, a strict copy and paste! i can't get into the BDE view as it won't let
me get out of the SQL view because it keeps giving me that error message -
i'll try to start from scratch using the BDE view perhaps?

 

[Gary Walter]

Also, if this is a "copy-and-paste,"
I don't understand why there is a
space after DateSerial in SELECT
clause, but not in GROUP BY clause?

Only strange thing I can see is that
I would have expected Access to
automatically put brackets around table
and field names within Year/Month functions
within DateSerial function?

SELECT
DateSerial (Year([Recruitment].[DateOfReferral]),
Month([Recruitment].[DateOfReferral]), 1) AS Month_Year,Screened.Site,
Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
FROM Screened INNER JOIN Recruitment ON
Screened.StudyNumber=Recruitment.StudyNumber
GROUP BY
DateSerial(Year([Recruitment].[DateOfReferral]),
Month([Recruitment].[DateOfReferrall]), 1), Screened.Site;

thanks so much for your prompt response. however, i can't seem to make
the
query work. i'm guessing you were shorthanding some of the code so here's
the
SQL i've got based on your help:

SELECT
DateSerial (Year(Recruitment.DateOfReferral),
Month(Recruitment.DateOfReferral), 1) AS Month_Year,Screened.Site,
Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
FROM Screened INNER JOIN Recruitment ON
Screened.StudyNumber=Recruitment.StudyNumber
GROUP BY
DateSerial(Year(Recruitment.DateOfReferral),Month(Recruitment.DateOfReferrall),
1), Screened.Site;

but i get the error message: 'you tried to execute a query that does not
include the specified expression DateSerial
(Year(Recruitment.DateOfReferral), Month(Recruitment.DateOfReferral), 1)
as
part of an aggregate function'

any help greatly appreciated as always!

 

[Emelina Bumsquash]

I just started the query from scratch and used the view you suggested and it
worked! thank you so much. not sure what the problem was with the SQL view
but it must've been something i did wrong - apologies for that.

the resulting SQL i've got which works is:
SELECT
DateSerial(Year(Recruitment.DateOfReferral),Month(Recruitment.DateOfReferral),1)
AS Month_Year, Screened.Site, Count(Recruitment.DateOfReferral) AS
CountOfDateOfReferral
FROM Screened INNER JOIN Recruitment ON
Screened.StudyNumber=Recruitment.StudyNumber
GROUP BY
DateSerial(Year(Recruitment.DateOfReferral),Month(Recruitment.DateOfReferral),1), Screened.Site, Screened.ResultsOfSLTScreenEligible
HAVING (((Screened.ResultsOfSLTScreenEligible)=1))
ORDER BY
DateSerial(Year(Recruitment.DateOfReferral),Month(Recruitment.DateOfReferral),1), Screened.Site;

thanks so much for your help

Also, if this is a "copy-and-paste,"
I don't understand why there is a
space after DateSerial in SELECT
clause, but not in GROUP BY clause?

Only strange thing I can see is that
I would have expected Access to
automatically put brackets around table
and field names within Year/Month functions
within DateSerial function?

SELECT
DateSerial (Year([Recruitment].[DateOfReferral]),
Month([Recruitment].[DateOfReferral]), 1) AS Month_Year,Screened.Site,
Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
FROM Screened INNER JOIN Recruitment ON
Screened.StudyNumber=Recruitment.StudyNumber
GROUP BY
DateSerial(Year([Recruitment].[DateOfReferral]),
Month([Recruitment].[DateOfReferrall]), 1), Screened.Site;

thanks so much for your prompt response. however, i can't seem to make
the
query work. i'm guessing you were shorthanding some of the code so here's
the
SQL i've got based on your help:

SELECT
DateSerial (Year(Recruitment.DateOfReferral),
Month(Recruitment.DateOfReferral), 1) AS Month_Year,Screened.Site,
Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
FROM Screened INNER JOIN Recruitment ON
Screened.StudyNumber=Recruitment.StudyNumber
GROUP BY
DateSerial(Year(Recruitment.DateOfReferral),Month(Recruitment.DateOfReferrall),
1), Screened.Site;

but i get the error message: 'you tried to execute a query that does not
include the specified expression DateSerial
(Year(Recruitment.DateOfReferral), Month(Recruitment.DateOfReferral), 1)
as
part of an aggregate function'

any help greatly appreciated as always!


:


Hi Emilina,

There are so many ways...

but, if we are not talking millions of records here,
I might just recast all dates to the first of the month
using DateSerial()

SELECT
DateSerial(Year(R.DateOfReferral),
Month(R.DateOfReferral),
1) AS Month_Year,
S.Site,
Count(R.DateOfReferral) AS CountOfDateOfReferral
FROM
Screened As S
INNER JOIN
Recruitment As R
ON
S.StudyNumber = R.StudyNumber
GROUP BY
DateSerial(Year(R.DateOfReferral),
Month(R.DateOfReferral),
1),
S.Site;

I did not include ORDER BY
since your report grouping will
override it anyway where you
would group by "Month_Year"
and sort by Month_Year, Site.

The report textbox bound to "Month_Year"
could then be formatted to "mmm yy"

That might be one way...

good luck,

gary

 

[Gary Walter]

okay then....
eliminate the space after DateSerial
in SELECT clause...
it thinks you are grouping by something else...