Microsoft Access Inventory

Question

Hi, I have an inventory system in Access and I have been attempting to automatically remove quantities of items when a single item's quantity is removed. For example, I have a single door and when I remove it I would like the several parts quantities that are included on the single door to be removed automatically. 

1 door

4 corners per door (automatically removed)

3 rubbers per door (automatically removed)

1 lock strap per door (automatically removed)

I'm not sure if this possible but I thought I would ask. Thanks.

Answer 1

Hi Grant, I'm an independent adviser and will try to help.

What you ask is possible, but its not the right way to do it. Quantity on Hand is a calculation and, as a general rule, we don't store calculated values. Quantity On hand can be calculated by adding the incoming transactions and subtracting the outgoing ones.

What you have is referred to as a Bill of Materials (BOM). So a door is composed of several components. So you need a table of top level parts (i.e. doors) and a table of component parts with a foreign key pointing to the top level part. Next, when you process an order for a door you need to automatically create component transactions for corners, rubbers and lock strap.

Finally, you create a query that adds purchases for components and subtracts transactions for used components.

If you need further clarification on this please feel free to ask.

Answer 2

I agree with George.

If it is properly implemented, the concept of an "assembly" can accomplish this. This would be a product that consists of several products and their contributing quantities.

"self join" may also be in the picture. A lot depends on finer points of your needs (and skills, frankly).

Answer 3

It might be possible, depending on how your tables are set up.

So, if we can get more detail on how your tables are set up, we can possibly help with the queries that'll accomplish the task.