Forum Discussion
Summing an inventory list
I am new to access, but I am creating a simple inventory database for work. So far, I have a list of machines and locations feeding a parts info list. Then I made a form to easily input new parts and update the table. Now, I want to show the total quantity for each part. The Key is different, but the part name is the same for some parts so I would like the parts grouped by the Key number. I've used the report wizard, but it just shows every transaction not a summary. In my most recent attempt, I got it more condensed, but every part is doubled and not updating.
- "new to Access" and "inventory database" is a difficult combination. Correctly tracking inventory is decidedly non-trivial. I would highly encourage you to review the Northwind 2 Developer Edition template and its documentation, and the video https://www.youtube.com/watch?v=nJucF2MFc5A where Kim Young is discussing the inventory module.
Solution 1: You can keep track of the current inventory numbers in a separate table. Whenever some part is added or removed, update the partname record in that summary table. You may use this info in your entry form via a connected subform.
Solution 2: In tiny databases, you can do it on the fly with one of the domain functions in the ControlSource of a text field. It may or may not be too slow for your purpose. May look like this =DSum("[Quantity] * IIF([In/Out]='Out', -1, 1)", "PartsInOutLog", "PartType = '" & Left([PartNumber], 3) & "'"I recommend keeping the RecordSource of your entry forms simple and with no aggregate functions.
Gerrit_Viehmann Do you mind explaining solution 1 a bit more?
- I agree with Tom. Take a look at the Northwind Traders template and see, for example, how inventory is generated from inventory transactions.
