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...
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.
