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

[klarae99]

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

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
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
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
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));

[MMcCarthy]

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.

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
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
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=tblBookings

 Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[klarae99]

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.

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

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
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
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
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));

[MMcCarthy]

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.

[klarae99]

mmccarthy;

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...In 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.

[MMcCarthy]

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.

tblVendor
VendorID (Primary key)
VendorName
Address1
Address2
Address3
City
Country
Contact
Phone
Email

tblProduct
ProdID (Primary key)
ProdName
VendorID (Foreign key reference to the primary key of tblVendor)
CatID
SubCatID

tblPurchases
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)

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

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

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

Note:
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.

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

tblLocation
LocationID (Primary key)
LocationName

tblEvent
EventID (Primary key)
EventName
ContactID (Foreign key reference to tblEventContacts)
DateStart
DateEnd

tblEventContacts
ContactID(Primary key)
ContactName
Address1
Address2
Address3
City
Phone

[klarae99]

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 misunderstanding?

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.

[MMcCarthy]

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 misunderstanding?

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

[klarae99]

mmccarthy,

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.

[MMcCarthy]

No problem, just post a reply whenever you are ready to take it up again.

Mary

[klarae99]

Mary,

I bet you thought I forgot about you. I have had to put this project aside for a while as it was working OK and there were other more pressing matters that I had to deal with. Luckily I do have some time over the next week or two to return my thoughts to this database. I have been reviewing your suggestions for a reworked table structure for my gift shop database, as well as your first set of answers to my questions. I am going to summarize my understanding of where we are at now so that hopefully you will be able to guide me through the rest of this process.

TblVendor
VendorID (Primary Key)
Vendor Name
Address1
Address2
City
State
Zip
Country
Contact Name
Phone
E-mail
Web Address

tblProduct
ProdID (Primary Key)
Prod Description
VendorID (foreigh Key)
Cat ID (FK)
SubCatID (FK)

tblInventory
InvenID (Primary Key)
ProdID (FK)
Qty
LocID (FK)
DateEntered
Discontinued
Reorder Level
Cost

tblSales
SalesID (PK)
ProdID (FK)
Qty
LocID(FK)
DateSold
EventID(FK) could be blank
Discount

tblLocation
LocID (PK)
Location Name
Contact Name
Address
City
State
Zip
e-mail
phone
fax
website

tblEvents
EventID (PK)
Event Name
EveConID (FK)
DateStart
DateEnd
Employees
Volunteers
TimeStart

tblEventContacts
EveConID (PK)
Contact Name
Address
Address2
City
State
Zip
Phone
Fax
website
E-mail

I beleive this reflects all of the changes that we discussed at the end of last year. I have added new fields to some of the tables to reflect additional data that I need to store for the contacts.

I have a feeling that many of these components (aka, City and state) will probably be set up in seperate tables with just a link back to contact information. I know that all of our events, and locations will be in the USA, however many vendors are in Ireland. I'm not sure if I could use a combined tblCity, tblState, tblZip for the Events and Contacts perhaps using ZipID in the tables as the link (cascading lists) and then use different tables for Vendors since we do not use zip codes for Irish addresses or if I should somehow combine them and have multiple FK in the tbl Events and Contacts. Perhaps you could give me some imput on that.

I also still have a question regarding the setup of tblSales. I understand that I could use a query to review all of the sales where event was null, and I could also review the sales with a specific event listed. My question is...will it be possible to look at all of the sales within a specific timeframe and not see an item sold from Albany at an Event listed in both locations. Example I want to look at all my sales for March in one report. How would I set this up so that I would not see the necklace sold from Albany at the Irish Festival listed as being sold from Albany and also as being sold at the Irish Festival? Am I making any sence?

I'm also not sure if this is a good time to think about this or not but it would be helpful if we could record payment transaction and methods for each of our sales. How difficult will it be to work this into the database. My coding skills are basic to put it nicely. And I have a feeling that this type of setup may take more skills than I have. What are your thoughts?

I'm hoping that I can get all of this table structure set up figured out within the next week or so because after that I will have limited time to work on it again until later this summer. At least if I know the table structures that I need I can work on getting the data reentered into the new structure, and reworking all my forms, quearies and reports to reflect the new structure in a peice meal fashion, using the exsisting database in the meantime.

Thanks for all your help with this. I really appreciate it!