Cascaded many-to-many form question

[twangsness]

Hi All -
I'm designing an Access database to keep track of equipment we're delivering. Here's the specifics:

Tables:
1. CM_ITEM [Configuration Item Table]:
- ID (key)
- OPPN_ID [part number id]
- OP_SN [our serial number]
- Manuf_SN [manufacturer's serial number]

2. PARTNO [Available part numbers]:
- ID (key)
- Nomenclature [part number id]
- OP_PN [our part number]
- Manuf_PN [manufacturer's part number]

3. CAL_PARM_LIST[List of available calibration parms]
- ID (key)
- CalParamName [available cal params]

4. PARTNO_CALPARM_JCT [Junction table for Cal and P/N]
- ID (key)
- OPPN_ID [index into PARTNO table]
- CalParm_ID [index into CAL_PARM_LIST table]

Here's the questions:

A. Each Item under configuration management (CM_ITEM) can be any of the available part numbers in PARTNO. When a particular part is manufactured, we assign it a serial number and want to add it to the CM_ITEM table using only the available part numbers from PARTNO. I've figured out how to do this. However, some of the part numbers require unique calibration values to be saved in the CM_ITEM table. I don't want to add CalParm1, CalParm2, ... , CalParmN to the CM_ITEM table. I do want to have a list of available Calibration Parameters (CAL_PARM_LIST), and for the part numbers in CM_ITEMS that require calibration, store the Calibration Data per parameter in CAL_DATA [CalParamValue]. Unfortunately, I'm completely lost on how to do this.

If anyone out there wants to take a whack at this, I would be most grateful.

Thx,

[nico5038]

I would probably use two tables:

1. tblPart
- ID_Part
- OPPN_ID [part number id]
- OP_PN [our part number]
- OP_SN [our serial number]
- Nomenclature [part number id]
- Manuf_PN [manufacturer's part number]
- Manuf_SN [manufacturer's serial number]

2. tblCalParm
- ID_CalParm
- ID_Part
- CalParmName
- CalParmValue

The partnumbers available will be the rows in tblPart with an empty [our serial number].
The number of Parms can be made flexible by storing the name and value and the ID_Part will be the foreignkey to get them "grouped".

Getting the idea ?

Nic;o)