查询中的Sum（或Group By）导致奇怪的字符 [英] Sum(or Group By) in Query causing weird characters
问题描述
你好〜
帮助请
我是Access新手，所以有点落后于学习曲线。我有一个查询，我已经运行了一段时间，工作正常，现在（没有任何更改）在Item Note字段中抛出奇怪的字符（如小块）。
（注意：不知道这个信息是否重要，我能想到的另一件事在上个月发生了变化，数据库的大小为456M，并链接到其他3个数据库尺寸较小。）
这是在Access 2000，Windows XP中
查询将每个属性的项目组合在一起，如果它们是相同的项目总和物品数量。如果我关闭Group，查询工作正常（当然它列出了所有项目而没有总和）
这是SQL视图（总结一下）：
SELECT [物业费用每月]。[物业编号]，[物业费用每月]。[物品名称]，[物业费用每月]。[物品注释]，[物业费用每月]。[物品数量]， [物业费用每月]。[物品费]，[物业费用每月]。[杂项物品价格]，[物品总数] *（IIf（[杂项物品价格] = 0，[物业费用每月]！[物品]收费]，[物业费用每月]！[其他商品的价格]））AS Expr2，ItemLookUp。[维护费用]，ItemLookUp。[清洁费]，ItemLookUp。[专业费用]，ItemLookUp。[耗材费用]，ItemLookUp。 [Spring Cleaning Charge]，ItemLookUp.ADJUSTMENTS，[Statement Month] AS Expr3
FROM ItemLookUp INNER JOIN [Property Charges Monthly] ON ItemLookUp。[Item Title] = [Property Charges Monthly]。[Item Title ]
WHERE（（（[物业费用每月]。[物品数量]）&l t;> 0））
订购[物业费用每月]。[物业编号]，[物业费用每月]。[物品名称];
，这里是打开了总额的SQL视图：
SELECT [物业费用每月]。[物业编号]，[物业费用每月]。[物品名称]，[物业费用每月]。[项目备注]，总和（[物业费用每月]。[物品数量]）AS [物品总数]，[物业费用每月]。[物品费用]，[物业费用每月]。[杂项价格]项目]，[项目数量] *（IIf（[其他商品价格] = 0，[物业费用每月]！[物品费用]，[物业费用每月]！[杂项物品价格]））AS Expr2，ItemLookUp 。[维护费用]，ItemLookUp。[清洁费]，ItemLookUp。[专业费用]，ItemLookUp。[耗材费用]，ItemLookUp。[Spring Cleaning Charge]，ItemLookUp.ADJUSTMENTS，[Statement Month] AS Expr3
FROM ItemLookUp INNER JOIN [Property Charges Monthly] ON ItemLookUp。[Item Title] = [Property Charges Monthly]。[Item Tit] le]
GROUP BY [物业费用每月]。[物业编号]，[物业费用每月]。[物品名称]，[物业费用每月]。[物品注释]，[物业费用每月] 。[物品费用]，[物业费用每月]。[杂项物品的价格]，物品搭扣[维护费用]，物品搭扣。[清洁费]，物品搭扣。[专业费用]，物品搭扣。[物资费用]，物品搭扣。[春季清洁费]，ItemLookUp.ADJUSTMENTS
HAVING（（（（[物业费用每月]。[物品数量]））<> 0））
订购[物业费用每月]。[物业编号]，[物业费用每月]。[物品名称];
Hello~
Help Please~
I am new to Access, so am a little behind the learning curve. I have a query that I have been running for a while that has worked fine and now (with no changes) is throwing up weird characters(like little blocks) in the Item Note field.
(Note : don''t know if this infomation is important the only other thing I can think of that has changed in the last month is the size of the database which is 456M and links to 3 other databases smaller in size.)
This is in Access 2000, Windows XP
The query groups items together for each property and if they are the same item sums the number of items. The query works fine if I turn off the Group by(of course it lists all items and no sum)
Here is the SQL view (with out sum on):
SELECT [Property Charges Monthly].[Property Number], [Property Charges Monthly].[Item Title], [Property Charges Monthly].[Item Note], [Property Charges Monthly].[Number Of Items], [Property Charges Monthly].[Item Charge], [Property Charges Monthly].[Price for Misc Items], [SumOfNumber Of Items]*(IIf([Price for Misc Items]=0,[Property Charges Monthly]![Item Charge],[Property Charges Monthly]![Price for Misc Items])) AS Expr2, ItemLookUp.[Maintenance Charge], ItemLookUp.[Cleaning Charge], ItemLookUp.[Specialty Charge], ItemLookUp.[Supplies Charge], ItemLookUp.[Spring Cleaning Charge], ItemLookUp.ADJUSTMENTS, [Statement Month] AS Expr3
FROM ItemLookUp INNER JOIN [Property Charges Monthly] ON ItemLookUp.[Item Title] = [Property Charges Monthly].[Item Title]
WHERE ((([Property Charges Monthly].[Number Of Items])<>0))
ORDER BY [Property Charges Monthly].[Property Number], [Property Charges Monthly].[Item Title];
and here is the SQL View with sum turned on:
SELECT [Property Charges Monthly].[Property Number], [Property Charges Monthly].[Item Title], [Property Charges Monthly].[Item Note], Sum([Property Charges Monthly].[Number Of Items]) AS [SumOfNumber Of Items], [Property Charges Monthly].[Item Charge], [Property Charges Monthly].[Price for Misc Items], [SumOfNumber Of Items]*(IIf([Price for Misc Items]=0,[Property Charges Monthly]![Item Charge],[Property Charges Monthly]![Price for Misc Items])) AS Expr2, ItemLookUp.[Maintenance Charge], ItemLookUp.[Cleaning Charge], ItemLookUp.[Specialty Charge], ItemLookUp.[Supplies Charge], ItemLookUp.[Spring Cleaning Charge], ItemLookUp.ADJUSTMENTS, [Statement Month] AS Expr3
FROM ItemLookUp INNER JOIN [Property Charges Monthly] ON ItemLookUp.[Item Title] = [Property Charges Monthly].[Item Title]
GROUP BY [Property Charges Monthly].[Property Number], [Property Charges Monthly].[Item Title], [Property Charges Monthly].[Item Note], [Property Charges Monthly].[Item Charge], [Property Charges Monthly].[Price for Misc Items], ItemLookUp.[Maintenance Charge], ItemLookUp.[Cleaning Charge], ItemLookUp.[Specialty Charge], ItemLookUp.[Supplies Charge], ItemLookUp.[Spring Cleaning Charge], ItemLookUp.ADJUSTMENTS
HAVING (((Sum([Property Charges Monthly].[Number Of Items]))<>0))
ORDER BY [Property Charges Monthly].[Property Number], [Property Charges Monthly].[Item Title];
推荐答案
有趣的块字符通常表示您有一些数据中不可打印的字符（Null;高温超导;室速;等等。
然而，这些将显示是否分组。
当您按[Item Note]字段本身进行分组时，我看不出任何理由为何在分组时表现不同。抱歉。
Funny block characters normally indicate that you have some non-printable characters in your data (Nulls; HTs; VTs; etc).
These would, however, show whether grouped or not.
As you''re grouping by the [Item Note] field itself, I can see no reason why it would behave differently when grouped. Sorry.
有趣的块字符通常表示您的数据中有一些不可打印的字符（Null; HT; VT;等等） 。
然而，这些将显示是否分组。
当您按[Item Note]字段本身进行分组时，我看不出有什么原因分组时表现不同。抱歉。
Funny block characters normally indicate that you have some non-printable characters in your data (Nulls; HTs; VTs; etc).
These would, however, show whether grouped or not.
As you''re grouping by the [Item Note] field itself, I can see no reason why it would behave differently when grouped. Sorry.
如果是备忘录数据类型，这将是一个问题。
Mary
If it''s a memo data type this would be a problem.
Mary
感谢您的回复，NeoPa和Mary！
项目备注是备忘录类型 - 我能解决这个问题吗？为什么Memo类型会导致问题？
Thanks for your responses, NeoPa and Mary!
Item Note is a Memo type- can I fix this? Why would Memo type cause a problem?
这篇关于查询中的Sum（或Group By）导致奇怪的字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
