Re: group and sort by month / site

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

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...


"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
    ... "Gary Walter" wrote: ... "part of an aggregate function" because ... From your SQL, it should look like: ... "Emelina Bumsquash" wrote ...
    (microsoft.public.access.queries)
  • Re: reindexing DBF files
    ... Tquery means BDE. ... SQL commands for reindexing DBF files via the BDE. ... So I want to reindex all the dbf tables first, ...
    (alt.comp.lang.borland-delphi)
  • GROUP BY
    ... One of my favorite lessons from TTM was always to consider the 0-arity ... SQL obscures this fact, due to the templated nature of a SQL query. ... What if 4) uses not an aggregate function, ... necessarily limited to producing aexactly one output tuple from ...
    (comp.databases.theory)
  • Re: GROUP BY
    ... One of my favorite lessons from TTM was always to consider the 0-arity ... SQL obscures this fact, due to the templated nature of a SQL query. ... What if 4) uses not an aggregate function, ... a mechanism for applying generators. ...
    (comp.databases.theory)
  • Ntext Field Not Read by BDE
    ... that does SQL Quering on the SQL table. ... I ended up determining that it was not the ODBC connection, ... speaking the BDE either (although it only works on the MSSQL Driver, ... ntext field within it. ...
    (microsoft.public.sqlserver.server)