查询返回同一记录的多个结果还计算错误（有时） [英] Query Returning Multiple Results of Same Record Also Calculation Errors (Sometimes)

问题描述

我正在使用Access 2003中的库存数据库。我在设计显示库存当前库存的报告时遇到问题。我有一个未绑定的表单（frmInventory）。此表单上有四个组合框，允许用户选择Location，Vendor，Category和SubCategory。然后将这些选择（或空白字段）传递给查询（请参阅下面的SQL），并打开报告以显示清单。当我只有有限的测试数据时，这非常有效。


现在我们开始使用数据库来存放实际库存我发现有时项目在报告中列出两次，而且有时库存数据不准确。关于它的最令人沮丧的部分是，在同一报告中，一些项目是绝对正确的，一些列出两次，但有正确的库存（在两个条目中列出），一些列出两次，但有不正确的库存而其他一些只列出一次但库存不正确。


我检查了我的桌子，每个项目只有一个肠道，我的公式似乎是正确的。我不知道这有什么问题。


如果有人想知道为什么这会从一个查询运行返回如此多种结果我会很感激关于如何解决它的建议。 （我正在学习代码，因此我仍然不太擅长，请耐心等待。）

I am working on an Inventory Database in Access 2003. I am having trouble with a report I designed to show current inventory in stock. I have a form (frmInventory) that is unbound. There are four combo boxes on this form that allow the user to select Location, Vendor, Category and SubCategory. These selections (or blank fields) are then passed on to a query (see SQL below) and a report opens to show the inventory. This worked really well when I only had limited test data.

Now that we are starting to use the database to house actual inventory I have discovered that sometimes items are listed twice in the report, and also that sometimes the data for in stock is not accurate. The most frustrateing part about it is that in the same report some of the items are absolutely correct, some are listed twice but with the correct inventory (listed in both entries), some are listed twice but have incorrect inventories and others are listed only once but with incorrect inventories.

I have checked my tables and there is only one entery for each item, and my formulas seem to be correct. I do not have any idea of what is going wrong with this.

If anyone has any ideas as to why this is returning such a variety of results from one query run I would appreciate any advice on how to fix it. (I am learning code as I go so I am still not very good at it, Please be patient with me.)

展开 | 选择 | 换行 | 行号

推荐答案

好的，这是我见过的最复杂的查询。我会感到震惊的是发现它没有错误。这与您的标准无关。您的表结构存在问题。如果您在没有任何条件的情况下运行如下查询，您将看到您正在重复记录。

OK that is the most complicated query I have ever seen. I would be shocked to find it was working without errors. This is not about your criteria. There is a problem with your table structures. If you run the query as below without any criteria you will see that you are getting duplication of records.

展开 | 选择 | Wrap | 行号

mmcarthy;


这是我用wizzards之外的任何东西创建的第一个数据库，我参加了为期两天的访问课程，并了解它可以做多少，现在我在舞台上在那里我知道足够危险;）非常感谢你甚至试图了解所有这一切。我知道它可能会引起一两次头痛，我会尽力限制它们。


为了回复你的帖子，我已经从我的数据库中包含了MetaData信息。我不确定如何直接从Access获取MetaData，所以我手动输入。

mmcarthy;

This is the first database that I have created using anything beyond the wizzards, I took a two day course on access and learned how much more it could do so now I am at the stage where I know just enough to be dangerous ;) Thank you so much for even trying to attempt to understand all of this. I know it will probably involve a headache or two, I will do my best to limit them.

In response to your post I have included the MetaData information from my database. I was unsure how to get the MetaData from Access directly so I typed it up manually.

（大约一个月前我在一个帖子中发布了我的所有表MetaData - 在表单上查看计算查询字段 - 如果你愿意的话想要参考它们，它们仍然以这种方式设置[我担心我可能看起来很顽固，并且通过在发布他的回复之后再次重申信息来关注FishVal，我只知道重组我的表将意味着很多修改表格和报告{我最后一次这样做我不得不重新创建它们中的许多因为我无法找到所有旧的参考文献}并希望在我开展一个大项目之前真正理解它的必要性。如果有必要我改变我的在这个晚期的表结构我会这样做，但除非真的有必要，我宁愿不管它。）

(About a month ago I posted all of my tables MetaData in a post called- Viewing a calculated query field on a form - if you would like to reference them they are still pretty much set up this way [I fear I may have seemed stuborn and close minded to FishVal by reiterating information over again after he posted his responses, I just know that restructuring my tables will mean alot of revisions to forms and reports {the last time I did it I had to recreate many of them because I could not eminate all the old references} and want to really understand its necessity before I undertake a project that big. If it is necessary that I change my table structure at this late date I will do it, but unless it is really necessary I would prefer to leave it alone.)

表名= tblProd

字段;类型; IndexInfo

项目;数; PK

VendorID;数; FK

描述;文字

价格;文字

CatID;数; FK

SubCatID;数; FK

停产;是/否

重新订购;号码

AirID;号码

ConID;数字


表名= tblVen

字段;类型; IndexInfo

VendorID;自动编号; PK

VendorName;文字

VendorCode;文字

地址;文字

地址2;文字

城市;文字

州/国家;文字

PostalCode;文字

联系人姓名;文字

电子邮件;文字

网站;文字

电话;文字


表名= tblTrans

字段;类型; IndexInfo

SubProdID;自动编号; PK

日期;日期

EventID;数; FK

收到;号码

已售出;号码

我们的成本;货币

折扣;货币

项目;编号FK

现在，因为我发布了这个，我继续研究这个问题。由于我无法找出导致上一个查询中出现问题的原因，因此我开始创建一个新查询。


这个新查询现在正确工作，但我注意到在设计它时，当特定项目有多个位置时，重复项发生了。在任何特定时间有四个地点可能有库存（奥尔巴尼，东达勒姆，机场，会议中心），但也有一些地点（一天的展览和节日，筹款活动等），我们将有销售，但将有没有常备库存（物品将从四个主要地点中的一个取出，如果他们不卖，则返还给他们，如果他们确实卖出，那么主要地点将有-X收到，并且临时位置将收到X和卖X.右现在我通过添加一个标准来确定查询正常工作，该标准表明姓名必须与奥尔巴尼，东达勒姆，机场，会议中心相同。


我认为这是一个临时修复，因为现在我的数据库中唯一的数据位于奥尔巴尼（以及一天的事件）我没有为其他三个永久性位置添加任何信息。我​​担心当我输入其他永久位置的数据时，我将再次遇到问题。数据重复，因为它不能cr为多个地点的项目选择一个列表，我不知道为什么。


我在下面包含了我的新查询'SQL以供您查看（它不是更简单） ，可能更复杂，但它是我的[抱歉]）。非常感谢您的持续建议和理解。

Table Name = tblProd
Field; Type; IndexInfo
Item; Number; PK
VendorID; Number; FK
Description; Text
Price; Text
CatID; Number; FK
SubCatID; Number; FK
Discontinued; Y/N
Reorder; Number
AirID; Number
ConID; Number

Table Name = tblVen
Field; Type; IndexInfo
VendorID; AutoNumber; PK
VendorName; Text
VendorCode; Text
Address; Text
Address2; Text
City; Text
State/Country; Text
PostalCode; Text
ContactName; Text
E-mail; Text
Website; Text
Phone; Text

Table Name = tblTrans
Field; Type; IndexInfo
SubProdID; AutoNumber; PK
Date; Date
EventID; Number; FK
Received; Number
Sold; Number
Our Cost; Currency
Discount; Currency
Item; Number FK

Now, since I posted this I have continued to work on the issue. Since I was unable to figure out what was causing the issues in the previous query, I set out to create a new one.

This new query is working ''correctly'' now, but I noticed when I was designing it that the duplicates were occuring when there was more than one location for a specific item. There are four locations that could potentially have stock at any given time (Albany, East Durham, Airport, Convention Center) but there are also locations (one day fairs and festivals, fundraising events, etc) where we will have sales but there will be no standing inventory (items will be taken from one of the four main locations and returned to them if they do not sell, if they do sell then the main location will have -X recieved and the temperary location will recieve X and Sell X. Right now I have the query working correctly by adding a criteria that says that Name has to equal Albany, East Durham, Airport, Convention Center.

I think this is a temporary fix because right now the only data in my database is located in Albany (and some one day events) I have not added any information for the other three permanent locations. I am afraid that when I enter data for the other permanent locations I will again have issues with the data duplicating because it cannot create one listing for items in multiple locations, I''m not sure why.

I have included my new query''s SQL below for you to review (it is not any simpler, and is probably more complicated but its what I have [sorry]). Your continued advice and understanding will be greatly appreciated.

展开 | 选择 | Wrap | 行号

确定了解表格结构看看在数据库规范化和表结构上的这篇文章中。


说实话，除非你得到你的桌面结构，你试图做的每一个查询都将是一场噩梦。


你所拥有的桌子......


我不敢了解tblTrans正在发生的事情。你在录什么节目？为什么它与tblEveInfo（EventID）绑定，谁是tblEveCons中的联系人？他们与供应商有什么关系吗？

假设：

我知道您有产品和供应商，每个产品都与特定供应商。那么每个产品只有一个供应商？每个产品都是类别和子类别的一部分。产品的税率取决于它属于哪个类别。


如果我的假设是正确的，那么尝试用逻辑解释其他三个表格的情况，我们会看到我们能做什么。它可能不需要太多变化，但目前我还不太明白发生了什么。

OK to understand about table structures have a look at this article on
Database Normalisation and Table Structures.

The honest truth is that unless you get your table structures right every query you try to do will be a nightmare.

Of the tables you have ...

I don''t understand what is going on with tblTrans. What are you recording? And why is it tied to tblEveInfo (EventID) and who are the contacts in tblEveCons? Have they any relationship to the vendors?

Assumptions:
I understand that you have a product and a vendor and each product is tied to a particular vendor. So there is only ever one vendor per product? Each product is part of a category and of a subcategory. The tax rate of the product is dependent on which category it belongs to.

If my assumptions are correct then try explaining with logic what is going on with the other three tables and we will see what we can do. It may not require too many changes but at the moment I don''t really understand what is going on.

这篇关于查询返回同一记录的多个结果还计算错误（有时）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！