Re: Query Conflict
- From: Salad <oil@xxxxxxxxxxx>
- Date: Sat, 29 Sep 2007 09:45:51 -0700
Stan wrote:
On Sep 29, 8:27 am, Bob Quintal <rquin...@xxxxxxxxxxxxx> wrote:
Stan <stanha...@xxxxxxxxxxx> wrote innews:1191028805.846142.272220@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:
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 a summary query, any field in the WHERE clause also has to be 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
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account fromhttp://www.teranews.com
Bob,
I changed the query to the following:
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], [Lake
Worth CFP Clients].City
FROM [Lake Worth CFP Clients]
WHERE (((Format([SvcDate 1],"mm/yyyy"))=[Enter Month/Year as mm/yyyy])
HAVING AND (([Lake Worth CFP Clients].City)="Lake Worth"));
I received the following error message:
syntax error (missing operator) in query
expression' (((Format([SvcDate 1],"mm/yyyy"))=[Enter Month/Year as mm/
yyyy]) HAVING AND (([Lake Worth CFP Clients].City)="Lake Worth"))'
IF I remove the HAVE the query runs OK.
Any other suggestions?
Thanks again,
Stan Hanna
What is Having AND? What are you ANDing?
.
- References:
- Query Conflict
- From: Stan
- Re: Query Conflict
- From: Bob Quintal
- Re: Query Conflict
- From: Stan
- Query Conflict
- Prev by Date: Re: Change Printer
- Next by Date: Re: Query Conflict
- Previous by thread: Re: Query Conflict
- Next by thread: Re: Query Conflict
- Index(es):
Relevant Pages
|
