How best to remove different quantities from multiple records?

[Cuda426]

I have a small Inventory DB, main field is Part Number. I am trying to remove
different amounts from different part numbers when you remove 1 item.
Multiple parts go into a welding bug. When one is complete, trying to go into
inventory and select "Complete Bug" and have it remove 1 from Quantity field
for part number 001 and remove 2 from quantity field from part number 002.
Field names affected will be [Part Number] and Quantity. If anyone has done
something similar, and help would be greatly appreciated.

I am not sure if it would better or capable to do this with a
Macro/Query/Stored Procedure?? Thanks

 

[Pete D.]

You would need to have a table of Bug requirements

For instance you would have an assembly table linked to (many to many)
listing the items/parts qty used for this assembly. Then you would use a
combination of VBA and SQL to add/subtract items as needed for each assembly
created from your table. By using a table with assembly and one with part
quantities of
items required your assembly one VBA/SQL routine could handle all the
different assemblies. You would add new assemblies and item/qty as you
create new versions of your assembly. The VBA would call the SQL adjusting
variables with quantity of parts from the part table.



Now you might add a reserved/orders table of some kind. This table would
list orders and part qty reserved so your
inventory would reflect that you have committed x amount of parts and for
future requests you need to order additional stock.



I have never done this but it does sound like a fun project. You are also
getting into the advanced level but with some experimenting and specific
questions here you can do it. Might look at



http://allenbrowne.com/AppInventory.html



for some thoughts on inventory database.