Re: Query Conflict
- From: Bob Quintal <rquintal@xxxxxxxxxxxxx>
- Date: 29 Sep 2007 12:57:11 GMT
Bob Quintal <rquintal@xxxxxxxxxxxxx> wrote in
news:Xns99BA5EA86A7ECBQuintal@xxxxxxxxxxxxx:
Stan <stanhanna@xxxxxxxxxxx> wrote in
news:1191028805.846142.272220@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:
In a summary query, any field in the WHERE clause also has to be
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 ?
in the SELECT clause. because the where is applied after the
sum(), average() etc... and if it's in the SELECT clause it has to
be in a function like sum(), first(), etc... or in the GROUP BY
Clause
To get around this, there is a HAVING clause. which is applicable
to the records before the sum() is performed.Just change AND AND
(([Lake Worth... to HAVING AND (([Lake Worth C... and it should
work.
HTH
oops.
Just change AND (([Lake Worth... to
HAVING (([Lake Worth C... and it should work.
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com
.
- References:
- Query Conflict
- From: Stan
- Re: Query Conflict
- From: Bob Quintal
- Query Conflict
- Prev by Date: Re: Query Conflict
- Next by Date: Re: Query Conflict
- Previous by thread: Re: Query Conflict
- Next by thread: Re: Query Conflict
- Index(es):
Relevant Pages
|
