Re: Query Conflict

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

.

Relevant Pages

  • 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: 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
    ... the following query with good results. ... I now want to add an additional criteria of the city field. ... The conflict appears to be between the city criteria and the SUM ... the SELECT clause. ...
    (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)
Quantcast