Totals Query Separating by Lot#

[isoman53]

I am working on a Totals query which will show our current inventory of each part# by lot#. That is to say it will show me how many I have on hand for each lot# when I have more than one lot of any part# in stock. My design involves recording inventory when it goes on the shelf and then recording it when it ships. Then when we need to know an on-hand quantity just subtract one from the other. I can get an overall quantity but have hit a wall in dividing that quantity into separate lot#'s when this exists. I've tried about every way I know how but just can't seem to get this one nailed down.
Any help would be greatly appreciated.

Table Structure: 2 tables

-tblReceiveDetail-
ProductID
LotNumber
Quantity

-tblShipDetail-
ProductID
LotNumber
Quantity

Receive Query:
SELECT tblReceiveDetail.ProductID, Sum(tblReceiveDetail.Quantity) AS SumOfQuantity
FROM tblReceiveDetail
GROUP BY tblReceiveDetail.ProductID
HAVING (((tblReceiveDetail.ProductID)=[Forms]![frmTotalQtyOnHand]![Text2]));

Ship Query:
SELECT tblShipDetail.ProductID, Sum(tblShipDetail.Quantity) AS SumOfQuantity
FROM tblShipDetail
GROUP BY tblShipDetail.ProductID
HAVING (((tblShipDetail.ProductID)=[Forms]![frmTotalQtyOnHand]![Text2]));

On Hand Query:
SELECT qryReceiveTotal.ProductID, ([qryReceiveTotal]![SumOfQuantity])-([qryShipTotal]![SumOfQuantity]) AS Expr1
FROM qryReceiveTotal INNER JOIN qryShipTotal ON qryReceiveTotal.ProductID = qryShipTotal.ProductID;

 

[Brianwarnock]

Re: Totals Query Seoarating by Lot#

Don't you just have to Select and Group on the Lot# in your queries?

Brian

 

[isoman53]

Re: Totals Query Seoarating by Lot#

Don't you just have to Select and Group on the Lot# in your queries?

Brian

Each part# may have more than one lot. The part# is the primary identifier. The lot# is a sequential number starting at "1" for each part number so is not a primary identifier.

Thus I need to group by part# and then Lot# to show how many of each lot# I may have for any part#. Does that help?

 

[Brianwarnock]

Re: Totals Query Seoarating by Lot#

Where is part# is that the same as productid?

Brian

 

[isoman53]

Re: Totals Query Seoarating by Lot#

Where is part# is that the same as productid?

Brian

My mistake, thanks for catching me. Yes each ProductID may have several Lot#'s.

 

[Brianwarnock]

Then grouping by productid and lot# should work.

Brian

 

[isoman53]

I can get a total for each ProductID. The problem is being able to get how many of each ProductID/Lot# there are when I have a balance of more than one Lot# in stock.
Example: What I want to show.
ProductID.............Lot#1...............Lot# 2..........Total
#123456................150..................250..............400

Trying to do this by subtracting Shipped Product from Product put on the shelf keeping in mind the Lot# and that the number of lots can vary.
The Lot# is important so that Shipping can follow FIFO.

 

[vbaInet]

It would be much easier if you show us some more records of your records and then also show us your desired outcome.