Inventory Help

[3239]

I am having a time with this database. I pretty much have everything working the way I would like but I am having trouble with one last piece. This database was created to keep track of inventory received from a vendor to our Warehouse , shipped to location, and the amount distributed from that location. I am able to keep track of items that are received into the Warehouse and items that are shipped out to locations, but when I get to the part where I have to keep track of the items that the location distribute, I run into a problem. What I am really trying to do is get it to where the amount distributed from the location reconcile with the On hand total at our Warehouse.


I tried to attach the file but it was too large to post here.

thanks for you help
tony

 

[TommyJ314]

I think you need to be a little more specific in your description of the problem. What exactly is the nature of it, are you looking for help on table design? table relationships? queries to do the reconciling?

I'm also not very clear on these three types of information you're trying to track, and what their differences are.

 

[3239]

I apologize for not explaining myself well. I am a beginner when it comes to creating functions and using VBA to do calculations. What I am trying to do requires programming and I have posted a function below that creates a temporary table to track whats received,shipped,and whats on hand. I got this code from another source and what I have been trying to do is modify the code so that it will also track the inventory of every location the warehouse ships to. Every location the warehouse ships to dispenses 1 of every item it receives from the warehouse.
I wish I could email the database to you so you can get a clear understanding of what I am trying to do.

'This function updates OnHandbyMat and OnHandbyLot tables when material is added, dispensed, and pick-upFunction Compute()
On Error GoTo Compute_Err

DoCmd.SetWarnings False

'This SQL creates a temp table with the SUM of QuanityRcvd per MatID&{LotID} from TrackRecieved DoCmd.RunSQL "SELECT TrackReceived.MatID, [LotNum] & ', ' & [ExpDate] AS LotID, " & _
"Sum(TrackReceived.QuantityRcvd) AS SumOfQuantityRcvd INTO " & _
"TempComputeReceived FROM TrackReceived GROUP BY TrackReceived.MatID, " & _
"[LotNum] & ', ' & [ExpDate];"

'This SQL creates a temp table with the SUM of Quant iff the record is PickedUp=True DoCmd.RunSQL "SELECT TrackDispensed.MatID, TrackDispensed.LotID, " & _
"Sum(TrackDispensed.Quant) AS SumOfQuant INTO TempComputeDisp " & _
"FROM TrackDispensed WHERE ((TrackDispensed.Allocated) = True) " & _
"GROUP BY TrackDispensed.MatID, TrackDispensed.LotID;"

'This SQL creates a temp table that is a RIGHTJOIN b/w the two temp tables and finds OnHand=(SumOfQuantityRcvd - SumOfQuant)
DoCmd.RunSQL "SELECT TempComputeReceived.MatID, TempComputeReceived.LotID, " & _
"TempComputeReceived.SumOfQuantityRcvd, TempComputeDisp.SumOfQuant, " & _
"Nz([SumofQuantityRcvd])-Nz([SumofQuant]) AS OnHand INTO TempComputeOnhand " & _
"FROM TempComputeDisp RIGHT JOIN TempComputeReceived ON " & _
"(TempComputeDisp.MatID = TempComputeReceived.MatID) AND " & _
"(TempComputeDisp.LotID = TempComputeReceived.LotID);"

'This SQL inserts OnHand values into OnHandbyMat table
DoCmd.RunSQL "SELECT Material.MatID, Material.MatName, " & _
"Sum(TempComputeOnhand.OnHand) AS SumOfOnHand INTO OnHandbyMat " & _
"FROM Material LEFT JOIN (MatDetails LEFT JOIN TempComputeOnhand ON " & _
"(MatDetails.MatID = TempComputeOnhand.MatID) AND " & _
"(MatDetails.LotID = TempComputeOnhand.LotID)) ON " & _
"Material.MatID = MatDetails.MatID GROUP BY " & _
"Material.MatID, Material.MatName;"

'This SQL inserts OnHand values into OnHandbyLot table
DoCmd.RunSQL "SELECT Material.MatID, Material.MatName, MatDetails.UnitOfIssue, " & _
"MatDetails.LotNum, MatDetails.ExpDate, TempComputeOnhand.OnHand, " & _
"MatDetails.LotID INTO OnHandbyLot FROM Material INNER JOIN " & _
"(MatDetails LEFT JOIN TempComputeOnhand ON " & _
"(MatDetails.MatID = TempComputeOnhand.MatID) AND " & _
"(MatDetails.LotID = TempComputeOnhand.LotID)) ON " & _
"Material.MatID = MatDetails.MatID ORDER BY " & _
"Material.MatName, MatDetails.ExpDate;"

DoCmd.DeleteObject acTable, "TempComputeDisp"
DoCmd.DeleteObject acTable, "TempComputeReceived"
DoCmd.DeleteObject acTable, "TempComputeOnHand"

DoCmd.SetWarnings True

Compute_Exit:

Exit Function

Compute_Err:
MsgBox err.Description
Resume Compute_Exit

End Function