Query Conflict

[Stan]

I am using MS Office Access 2003 (11.5614). I have been running the
following query with good results. The query lets me enter the month
period and returns the SUM of the age fields.

SELECT [Enter Month/Year as mm/yyyy] AS [Month/Year], SUM([0 to 18])
AS [Sum of 0 to 18], SUM([19 to 40]) AS [Sum of 19 to 40], SUM([41 to
65]) AS [Sum of 41 to 65], SUM([66 Plus]) AS [Sum of 66 Plus]
FROM [Lake Worth CFP Clients]
WHERE FORMAT([SvcDate 1],"mm/yyyy")=[Enter Month/Year as mm/yyyy];

I now want to add an additional criteria of the city field. I added
the criteria with the SQL results
..
SELECT [Enter Month/Year as mm/yyyy] AS [Month/Year], Sum([0 to 18])
AS [Sum of 0 to 18], Sum([19 to 40]) AS [Sum of 19 to 40], Sum([41 to
65]) AS [Sum of 41 to 65], Sum([66 Plus]) AS [Sum of 66 Plus]
FROM [Lake Worth CFP Clients]
WHERE (((Format([SvcDate 1],"mm/yyyy"))=[Enter Month/Year as mm/yyyy])
AND (([Lake Worth CFP Clients].City)="Lake Worth"));

I get the following error message:

You tried to execute a query that does not include the specified
expression "city" as part of an aggregate function.

The conflict appears to be between the city criteria and the SUM of
the ages.

Can anyone give me some direction in this matter ?

[Bob Quintal]

Michael Gramelspacher <gr******@psci. netwrote in
news:ng******** *************** *********@4ax.c om:

There was a discussion of this on m.p.sqlserver.p rogramming only a
few days ago on the 24th, Question regarding "HAVING COUNT(*)=0".

What I got out of it was that using an aggregate function without
a GROUP BY clause means there is just one single group. Thus,
HAVING is appropriate.

That's what I had been led to believe too, from a different source,
and personal experience.

>
On Sat, 29 Sep 2007 16:54:38 -0500, "David W. Fenton"
<XX*******@dfen ton.com.invalid wrote:

>>I still don't get why you need a HQAVING because you have no GROUP
BY clause.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[David W. Fenton]

Michael Gramelspacher <gr******@psci. netwrote in
news:ng******** *************** *********@4ax.c om:

On Sat, 29 Sep 2007 16:54:38 -0500, "David W. Fenton"
<XX*******@dfe nton.com.invali dwrote:

>>I still don't get why you need a HQAVING because you have no GROUP
BY clause.

There was a discussion of this on m.p.sqlserver.p rogramming only a
few days ago on the 24th, Question regarding "HAVING COUNT(*)=0".

What I got out of it was that using an aggregate function without
a GROUP BY clause means there is just one single group. Thus,
HAVING is appropriate.

Only if you're putting criteria on the *result* of an aggregate.
that was not the case in the example given, where the criterion was
on a field *not* being aggregated.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/