Remanufacturing Database

[equestrian]

I am using Access 2007. I have a thread in the Query section because I was having trouble setting up an Append Query. The more I work on it the more I believe my problem is with the table structures I have. So, I am hoping someone can look at this and give me some ideas.

I am working on a database that has Units that are built with parts. I have a table with the various Unit numbers and a table with the various Part numbers. After the Unit is built, I will need to show the Unit that was built, assign it a serial number, and show the parts that were put in the unit. In addition for each unit I have a Bill of Materials that shows all the parts that can go in the unit. Each part in the BOM has a status of Mandatory or Discretionary. I know I need the following:

tblUnits
UnitNum_PK
Unit

tblParts
ParNum_PK
Part

I need a way to show what parts should be used in a unit when it is built. This is the BOM
Example
UnitA
Part123 qty 3 Mandatory
Part456 qty 2 Discretionary
Part789 qty 1 Mandatory

I also need a way to show when a unit is built and what parts went into that unit
So we build UnitA, give it a serial number, and use the following parts
Part123 qty 3
Part456 qty 1
Part789 qty 1

Currently I have
tblBOM
UnitNum_FK
PartNum_FK
BOMQty
ReplacementType_FK (if =1 mandatory, if=2 discretionary)
ReplacementNote

tblUnitAssy
UnitAssyNum_PK
UnitNum_FK


SerialNum
UnitAssyDate
UnitAssyQty

tblPartAssy
PartAssyNum_PK
PartNum_FK
PartAssyQty

Maybe I should split tblBOM into 2 tables with a PK joining the two?

Maybe add UnitAssyNum_FK to tblPartAssy to join the 2 tables?

[ArviLaanemets]

Hi

tblUnits and tblParts look OK for me.

The BOM list as you have it (really it is BOM row table, but you can take UnitNumber as Bom primary key, unless you need to have different BOM lists for same unit in future) will do too, when you want only to get summary count of parts.

When you want it also to describe, in which order are parts disassembled/assembled, and how many parts you have remove/add at every step, then you need more detailed BOM, with Unit structure described. There are practically 2 ways to do it:

1. You have the BOM row table like
BomID (can be UnitNum)
BomRow (the order number of assembly/disassembly step)
PrevBomRow (after which BOM row is the part(s) added/removed)
PartNum
PartQty
IsMandatory (True/False)
This is (generally) how BOM lists are usually stored in ERP systems. For Access, it my be too slow, as to get the BOM list you have to make step-by-step calculations.

2. You have the BOM row table like
BomID
RowCode (a text field, where fixed length character groups are determing the position of row in BOM list. P.e. 2-character groups, where every group is a numeric string from '00' to '99' or 32-bit numeric string (I prefer Crockford's Base32) from '00' to 'zz' (over 1000 different values). When 32-bit groups are used, then you also have to write functions to convert them to integers and vice versa. And you have to decide, how many levels can your BOM list have - this determines the length of RowCode field. RowCode '00' has UnitNum as PartNum)
PartNum
PartQty
IsMandatory
To get the BOM list with this structure in right order, simply order it by RowCode.

For tblPartAssy, consider having table structure as
PartAssyNum (probably Long Integer Autonumber is best)
UnitAssyNum
RowCode/BomRow, PrevBomRow
PartNum
PartQty
ReplacedQty
IsMandatory

When a new assembly is registered, the BOM list for unit is isnerted into tblPartAssy, along with UnitAssyNum. When PartassyNum is autonumber, then you don't have to bother about it. The only editable field will be ReplacedQty. You can use conditional formatting to mark rows with mandatory replacement, where BOM list quantity doesn't match with replaced quantity.