查询冲突 [英] Query Conflict
问题描述
我正在使用MS Office Access 2003（11.5614）。我一直在运行
以下查询并获得了良好的结果。查询允许我输入月份
期间并返回年龄字段的总和。
SELECT [输入月/年为mm / yyyy] AS [月/年]，SUM（[0到18]）
AS [0到18之和]，SUM（[19到40]）AS [19到40之和]，SUM（[ 41至
65]）AS [41至65之和]，SUM（[66 Plus]）AS [66加之和]
来自[Lake Worth CFP客户] ]
WHERE FORMAT（[SvcDate 1]，" mm / yyyy"）= [输入月/年为mm / yyyy];
我现在想要添加城市字段的其他条件。我添加了
标准的SQL结果
..
SELECT [输入月/年为mm / yyyy] AS [月/年]，Sum（[0到18]）
AS [0到18之和]，Sum（[19到40]）AS [19到40之和]，Sum（[41到
65]）AS [总和41到65]，总和（[66 Plus]）AS [66加之和]
来自[Lake Worth CFP客户]
WHERE（（（格式（[SvcDate 1]，" mm / yyyy"））= [输入月/年为mm / yyyy]）
AND（（[Lake值得CFP客户] .City）=Lake Worth））;
我收到以下错误消息：
你试过执行不包含指定的
表达式city的查询作为一个集合函数的一部分。
冲突似乎在城市标准和
的年龄之间。
有谁可以给我一些方向吗？
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 ?
推荐答案
Stan< st ****** *@hotmail.com写在
新闻：11 ********************** @ y42g2000hsy.googlegr oups.com：
Stan <st*******@hotmail.comwrote in
news:11**********************@y42g2000hsy.googlegr oups.com:
>
我正在使用MS Office Access 2003（11.5614）。我一直在运行
以下查询并获得了良好的结果。该查询允许我输入
月份周期并返回年龄字段的SUM。
SELECT [输入月/年为mm / yyyy] AS [月/年]，SUM（[0到
18]）AS [0到18之和]，SUM（[19到40]）AS [19到40之和]，
SUM（[41至65]）AS [41至65的总和]，SUM（[66 Plus]）AS [66的总和
Plus]来自[Lake Worth CFP客户] ]
WHERE FORMAT（[SvcDate 1]，" mm / yyyy"）= [输入月/年为mm / yyyy];
我现在想要添加城市字段的其他条件。我用
添加了SQL结果的标准
。
SELECT [输入月/年为mm / yyyy] AS [月/年] ，总和（[0到
18]）AS [0到18之和]，总和（[19到40]）AS [19到40之和]，
总和（[41至65]）AS [41至65的总和]，总和（[66加]）AS [来自[Lake Worth CFP客户]的总和66
加上]
WHERE（（（格式（[SvcDate 1]，mm / yyyy））= [输入月/年为
mm / yyyy]）和（（Lake Worth CFP客户] .City）=Lake Worth））;
我收到以下错误消息：
你试图执行查询不包含指定的
表达式" city"作为一个集合函数的一部分。
冲突似乎在城市标准和年龄之间的
之间。
任何人都可以给我一些方向吗？
>
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 ?
在摘要查询中，WHERE子句中的任何字段也必须在
SELECT子句。因为在sum（）之后应用where，
average（）等......如果它在SELECT子句中，则必须在
中函数如sum（），first（）等......或者在GROUP BY子句中
为了解决这个问题，有一个HAVING子句。这适用于总和（）执行前的记录
。只需更改AND AND
（（Lake Worth ...到HAVING AND（（Lake Worth） C ...它应该
工作。
HTH
-
Bob Quintal
PA是我已经改变了我的电子邮件地址。
-
通过免费发布来自 http://www.teranews.com 的Usenet帐户
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
Bob Quintal< rq ****** @ sPAmpatico.cawrote
新闻：Xn ************ **********@66.150.105.47：
Bob Quintal <rq******@sPAmpatico.cawrote in
news:Xn**********************@66.150.105.47:
Stan< st ******* @ hotmail.comwrote in
新闻：11 ********************** @ y42g2000hsy.googlegr psps.com：
Stan <st*******@hotmail.comwrote in
news:11**********************@y42g2000hsy.googlegr oups.com:
>>
我正在使用MS Office Access 2003（11.5614）。我一直在运行
以下查询，结果很好。查询让我进入
月份期间和返回年龄字段的SUM。
SELECT [输入月/年为mm / yyyy] AS [月/年]，SUM（[0到
18]）AS [总和0到18]，SUM（[19到40]）AS [19到40之和]，
SUM（[41到65]）AS [41到65之和]，SUM（[66 Plus]）作为[Lake Corth CFP客户]的[总和]
格式（[SvcDate 1]，mm / yyyy）= [输入月/年为
mm / yyyy];
我现在想要添加城市领域的其他标准。我用SQL结果添加了标准
。
SELECT [输入月/年为mm / yyyy] AS [月/年]，总和（[0到
18] ）AS [0到18之和]，Sum（[19到40]）AS [19到40之和]，总和（[41到65]）AS [41到65之和]，Sum（ [66 Plus]）AS [来自Lake Worth CFP客户的总和]
WHERE（（（格式（[SvcDate 1]，mm / yyyy））= [输入月份] /年
mm / yyyy]）和（（[Lake Worth CFP客户] .City）=Lake Worth））;
我收到以下错误消息：
您尝试执行的查询不包含指定的
表达式" city"作为一个综合功能的一部分。
冲突似乎是在城市标准和年龄的总和之间。
任何人都可以给我一些方向这个问题？
>>
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 ?
在摘要查询中，WHERE子句中的任何字段在SELECT子句中也必须是
。因为在
sum（），average（）等之后应用的地方......如果它在SELECT子句中，它必须是
函数如sum（），first（）等......或者在GROUP BY
条款
为了解决这个问题，有一个HAVING子句。在执行sum（）之前适用于记录的
。只需改变AND AND
（（[Lake Worth ...到HAVING AND（（Lake Worth） C ...它应该
工作。
HTH
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
oops。
只需更改AND（（[Lake Worth ...
HAVING（（[Lake Worth C ...它应该可以工作。
-
Bob Quintal
PA是我改变了我的电子邮件地址。
-
通过 http：// www。中的免费Usenet帐户发布。 teranews.com
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
9月29日上午8:27，Bob Quintal< rquin ... @ sPAmpatico.cawrote：
On Sep 29, 8:27 am, Bob Quintal <rquin...@sPAmpatico.cawrote:
Stan< stanha ... @ hotmail.comwrote innews：11 ********************** @ y42g2000hsy.google groups.com：
Stan <stanha...@hotmail.comwrote innews:11**********************@y42g2000hsy.google groups.com:
我正在使用MS Office Access 2003（11.5614）。我一直在运行
以下查询结果良好。该查询允许我在月份周期中输入
并返回年龄字段的SUM。
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 [输入月/年为mm / yyyy] AS [月/年]，SUM（[0到
18]）AS [0到18之和]，SUM（[19到40]）AS [19到40之和]，
SUM（[41到65]）AS [总和] 41至65]，SUM（[66 Plus]）AS [66％的总和] [来自Lake Worth CFP客户]
格式（[SvcDate 1]， mm / yyyy）= [输入月/年为mm / yyyy];
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];
我现在想要添加城市字段的其他条件。我用
添加了SQL结果的标准
。
SELECT [输入月/年为mm / yyyy] AS [月/年] ，总和（[0到
18]）AS [0到18之和]，总和（[19到40]）AS [19到40之和]，
总和（[41至65]）AS [41至65的总和]，总和（[66加]）AS [来自[Lake Worth CFP客户]的总和66
加上]
WHERE（（（格式（[SvcDate 1]，mm / yyyy））= [输入月/年为
mm / yyyy]）和（（Lake Worth CFP客户] .City）=Lake Worth））;
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:
你试过执行不包含指定的
表达式city的查询作为聚合函数的一部分。
You tried to execute a query that does not include the specified
expression "city" as part of an aggregate function.
冲突似乎介于城市标准和年龄之间的SUM
之间。
The conflict appears to be between the city criteria and the SUM
of the ages.
有谁可以给我一些方向？
Can anyone give me some direction in this matter ?
在摘要查询中，WHERE子句中的任何字段也必须在SELECT子句中为
。因为在sum（）之后应用where，
average（）等......如果它在SELECT子句中，则必须在
中函数如sum（），first（）等......或者在GROUP BY子句中
为了解决这个问题，有一个HAVING子句。这适用于总和（）执行前的记录
。只需更改AND AND
（（Lake Worth ...到HAVING AND（（Lake Worth） C ...它应该
工作。
HTH
-
Bob Quintal
PA是我已经改变了我的电子邮件地址。
-
通过免费发布Usenet帐户来自http://www.teranews.com
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，
我将查询更改为以下内容：
SELECT [输入月/年为mm / yyyy] AS [月/年]，总和（[0到18]）
AS [0到18之和]，总和（[19到40]）AS [19到40之和]，总和（[41到
65]）AS [41到65之和]，Sum（[66 Plus]）AS [66加之和]，[湖泊
值得CFP客户]。城市
来自[Lake Worth CFP客户]
WHERE（（（格式（[SvcDate 1]，mm / yyyy））= [输入月/年为mm / yyyy]）
HAVING AND（（[Lake Worth CFP客户] .City）=" ; Lake Worth））;
我收到以下错误消息：
查询语法错误（缺少运算符）
expression''（（格式（[SvcDate 1]，mm / yyyy"））= [输入月/年为mm /
yyyy]）HAVING AND（（[Lake Worth CFP Clients] .City）=Lake Worth））''
如果我删除了HAVE查询运行正常。
还有其他任何建议吗？
再次感谢，
Stan Hanna
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
这篇关于查询冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
