Re: Query Conflict

Stan <stanhanna@xxxxxxxxxxx> wrote in
news: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 from http://www.teranews.com

.

Relevant Pages

  • Re: Query Conflict
    ... the following query with good results. ... 66 Plus] FROM [Lake Worth CFP Clients] ... The conflict appears to be between the city criteria and the SUM ... any field in the WHERE clause also has to be ...
    (comp.databases.ms-access)
  • Re: Query Conflict
    ... running the following query with good results. ... 66 Plus] FROM [Lake Worth CFP Clients] ... SUM of the ages. ... any field in the WHERE clause also has to be ...
    (comp.databases.ms-access)
  • Re: error message.
    ... Since you are using one of the aggregate functions (SUM) in the query, you need to group by all of the other fields that are not using the sum function. ... You might need to replace the SUM with the VBA function DSUM or with a subquery in the Select clause; and drop the entire Group by clause. ...
    (microsoft.public.access.queries)
  • Re: Query Conflict
    ... the following query with good results. ... the month period and returns the SUM of the age fields. ... Plus] FROM [Lake Worth CFP Clients] ... the SELECT clause. ...
    (comp.databases.ms-access)
  • RE: To Avoid Blank Space in Combo Box
    ... You can do a delete query where the city is null when the form closes. ... world.now sum of the cities has been entered and sum which not been entered ... well the problem cums many times user leaves blank,so even blank space row ...
    (microsoft.public.access.forms)