How to track and query inventory properly. Advanced relationship queries

[pdevito3]

Hi guys, I am making a database that tracks inventory for products and I am at a wall. I want to make sure my approach is sound, and find out how I can improve the functionality of what I am trying to do.

I have three tables: 1) tblProductList (simply lists all of my products) 2) tblProductInventory (tracks the quantty of the products that are coming in) 3)tblProductBurnRate (track the qunatity of products that are being used.

For simplicity sake, let's say tblProductList has just an autonumber field and the product name. tblProductInventory has productid (refering back to the product list autonumber, with a renforced 1-many relationship) and the quantity of that shipment. tblProductBurnRate has productid ( again refering back to the product list autonumber, with a renforced 1-many relationship) and the quantity of that was used.

I can query for everything I have coming in with tblProductInventory and I can query for everything I've used with tblProductBurnRate. Both are very simple and straightforward. My porblem comes when I try and see what I have left after products have been used. You would think that it would be a simple subtraction between each of the two numbers, but access isn't liking my approach. It seems like it is having trouble knowing that product1 from tblProductInventory goes with product1 from tblProductBurnRate. Below is my query code for each of the successful indivudal queries and for one of my many unsuccessful ones.

Appreciate any help you guys can give.



SELECT tblProductList.ProductName, Sum(tblProductInventory.Quantity) AS SumOfQuantity
FROM tblProductList INNER JOIN tblProductInventory ON tblProductList.ProductID = tblProductInventory.ProductID
GROUP BY tblProductList.ProductName;



SELECT tblProductList.ProductName, Sum(tblProductBurnRate.QuantityUsed) AS SumOfQuantityUsed
FROM tblProductList INNER JOIN tblProductBurnRate ON tblProductList.ProductID = tblProductBurnRate.ProductID
GROUP BY tblProductList.ProductName;



SELECT tblProductList.ProductName, Sum([tblProductInventory]![Quantity])-Sum([tblProductBurnRate]![QuantityUsed]) AS Expr1


FROM (tblProductList INNER JOIN tblProductInventory ON tblProductList.ProductID = tblProductInventory.ProductID) INNER JOIN tblProductBurnRate ON tblProductList.ProductID = tblProductBurnRate.ProductID
GROUP BY tblProductList.ProductName, Sum([tblProductInventory]![Quantity])-Sum([tblProductBurnRate]![QuantityUsed]);

[June7]

Don't bother with the table joins in the first two. Just do the aggregate calc with the ProductID and Quantity fields and group by ProductID.

Then join the first two queries to tblProductList, join type "Include all records from tblProductList ...".

[pdevito3]

I wasn't completely sure what you were getting at June7, but I tried several union queries and some detailed select queries, but no luck. Here are some of the queries I tried:

SELECT tblProductBurnRate.ProductID, Sum([tblProductInventory]![Quantity]-[tblProductBurnRate]![ProductID]) AS Remaining
FROM tblProductBurnRate, tblProductInventory
GROUP BY tblProductBurnRate.ProductID;

------
SELECT tblProductInventory.ProductID, Sum(tblProductInventory.Quantity) AS Quantity
FROM tblProductInventory
GROUP BY tblProductInventory.ProductID
UNION ALL SELECT tblProductBurnRate.ProductID, Sum(tblProductBurnRate.QuantityUsed*-1) AS Quantity
FROM tblProductBurnRate
GROUP BY tblProductBurnRate.ProductID;

------

SELECT tblProductBurnRate.ProductID, Sum([tblProductInventory]![Quantity]-[tblProductBurnRate]![ProductID]) AS Remaining
FROM tblProductBurnRate, tblProductInventory
GROUP BY tblProductBurnRate.ProductID
HAVING (([tblProductBurnRate].[ProductID]=[tblProductInventory].[ProductID]));

[June7]

Could probably use the UNION as source for another GROUP BY query but that won't show products with no transaction data. My suggestion is to join the two aggregate queries to tblProductList in a simple SELECT query. Just pull the two queries and tblProductList into the query designer and set links on the ProductID fields.

[pdevito3]

Got it! With this method, I just have to make sure that I enter a value in the inventory, eve if it's zero, so the product is picked up.

SELECT tblProductList.ProductName, Sum([Query1]![What We Had Before]-[Query2]![What We Lose]) AS [What We Have Left]
FROM (tblProductList INNER JOIN Query2 ON tblProductList.ProductName = Query2.ProductName) INNER JOIN Query1 ON tblProductList.ProductName = Query1.ProductName
GROUP BY tblProductList.ProductName;

[June7]

Don't need dummy record if you do what I suggested about joining the aggregate queries to tblProductList.

[pdevito3]

That would be great to not have to use dummy files, but aren't the queries linked to the productID in the table already?

[June7]

Don't use INNER JOIN and won't need dummy record.

ProductID is set as primary key. Why aren't you saving the pk as fk? If you are actually saving the ProductName as fk then it should be set as the pk in tblProductList.

[pdevito3]

Well, I thought I might have known what you were getting at... I made the ProductName in tblProductList the pk and changed the productID fields in the burn and inventory tables to text and it still didn't come up right. Guess you meant some other approach? Lol

[June7]

Can't just change the ProductID fields in the burn and inventory tables to text. They must actually have the name values in those fields. If you have actually saved the ProductID (autonumber ?) into the burn and inventory tables then all you had to do was set the ProductID as PK. Then set the links in queries to the ProductID field.

Do you have Lookups set in the tables? I NEVER do that. Review: http://access.mvps.org/access/lookupfields.htm

What value is REALLY stored in the ProductName fields of burn and inventory tables?

[pdevito3]

Well, currently I have it set up where tblProductList has an autonumber field (ProductID) and a ProductName field as text. tblProductInventory has ProductID, which refers back to the tblProductList autonumber (with a renforced 1-many relationship from the ID to the ID) and the quantity of that shipment. tblProductBurnRate also has ProductID, which refers back to the tblProductList autonumber (with a renforced 1-many relationship from the ID to the ID) and the quantity of that was used.

The ProductName in the image was from the queries which drew from tblProductList and their respective burn or inventory table. I put in ProductName so it would show the name and not the number. Also, learned my lesson about lookups in tables very early on-- bad idea-- I never do it either.

Any advise?

[June7]

Are you saying that ProductID and ProductName are both saved in burn and inventory tables? Should not save both. Save only the PK from tblProductList into burn and inventory. The ProductName would be retrieved from tblProductList by joining tables in query.

So unless you want to use ProductName as pk/fk, remove product name data from burn and inventory tables. Join tables on the ProductID pk/fk fields.