Re: group and sort by month / site

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

"Gary Walter" wrote:

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?

"Gary Walter" wrote:
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" wrote:
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" wrote:


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








.

Relevant Pages

  • Re: group and sort by month / site
    ... FROM Screened INNER JOIN Recruitment ON ... "Gary Walter" wrote: ... The report textbox bound to "Month_Year" ...
    (microsoft.public.access.queries)
  • Re: group and sort by month / site
    ... clause, but not in GROUP BY clause? ... "Gary Walter" wrote: ... FROM Screened INNER JOIN Recruitment ON ...
    (microsoft.public.access.queries)
  • Re: using result of REPLACE function as the criteria for query
    ... >Option Compare Database ... >Gary Walter ... when I execute this Select query I ...
    (microsoft.public.access.queries)
  • Re: Unmatch, but exist in both tables
    ... "Nils" as requested by Steen. ... "Gary Walter" wrote in message ... > the min of that true/false condition will be 0 ... > and that group will not be returned by the query. ...
    (microsoft.public.access.queries)
  • Re: Query wont work with THIS table
    ... Gary Walter a écrit: ... > Hi Alain, ... > more one record for an IdType. ... >>Same query generates correct data for similar tables (same fields, ...
    (microsoft.public.access.queries)