473,888 Members | 1,636 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Query Returning Multiple Results of Same Record Also Calculation Errors (Sometimes)

85 New Member
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.)

Expand|Select|Wrap|Line Numbers
  1. SELECT tblProd.Item, tblProd.VendorID, tblProd.Description, tblProd.CatID, tblProd.SubCatID, tblProd.Discontinued, tblTrans.EventID, qryListPrice.ListPrice, Sum(tblTrans.Recieved) AS SumOfRecieved, Sum(tblTrans.Sold) AS SumOfSold, Sum([tblTrans].[Recieved]-[Sold]) AS [On Hand], tblVen.VendorName, tblVen.VendorCode, tblCat.Categories, tblSub.SubCategories, tblEveInfo.Name
  2. FROM tblEveInfo INNER JOIN (tblVen INNER JOIN ((tblCat INNER JOIN tblSub ON tblCat.CatID = tblSub.CatID) INNER JOIN ((tblProd INNER JOIN qryListPrice ON tblProd.Price = qryListPrice.Price) INNER JOIN tblTrans ON (tblProd.Item = tblTrans.Item) AND (qryListPrice.Recieved = tblTrans.Recieved)) ON (tblCat.CatID = tblProd.CatID) AND (tblSub.SubCategoryID = tblProd.SubCatID)) ON tblVen.VendorID = tblProd.VendorID) ON tblEveInfo.EventID = tblTrans.EventID
  3. GROUP BY tblProd.Item, tblProd.VendorID, tblProd.Description, tblProd.CatID, tblProd.SubCatID, tblProd.Discontinued, tblTrans.EventID, qryListPrice.ListPrice, tblVen.VendorName, tblVen.VendorCode, tblCat.Categories, tblSub.SubCategories, tblEveInfo.Name
  4. HAVING (((tblProd.VendorID)=[Forms]![frmInventory]![cboVendor]) AND ((tblProd.CatID)=[Forms]![frmInventory]![cboCategory]) AND ((tblProd.SubCatID)=[Forms]![frmInventory]![cboSubCategory]) AND ((tblTrans.EventID)=[Forms]![frmInventory]![cboLocation]) AND ((Sum([tblTrans].[Recieved]-[Sold]))>0)) OR (((tblProd.VendorID)=[Forms]![frmInventory]![cboVendor]) AND ((tblProd.CatID)=[Forms]![frmInventory]![cboCategory]) AND ((tblTrans.EventID)=[Forms]![frmInventory]![cboLocation]) AND ((Sum([tblTrans].[Recieved]-[Sold]))<>0) AND ((([tblProd].[SubCatID]) Like [Forms]![frmInventory]![cboSubCategory]) Is Null)) OR (((tblProd.CatID)=[Forms]![frmInventory]![cboCategory]) AND ((tblTrans.EventID)=[Forms]![frmInventory]![cboLocation]) AND ((Sum([tblTrans].[Recieved]-[Sold]))<>0) AND ((([tblProd].[VendorID]) Like [Forms]![frmInventory]![cboVendor]) Is Null) AND ((([tblProd].[SubCatID]) Like [Forms]![frmInventory]![cboSubCategory]) Is Null)) OR (((tblProd.VendorID)=[Forms]![frmInventory]![cboVendor]) AND ((tblTrans.EventID)=[Forms]![frmInventory]![cboLocation]) AND ((Sum([tblTrans].[Recieved]-[Sold]))<>0) AND ((([tblProd].[CatID]) Like [Forms]![frmInventory]![cboCategory]) Is Null) AND ((([tblProd].[SubCatID]) Like [Forms]![frmInventory]![cboSubCategory]) Is Null)) OR (((tblTrans.EventID)=[Forms]![frmInventory]![cboLocation]) AND ((Sum([tblTrans].[Recieved]-[Sold]))<>0) AND ((([tblProd].[CatID]) Like [Forms]![frmInventory]![cboCategory]) Is Null) AND ((([tblProd].[VendorID]) Like [Forms]![frmInventory]![cboVendor]) Is Null) AND ((([tblProd].[SubCatID]) Like [Forms]![frmInventory]![cboSubCategory]) Is Null)) OR (((tblProd.VendorID)=[Forms]![frmInventory]![cboVendor]) AND ((tblProd.CatID)=[Forms]![frmInventory]![cboCategory]) AND ((tblProd.SubCatID)=[Forms]![frmInventory]![cboSubCategory]) AND ((Sum([tblTrans].[Recieved]-[Sold]))<>0) AND ((([tblTrans].[EventID]) Like [Forms]![frmInventory]![cboLocation]) Is Null)) OR (((tblProd.CatID)=[Forms]![frmInventory]![cboCategory]) AND ((tblProd.SubCatID)=[Forms]![frmInventory]![cboSubCategory]) AND ((Sum([tblTrans].[Recieved]-[Sold]))<>0) AND ((([tblProd].[VendorID]) Like [Forms]![frmInventory]![cboVendor]) Is Null) AND ((([tblTrans].[EventID]) Like [Forms]![frmInventory]![cboLocation]) Is Null)) OR (((tblProd.VendorID)=[Forms]![frmInventory]![cboVendor]) AND ((tblProd.SubCatID)=[Forms]![frmInventory]![cboSubCategory]) AND ((Sum([tblTrans].[Recieved]-[Sold]))<>0) AND ((([tblProd].[CatID]) Like [Forms]![frmInventory]![cboCategory]) Is Null) AND ((([tblTrans].[EventID]) Like [Forms]![frmInventory]![cboLocation]) Is Null)) OR (((tblProd.SubCatID)=[Forms]![frmInventory]![cboSubCategory]) AND ((Sum([tblTrans].[Recieved]-[Sold]))<>0) AND ((([tblProd].[CatID]) Like [Forms]![frmInventory]![cboCategory]) Is Null) AND ((([tblProd].[VendorID]) Like [Forms]![frmInventory]![cboVendor]) Is Null) AND ((([tblTrans].[EventID]) Like [Forms]![frmInventory]![cboLocation]) Is Null)) OR (((tblProd.VendorID)=[Forms]![frmInventory]![cboVendor]) AND ((tblProd.CatID)=[Forms]![frmInventory]![cboCategory]) AND ((Sum([tblTrans].[Recieved]-[Sold]))<>0) AND ((([tblProd].[SubCatID]) Like [Forms]![frmInventory]![cboSubCategory]) Is Null) AND ((([tblTrans].[EventID]) Like [Forms]![frmInventory]![cboLocation]) Is Null)) OR (((tblProd.CatID)=[Forms]![frmInventory]![cboCategory]) AND ((Sum([tblTrans].[Recieved]-[Sold]))<>0) AND ((([tblProd].[VendorID]) Like [Forms]![frmInventory]![cboVendor]) Is Null) AND ((([tblProd].[SubCatID]) Like [Forms]![frmInventory]![cboSubCategory]) Is Null) AND ((([tblTrans].[EventID]) Like [Forms]![frmInventory]![cboLocation]) Is Null)) OR (((tblProd.VendorID)=[Forms]![frmInventory]![cboVendor]) AND ((Sum([tblTrans].[Recieved]-[Sold]))<>0) AND ((([tblProd].[CatID]) Like [Forms]![frmInventory]![cboCategory]) Is Null) AND ((([tblProd].[SubCatID]) Like [Forms]![frmInventory]![cboSubCategory]) Is Null) AND ((([tblTrans].[EventID]) Like [Forms]![frmInventory]![cboLocation]) Is Null)) OR (((Sum([tblTrans].[Recieved]-[Sold]))<>0) AND ((([tblProd].[CatID]) Like [Forms]![frmInventory]![cboCategory]) Is Null) AND ((([tblProd].[VendorID]) Like [Forms]![frmInventory]![cboVendor]) Is Null) AND ((([tblProd].[SubCatID]) Like [Forms]![frmInventory]![cboSubCategory]) Is Null) AND ((([tblTrans].[EventID]) Like [Forms]![frmInventory]![cboLocation]) Is Null));
Nov 9 '07 #1
10 2226
14,534 Recognized Expert Moderator MVP
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT tblProd.Item, tblProd.VendorID, tblProd.Description, tblProd.CatID, 
  2. tblProd.SubCatID, tblProd.Discontinued, tblTrans.EventID, qryListPrice.ListPrice, 
  3. Sum(tblTrans.Recieved) AS SumOfRecieved, Sum(tblTrans.Sold) AS SumOfSold, 
  4. Sum([tblTrans].[Recieved]-[Sold]) AS [On Hand], tblVen.VendorName, 
  5. tblVen.VendorCode, tblCat.Categories, tblSub.SubCategories, tblEveInfo.Name
  6. FROM tblEveInfo INNER JOIN 
  7. (tblVen INNER JOIN 
  8. ((tblCat INNER JOIN tblSub ON tblCat.CatID = tblSub.CatID) INNER JOIN 
  9. ((tblProd INNER JOIN qryListPrice ON tblProd.Price = qryListPrice.Price) 
  10. INNER JOIN tblTrans ON (tblProd.Item = tblTrans.Item) AND (qryListPrice.Recieved = tblTrans.Recieved)) 
  11. ON (tblCat.CatID = tblProd.CatID) AND (tblSub.SubCategoryID = tblProd.SubCatID)) 
  12. ON tblVen.VendorID = tblProd.VendorID) 
  13. ON tblEveInfo.EventID = tblTrans.EventID
  14. GROUP BY tblProd.Item, tblProd.VendorID, tblProd.Description, tblProd.CatID, tblProd.SubCatID, tblProd.Discontinued, tblTrans.EventID, qryListPrice.ListPrice, tblVen.VendorName, tblVen.VendorCode, tblCat.Categories, tblSub.SubCategories, tblEveInfo.Name
There are far too many tables involved in this query, there is even another query.

Lets start off with tblProd, tblTrans and tblVen. What is the metadata and relationship between these tables.

Here is an example of how to post table MetaData :
Table Name=tblBooking s
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Nov 14 '07 #2
85 New Member

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.
(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.)
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblProd.Item, tblProd.Description, tblProd.Discontinued, tblSub.SubCategories, tblCat.Categories, tblVen.VendorName, tblVen.VendorCode, qryListPrice.ListPrice, qryOnHand1.[On Hand], tblEveInfo.Name, tblSub.CatID, tblEveInfo.EventID, tblVen.VendorID, tblSub.SubCategoryID
  2. FROM tblEveInfo INNER JOIN (((tblCat INNER JOIN tblSub ON tblCat.CatID = tblSub.CatID) INNER JOIN ((tblVen INNER JOIN (tblProd INNER JOIN qryListPrice ON tblProd.Item = qryListPrice.Item) ON (tblVen.VendorID = tblProd.VendorID) AND (tblVen.VendorID = qryListPrice.VendorID)) INNER JOIN qryOnHand1 ON tblProd.Item = qryOnHand1.Item) ON (tblCat.CatID = tblProd.CatID) AND (tblSub.SubCategoryID = tblProd.SubCatID)) INNER JOIN tblTrans ON tblProd.Item = tblTrans.Item) ON tblEveInfo.EventID = tblTrans.EventID
  3. GROUP BY tblProd.Item, tblProd.Description, tblProd.Discontinued, tblSub.SubCategories, tblCat.Categories, tblVen.VendorName, tblVen.VendorCode, qryListPrice.ListPrice, qryOnHand1.[On Hand], tblEveInfo.Name, tblSub.CatID, tblEveInfo.EventID, tblVen.VendorID, tblSub.SubCategoryID
  4. HAVING (((qryOnHand1.[On Hand])>0) AND ((tblEveInfo.Name)="Albany" Or (tblEveInfo.Name)="Airport" Or (tblEveInfo.Name)="East Durham" Or (tblEveInfo.Name)="Convention Center") AND ((tblSub.CatID)=[Forms]![frmInventory]![cboCategory]) AND ((tblEveInfo.EventID)=[Forms]![frmInventory]![cboLocation]) AND ((tblVen.VendorID)=[Forms]![frmInventory]![cboVendor]) AND ((tblSub.SubCategoryID)=[Forms]![frmInventory]![cboSubCategory])) OR (((qryOnHand1.[On Hand])>0) AND ((tblEveInfo.Name)="Albany" Or (tblEveInfo.Name)="Airport" Or (tblEveInfo.Name)="East Durham" Or (tblEveInfo.Name)="Convention Center") AND ((tblEveInfo.EventID)=[Forms]![frmInventory]![cboLocation]) AND ((tblVen.VendorID)=[Forms]![frmInventory]![cboVendor]) AND ((tblSub.SubCategoryID)=[Forms]![frmInventory]![cboSubCategory]) AND ((([tblSub].[CatID]) Like [Forms]![frmInventory]![cboCategory]) Is Null)) OR (((qryOnHand1.[On Hand])>0) AND ((tblEveInfo.Name)="Albany" Or (tblEveInfo.Name)="Airport" Or (tblEveInfo.Name)="East Durham" Or (tblEveInfo.Name)="Convention Center") AND ((tblSub.CatID)=[Forms]![frmInventory]![cboCategory]) AND ((tblVen.VendorID)=[Forms]![frmInventory]![cboVendor]) AND ((tblSub.SubCategoryID)=[Forms]![frmInventory]![cboSubCategory]) AND ((([tblEveInfo].[EventID]) Like [Forms]![frmInventory]![cboLocation]) Is Null)) OR (((qryOnHand1.[On Hand])>0) AND ((tblEveInfo.Name)="Albany" Or (tblEveInfo.Name)="Airport" Or (tblEveInfo.Name)="East Durham" Or (tblEveInfo.Name)="Convention Center") AND ((tblVen.VendorID)=[Forms]![frmInventory]![cboVendor]) AND ((tblSub.SubCategoryID)=[Forms]![frmInventory]![cboSubCategory]) AND ((([tblSub].[CatID]) Like [Forms]![frmInventory]![cboCategory]) Is Null) AND ((([tblEveInfo].[EventID]) Like [Forms]![frmInventory]![cboLocation]) Is Null)) OR (((qryOnHand1.[On Hand])>0) AND ((tblEveInfo.Name)="Albany" Or (tblEveInfo.Name)="Airport" Or (tblEveInfo.Name)="East Durham" Or (tblEveInfo.Name)="Convention Center") AND ((tblSub.CatID)=[Forms]![frmInventory]![cboCategory]) AND ((tblEveInfo.EventID)=[Forms]![frmInventory]![cboLocation]) AND ((tblSub.SubCategoryID)=[Forms]![frmInventory]![cboSubCategory]) AND ((([tblVen].[VendorID]) Like [Forms]![frmInventory]![cboVendor]) Is Null)) OR (((qryOnHand1.[On Hand])>0) AND ((tblEveInfo.Name)="Albany" Or (tblEveInfo.Name)="Airport" Or (tblEveInfo.Name)="East Durham" Or (tblEveInfo.Name)="Convention Center") AND ((tblEveInfo.EventID)=[Forms]![frmInventory]![cboLocation]) AND ((tblSub.SubCategoryID)=[Forms]![frmInventory]![cboSubCategory]) AND ((([tblSub].[CatID]) Like [Forms]![frmInventory]![cboCategory]) Is Null) AND ((([tblVen].[VendorID]) Like [Forms]![frmInventory]![cboVendor]) Is Null)) OR (((qryOnHand1.[On Hand])>0) AND ((tblEveInfo.Name)="Albany" Or (tblEveInfo.Name)="Airport" Or (tblEveInfo.Name)="East Durham" Or (tblEveInfo.Name)="Convention Center") AND ((tblSub.CatID)=[Forms]![frmInventory]![cboCategory]) AND ((tblSub.SubCategoryID)=[Forms]![frmInventory]![cboSubCategory]) AND ((([tblEveInfo].[EventID]) Like [Forms]![frmInventory]![cboLocation]) Is Null) AND ((([tblVen].[VendorID]) Like [Forms]![frmInventory]![cboVendor]) Is Null)) OR (((qryOnHand1.[On Hand])>0) AND ((tblEveInfo.Name)="Albany" Or (tblEveInfo.Name)="Airport" Or (tblEveInfo.Name)="East Durham" Or (tblEveInfo.Name)="Convention Center") AND ((tblSub.SubCategoryID)=[Forms]![frmInventory]![cboSubCategory]) AND ((([tblSub].[CatID]) Like [Forms]![frmInventory]![cboCategory]) Is Null) AND ((([tblEveInfo].[EventID]) Like [Forms]![frmInventory]![cboLocation]) Is Null) AND ((([tblVen].[VendorID]) Like [Forms]![frmInventory]![cboVendor]) Is Null)) OR (((qryOnHand1.[On Hand])>0) AND ((tblEveInfo.Name)="Albany" Or (tblEveInfo.Name)="Airport" Or (tblEveInfo.Name)="East Durham" Or (tblEveInfo.Name)="Convention Center") AND ((tblSub.CatID)=[Forms]![frmInventory]![cboCategory]) AND ((tblEveInfo.EventID)=[Forms]![frmInventory]![cboLocation]) AND ((tblVen.VendorID)=[Forms]![frmInventory]![cboVendor]) AND ((([tblSub].[SubCategoryID]) Like [Forms]![frmInventory]![cboSubCategory]) Is Null)) OR (((qryOnHand1.[On Hand])>0) AND ((tblEveInfo.Name)="Albany" Or (tblEveInfo.Name)="Airport" Or (tblEveInfo.Name)="East Durham" Or (tblEveInfo.Name)="Convention Center") AND ((tblEveInfo.EventID)=[Forms]![frmInventory]![cboLocation]) AND ((tblVen.VendorID)=[Forms]![frmInventory]![cboVendor]) AND ((([tblSub].[CatID]) Like [Forms]![frmInventory]![cboCategory]) Is Null) AND ((([tblSub].[SubCategoryID]) Like [Forms]![frmInventory]![cboSubCategory]) Is Null)) OR (((qryOnHand1.[On Hand])>0) AND ((tblEveInfo.Name)="Albany" Or (tblEveInfo.Name)="Airport" Or (tblEveInfo.Name)="East Durham" Or (tblEveInfo.Name)="Convention Center") AND ((tblSub.CatID)=[Forms]![frmInventory]![cboCategory]) AND ((tblVen.VendorID)=[Forms]![frmInventory]![cboVendor]) AND ((([tblEveInfo].[EventID]) Like [Forms]![frmInventory]![cboLocation]) Is Null) AND ((([tblSub].[SubCategoryID]) Like [Forms]![frmInventory]![cboSubCategory]) Is Null)) OR (((qryOnHand1.[On Hand])>0) AND ((tblEveInfo.Name)="Albany" Or (tblEveInfo.Name)="Airport" Or (tblEveInfo.Name)="East Durham" Or (tblEveInfo.Name)="Convention Center") AND ((tblVen.VendorID)=[Forms]![frmInventory]![cboVendor]) AND ((([tblSub].[CatID]) Like [Forms]![frmInventory]![cboCategory]) Is Null) AND ((([tblEveInfo].[EventID]) Like [Forms]![frmInventory]![cboLocation]) Is Null) AND ((([tblSub].[SubCategoryID]) Like [Forms]![frmInventory]![cboSubCategory]) Is Null)) OR (((qryOnHand1.[On Hand])>0) AND ((tblEveInfo.Name)="Albany" Or (tblEveInfo.Name)="Airport" Or (tblEveInfo.Name)="East Durham" Or (tblEveInfo.Name)="Convention Center") AND ((tblSub.CatID)=[Forms]![frmInventory]![cboCategory]) AND ((tblEveInfo.EventID)=[Forms]![frmInventory]![cboLocation]) AND ((([tblVen].[VendorID]) Like [Forms]![frmInventory]![cboVendor]) Is Null) AND ((([tblSub].[SubCategoryID]) Like [Forms]![frmInventory]![cboSubCategory]) Is Null)) OR (((qryOnHand1.[On Hand])>0) AND ((tblEveInfo.Name)="Albany" Or (tblEveInfo.Name)="Airport" Or (tblEveInfo.Name)="East Durham" Or (tblEveInfo.Name)="Convention Center") AND ((tblEveInfo.EventID)=[Forms]![frmInventory]![cboLocation]) AND ((([tblSub].[CatID]) Like [Forms]![frmInventory]![cboCategory]) Is Null) AND ((([tblVen].[VendorID]) Like [Forms]![frmInventory]![cboVendor]) Is Null) AND ((([tblSub].[SubCategoryID]) Like [Forms]![frmInventory]![cboSubCategory]) Is Null)) OR (((qryOnHand1.[On Hand])>0) AND ((tblEveInfo.Name)="Albany" Or (tblEveInfo.Name)="Airport" Or (tblEveInfo.Name)="East Durham" Or (tblEveInfo.Name)="Convention Center") AND ((tblSub.CatID)=[Forms]![frmInventory]![cboCategory]) AND ((([tblEveInfo].[EventID]) Like [Forms]![frmInventory]![cboLocation]) Is Null) AND ((([tblVen].[VendorID]) Like [Forms]![frmInventory]![cboVendor]) Is Null) AND ((([tblSub].[SubCategoryID]) Like [Forms]![frmInventory]![cboSubCategory]) Is Null)) OR (((qryOnHand1.[On Hand])>0) AND ((tblEveInfo.Name)="Albany" Or (tblEveInfo.Name)="Airport" Or (tblEveInfo.Name)="East Durham" Or (tblEveInfo.Name)="Convention Center") AND ((([tblSub].[CatID]) Like [Forms]![frmInventory]![cboCategory]) Is Null) AND ((([tblEveInfo].[EventID]) Like [Forms]![frmInventory]![cboLocation]) Is Null) AND ((([tblVen].[VendorID]) Like [Forms]![frmInventory]![cboVendor]) Is Null) AND ((([tblSub].[SubCategoryID]) Like [Forms]![frmInventory]![cboSubCategory]) Is Null));
Nov 14 '07 #3
14,534 Recognized Expert Moderator MVP
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?

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.
Nov 14 '07 #4
85 New Member

Again, I thank you for all your help on this. I will try to describe what I am trying to do as concisely as I can and with as much logic as I possess. This database is for a Museum giftshop and it is complicated. I think that the easiest way to describe how I intend the database to work is to go through what would happen with each item that we stock.

Once we find an item that we think we would like to carry we contact the Vendor and order it. When we recieve it we go to the database and enter the Vendor information in tblVendor (through a form). We then enter the product information into tblProd (also through a form). The product information includes fields for Category and subcategory. These are designations that we use to group the inventory so that we can determine what sells best for us. Also as you noted the different categories have different tax rates. A SubForm to the product entry form is the form for tblTrans. This table records the date that we recieve an item, the location that the item is stocked to (Albany [administrative office], East Durham [Exhibit Center] Airport [consignment location] or Convention Center [consignment location]), the number we recieved, and our cost. The majority of our stock is entered into the inventory at Albany. If the stock moves to a different location we again go to the product form, enter a transaction where we enter a negative # in the recieved for Albany (or the original location) and then enter a seperate transaction where we recieve stock in the new location. We also sell stock from all four of these locations. To do this we go to the products form and enter a new transaction ( including date, location of sale, # sold, and any discount on our list price [for the consignments]. In addition to these four locations we also sell stock at various festivals and events that take place throughout the year. When we sell an item at one of these events we go to our database, we open a form (tblEvenCon) that has event contacts listed, these are the organizations that organize the various events. As a subForm to this contact information we have an events form (tbleveinfo). This event information stores the location of the event, start and end times and dates, I am also going to add a memo field for comments about the event (volunteers who worked, weather, notes about busy times, etc.) (NOTE- I have entered the four previously mentioned permanent locations as an event and use tblevent to create a dropdown list in tbltrans.) Once this information is entered we open the products form again and enter a new transaction, first we go to the location where the stock was temporarly removed and enter the negative number recieved. Then we enter another transaction to sell the item from the event (just like we sell it from the four main locations). I hope this explains better why I have things set up the way that I do and the significance of each of my tables.

In summary tblTrans records the inventory movement through all four of our permanent locations and also temperary festivals and events (some of which are annual etc.) It is tied to tblEveInfo so that we can keep track of what items sell best at the various location that we sell them. The contacts in tblEveCons are the groups that organize various festivals and events and also the contact information for our four 'permant' sites. The Events and Event Contacts are in no way related to the Vendors (at least for this database).

Each Product has only one Vendor, but Vendors have many products. Each product is assigned a Category (Clothing, Jewelry, Crystal, Music, Books, etc.) and a SubCategory (Clothing - Sweaters, T-shirts, Vests, GolfShirt, etc). The tax rate of the products varies depending on its category.

Also- I have read Database Normalisation and Table Sturctures at least three times (hence the original revamps of my table structures). At this point the only thing that I see that goes against the idea of normalization is that I do not have an address table that is related back to vendors or events. Since the two entities are completely seperate I have not seen a compelling argument to combined them.

The other possible issue to resolve is the Category SubCategory IDs listed in the Product Table that FishVal was trying to explain to me. My current understanding of his comments were that I could eliminate the CatID field in my products form because the SubCatID that I stored there would act as a link to the category information...I n which case I only have to delete the field. He had mentioned creating a new table with the CatID and the SubCatID fields (which I think is already there in my SubCat Table) and then adding a field to the tblProd that used both as a joined key. I'm not really sure where he was going with this thought, or why it might be important so if you have any insite I would appreciate it.

(I'm still learning and I like to understand the whys of things, not just the hows so sometimes I ask too many questions, or try to explain things back to people too often.) Let me know if I get too annoying, I'll try to scale it back. On the flip side I don't mind people asking me hundreds of questions so if anything I've outlined doesn't make sence let me know and I'll try to explain it again.

Thanks for all your help with this, I really appreciate all your time.
Nov 15 '07 #5
14,534 Recognized Expert Moderator MVP
OK you are trying to do too much with tblTrans. I understand why you feel all this data is related but it should be recorded separately. I know where FishVal was going with the category issue but in your case I don't think this is a problem. We can get into it further after we sort things out of if it become necessary. As an exercise I have created a table structure I believe will work very well for you and deliberately didn't look at your table structure too closely when doing it. See if you can follow why I have used the following structure. It is just an initial draft and some parts may not work but look at it from the point of view of how you would query information from the database and see if this would make those queries easier.

VendorID (Primary key)

ProdID (Primary key)
VendorID (Foreign key reference to the primary key of tblVendor)

PurchaseID (Primary key)
PONum (not unique allowing multiple purchaseID's on same PONum)
ProdID (Foreign key reference to tblProduct)
Qty (Number of product ordered)
CostPerItem (Cost of one Product Item - Total cost calculated but not stored)

InventoryID (Primary key)
ProdID (Foreign key reference to tblProduct)
Qty (Number of product Received)

In the above table you would record each product as it was received.

StockID (Primary key)
ProdID (Foreign key reference to tblProduct)
LocationID (Foreign key reference to tblLocation)

In the above table you would record each movement of stock as a new transaction. Max of DateStocked would always give you the current location of the stock based on any ProdID.

SalesID (Primary key)
ProdID (Foreign key reference to tblProduct)
LocationID (Foreign key reference to tblLocation)
EventID (Foreign key reference to tblEvents - only filled when products sold at event)

LocationID (Primary key)

EventID (Primary key)
ContactID (Foreign key reference to tblEventContact s)

tblEventContact s
ContactID(Prima ry key)
Nov 15 '07 #6
85 New Member
I see where keeping items recieved and number sold in different locations would make sence, I was having difficulty figuring out how I would then be able to calculate the inventory that I had at each seperate location. I can also see where seperating the locations from the events would make quering easier but I have a couple of questions and concerns about your proposed table structure.

1. tblPurchases - seems to record mostly information that we do not use(we are a small staff and only enter the inventory once we recieve it, we do not use purchase orders etc.), the only information that we use is our cost, which you propose to have as a calculation field but not store it, this cost tells us the price at the time we purchase it and should be stored. The Price (in my tblProd) is the price that we are selling an item for which also has to be stored (if it changes it will change for all that stock, not just the most reciently ordered), the List price (which is the price that includes tax) will be the only price that should be calculated but not stored. Does this make sence?

2. tblStock - if I am understanding correctly when a new Item came in we would enter the entire order into tblPurchases and then enter it again in tblStock to seperate it out into the various locations, but typically all of the product is added to Albany and at a later date moved to other locations as they need stock. It seems like this setup would be having me enter the same information twice. Am I misunderstandin g?

2a. In addition how would we remove stock from one location and move it to another, if something is not selling at the Airport for example it may sell at our Exhibit Center and we would move it accordingly. How would we do this in your structure?

3. tblStock - If an item is sold at an event we would fill in the EventID in tblsales but the locationID would be blank? How would this work, also how would we remove items from the location so they don't show up in stock? If we did not leave it blank and the database removed it from stock wouldn't it also record the sale of the same item for two locations? When I print out sales by date, I do not want to have the sale at an event included in the location sales, I would like to see all of the sales listed at the locations they occured. Is this a correct understanding of how it would work?

I am assuming that there will be other tables besides those that you have listed here. tblCat, tblSubCat, etc.

I really appreciate the time you have put into looking at this issue. I look forward to your continueing advice on my table structures.
Nov 15 '07 #7
14,534 Recognized Expert Moderator MVP
1. tblPurchases - seems to record mostly information that we do not use(we are a small staff and only enter the inventory once we recieve it, we do not use purchase orders etc.), the only information that we use is our cost, which you propose to have as a calculation field but not store it, this cost tells us the price at the time we purchase it and should be stored. The Price (in my tblProd) is the price that we are selling an item for which also has to be stored (if it changes it will change for all that stock, not just the most reciently ordered), the List price (which is the price that includes tax) will be the only price that should be calculated but not stored. Does this make sence?
tblInventory will be sufficient then. You can drop tblPurchases

The price of the the item should be stored in the tblProduct table not including tax which would be calculated as required using the category table. The main reason for this is it would allow changes in tax rates to be entered in the category table and would not require an update of the tblProduct table each time. So yes you are right.

2. tblStock - if I am understanding correctly when a new Item came in we would enter the entire order into tblPurchases and then enter it again in tblStock to seperate it out into the various locations, but typically all of the product is added to Albany and at a later date moved to other locations as they need stock. It seems like this setup would be having me enter the same information twice. Am I misunderstandin g?
tblStock should be sufficient given your circumstances.

2a. In addition how would we remove stock from one location and move it to another, if something is not selling at the Airport for example it may sell at our Exhibit Center and we would move it accordingly. How would we do this in your structure?
There are a couple of options here. You can do something similar to what you were doing in tblTrans and update the stock record that shows the qty of stock in Albany to be 0 if you move all stock or minus the qty figure by the amount of stock moved. Either that or you could add a negative record showing a minus qty figure and put the Date the stock was moved in the DateStocked field.

Then add a new stock record showing the location of the moved stock in the Airport. There are probably better ways of doing this but I would have to think about it. Using this method you could query the table to show the history of a particular product.

3. tblStock - If an item is sold at an event we would fill in the EventID in tblsales but the locationID would be blank? How would this work, also how would we remove items from the location so they don't show up in stock? If we did not leave it blank and the database removed it from stock wouldn't it also record the sale of the same item for two locations? When I print out sales by date, I do not want to have the sale at an event included in the location sales, I would like to see all of the sales listed at the locations they occured. Is this a correct understanding of how it would work?
If stock is sold it is only recorded in tblSales. LocationID would be filled in as in the stock that is sold at the event is stock from Albany. When stock is sold but not at an event the eventID would be left blank. You can query this table to return only those records where eventID is null to show non-event sales. You can also query it to show only event sales.

I am assuming that there will be other tables besides those that you have listed here. tblCat, tblSubCat, etc.
Yes they would also be included. I left them out as I think they are fine as they are for your purposes. Although I have included the references in tblProduct
Nov 15 '07 #8
85 New Member

Sorry I've fallen off the face of the earth for a while. Between my bosses return to the office, the Thanksgiving holiday last week, and the Fundraising Concert our Museum has on the 10th of December I have not had any time to really sit down and evaluate your suggestions and review how they would effect my database. I really want to get the most out of them that I can and I don't think I will have the time necessary to do that until the concert is over. I just wanted to let you know that I WILL look closely at your suggestions and post a reply, either letting you know how it works with the revisions or asking for more guidance. I just want to be able to give it the consideration it requires before I get started. Thanks for all your help and the time you have dedicated to this problem. I'll get back to you as soon as I can.
Nov 28 '07 #9
14,534 Recognized Expert Moderator MVP
No problem, just post a reply whenever you are ready to take it up again.

Nov 28 '07 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: John Bailo | last post by:
I wrote a webservice to output a report file. The fields of the report are formatted based on information in an in-memory XmlDocument. As each row of a SqlDataReader are looped through, a lookup is done, and format information retrieved. The performance was extremely poor -- producing about 1000 rows per minute. However, when I used tracing/logging, my results were inconclusive. First of all, based on the size of the data and the...
by: leavandor | last post by:
I am trying to design a query that works with a relationship between a Table and a Query. I am comparing a value in the table with a computed value inside the query. The reason for this is that the Query calculates a field to become identical to the corresponding table field, for instance: Table 1 contains field "ID", which is WV008A00 Table 2 contains field "ID In", which is WV001000
by: Ivan Carey | last post by:
How can a query display multiple fields with diferent condition on the same field example I have a field name of reason and a field name of duration. I would like to display 2 fileds of total duration but each have different reasion criteria. display Total Recall based on a calculation that adds the total duration for each record that has a criteria of RC on the same query display Total Shift based upon a calculation that adds the
by: Hannes Dorbath | last post by:
First - I'm not sure whether this should go to .bugs, .hackers oder ..sql, so I posted here :/ The query and the corresponding EXPLAIN is at http://hannes.imos.net/query.txt I'd like to use the column q.replaced_serials for multiple calculations in the SELECT clause, but every time it is referenced there in some way the whole query in the FROM clause returning q is executed again.
by: serge | last post by:
How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always either AND or OR but never mixed together. We can use Northwind database for my question, it is very similar to the structure of the problem on the database I am working on. IF(SELECT OBJECT_ID('REPORT')) IS NOT NULL DROP TABLE REPORT_SELECTION
by: TORQUE | last post by:
Hi, Im wondering if anyone can help me with a problem. I have a form with more than 50 unbound fields. Some of the fields will be blank from time to time. This seems to be where im having trouble. I have tried keeping some of the fields bound and when I use the save button it has been saving as 2 different records. This is unacceptable. This is what I have, can anyone help me out with this?
by: Cindy | last post by:
I have an Access 2003 database with a query that works fine on my PC but does not work on another PC. My PC has older versions of Access installed as well as 2003 - the other PC only has Access 2003. The query is as follows: SELECT tblOrganization.strOrgName, tblPerson.strNameL, tblPerson.strNameF, tblPersonFCL_Courses.lngIDPerson, tblPersonFCL_Courses.strHOTest, tblPersonFCL_Courses.strCert, tblPersonFCL_Courses.ysnProcessed,...
by: JJM0926 | last post by:
I'm trying to create a running totals query in access 97. I have followed the directions on how to do it from Microsofts website article id 138911. I took their code they had and replaced it with my fields. When I try to run it I get #errors in my RunTot column. I'm kinda new to this. Not sure if maybe I mistyped something wrong or is there a better way to do this? I have pasted the code. Any help would be greatly appreciated....
by: heckstein | last post by:
I have created a query in MS Access 2003 that is pulling training records for our company that includes training hour calculation. One field I am pulling is the instructor name. Many courses have multiple instructors, which means I am getting multiple records for a single course. I need to capture all of the instructor names, but I only want a single record for each course due to the hour calculation. I would like the report to generate only one...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.