Device assembly management

Question

I have a db of parts in an Access db. Now I wish to create a kind of construction management.

Imagine to have in warehouse some pieces, and you have to assembly devices starting from them.

I wish to create a query in which I introduce group name and I get a kind of bill of material with available and requested quantities to assembly.

------Example: Warehouse table (already existing)

[code]¦[Quantity]
A¦3
B¦4
C¦0...

------Device "Alpha" is made by (I don't know how to declare these devices composition)

[code of which Alpha is made]¦[Quantity for mounting Alpha]
A¦2
B¦0
C¦1

------I launch the query (as Input I'm going to pass "Alpha", the name of the device) and I get a table like

Device name: Alpha
[code]¦[warehouse qty]¦[required qty to be picked from warehouse]
A¦3¦2
B¦4¦0
C¦0¦1==> error: I can't assembly because 1 piece is missing

Answer 1

The classic model for a bill of materials (BoM) or similar is the 'adjacency list model'.  This simply means that a table with columns such as MajorPartNum, MinorPartNum and Quantity references a single Parts table in which each row represents an assembly or sub-assembly, or an 'atomic' part which is not an assembly of other parts.

To generate a BoM requires recursive querying, which Access does not support.  However, there are a number of standard algorithms for this, and these can be used as a basis for simulating recursive querying by progressively inserting rows into a temporary table at runtime.  You'll find an example of this in BoM.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

As well as the BoM.accdb file which simulates recursive querying, the zip archive includes a PartsTree.accdb file which illustrates a simpler method, which joins multiple instances of the tables in a query.  Unlike the BoM.accdb demo, which will create a BoM to an arbitrary number of levels, the PartsTree.accdb demo does so to a fixed maximum number of levels.  This will be sufficient for many requirements, however.

---

Ken Sheridan, Stafford, England

Answer 2

You have table: WarehouseParts  and table: DeviceParts

put both tables into a query (PickListQuery) joined on the PartID

put in the criteria of device name: Alpha so the results are limited to that part only.

then your PickListQuery  you have a calculated field that does the math i.e.

Picked: (WarehouseTable.PartQty - DeviceTable.PartQty)

obviously if Warehouse is 0 then you'll have a negative number which is a flag for a stocking issue....

you might look online in Access for calculated fields in a query if you are not familiar - they're quite simple....

(be sure the Warehouse Table must always have all parts (even if qty 0) in its table otherwise it needs to be an outer join but let's leave that aside...)