Multiple location inventory

[Testate]

I am trying to create a multiple location inventory. I have based my attempt off the Northwind Traders Database. To start, I created a mock database with tables of: Products, Warehouses, InvTrans, InvTransType.

The inventory works if any product is added or removed at one warehouse. However, once I add inventory to multiple warehouses and remove inventory from one, it messes up (it subtracts the 'sale' from all warehouses, not just the one it was sold from).

I have attached my simple starting point database. Can anyone suggest where my query's are wrong, or what I need to add?

[rpeare]

Add a third field to your InvTransType table
Name that field IT_CATEGORY
Put a C in the STOCK record
Put a D in the SELL record
(c for credit d for debit)

Run this query:

SELECT InvTrans.WID, InvTrans.PID, Sum(IIf([it_category]="C",[qty],0)) AS Receipt, Sum(IIf([it_category]="D",[qty],0)) AS Disbursement, Sum(IIf([it_category]="C",[qty],0)-IIf([it_category]="D",[qty],0)) AS OnHandFROM InvTransType RIGHT JOIN InvTrans ON InvTransType.ITTID = InvTrans.ITType
WHERE (((InvTrans.ITDate) Between [enter start date] And [enter end date]))
GROUP BY InvTrans.WID, InvTrans.PID;

Modifying your InvTransType table this way will also allow you to track internal transfers (transfer in/transfer out) and allocate the positives and negatives correctly

[Testate]

Add a third field to your InvTransType table
Name that field IT_CATEGORY
Put a C in the STOCK record
Put a D in the SELL record
(c for credit d for debit)

Run this query:

SELECT InvTrans.WID, InvTrans.PID, Sum(IIf([it_category]="C",[qty],0)) AS Receipt, Sum(IIf([it_category]="D",[qty],0)) AS Disbursement, Sum(IIf([it_category]="C",[qty],0)-IIf([it_category]="D",[qty],0)) AS OnHandFROM InvTransType RIGHT JOIN InvTrans ON InvTransType.ITTID = InvTrans.ITType
WHERE (((InvTrans.ITDate) Between [enter start date] And [enter end date]))
GROUP BY InvTrans.WID, InvTrans.PID;

Modifying your InvTransType table this way will also allow you to track internal transfers (transfer in/transfer out) and allocate the positives and negatives correctly

Absolutely beautiful! Can't thank you enough.
I modified it to remove the date requirement.
I also removed the "C" and "D" component, which I believe you put in because it allows for internal transfers. At this time, I am not smart enough to see the difference between using the AutonumberID vs using the IT_Category. Maybe you could enlighten me?

SELECT InvTrans.WID, InvTrans.PID, Sum(IIf([ITTID]=1,[qty],0)) AS StockIn, Sum(IIf([ITTID]=2,[qty],0)) AS StockOut, Sum(IIf([ITTID]=1,[qty],0)-IIf([ITTID]=2,[qty],0)) AS Stock
FROM InvTransType RIGHT JOIN InvTrans ON InvTransType.ITTID = InvTrans.ITType
GROUP BY InvTrans.WID, InvTrans.PID;

[rpeare]

The reason I categorized them is that if you ever DO use any category other than the two you originally created, all your queries will become useless and you will have to re-work them. You may not see the use now, but I always plan for as much flexibility as I can, specifically so I *don't* have to re-write anything. I don't typically hard code variables into my queries and rely a lot on pulling criteria from list boxes, combo boxes or specific categorizations based on table structure.

For instance, let's say you try, at some point in the future, to connect this to some sort of inventory management, you'd need a way to handle discrepancies in expected on hand quantities vs actual on hand quantities, a way to handle stock that is intentionally discarded, etc. In your current structure you'd have to note all of these as 'sell' because they are negatives to inventory which really isn't accurate. So if you added another category for 'Non Sale Losses', for instance, everything query, report, form that does anything with the ID field would have to be re-written to handle the new category.

[Testate]

The reason I categorized them is that if you ever DO use any category other than the two you originally created, all your queries will become useless and you will have to re-work them. You may not see the use now, but I always plan for as much flexibility as I can, specifically so I *don't* have to re-write anything. I don't typically hard code variables into my queries and rely a lot on pulling criteria from list boxes, combo boxes or specific categorizations based on table structure.

For instance, let's say you try, at some point in the future, to connect this to some sort of inventory management, you'd need a way to handle discrepancies in expected on hand quantities vs actual on hand quantities, a way to handle stock that is intentionally discarded, etc. In your current structure you'd have to note all of these as 'sell' because they are negatives to inventory which really isn't accurate. So if you added another category for 'Non Sale Losses', for instance, everything query, report, form that does anything with the ID field would have to be re-written to handle the new category.

Thanks for the explanation. I agree and will put the 'category' back in.
I truly appreciate the assistance.