Hi All - new to the forum....and an aspiring yet novice Access user. I am in the QC department of a steel manufacturing plant. We take big coils of steel and roll them into forms.
I am in charge of inspections. I am building an inspection database.
Basic DB structure has tables: tblJobs (general fields pertaining to Job); tblParts (ultimately the finished rolled sheets of rolled steel); tblInspections (junction table for the various types of inspections); tblMillInspections (one of several inspection types but the most relevant tbl for my current question); and tblCoils (the raw material, coils of steel).
We purchase our coils of steel from several suppliers. Each coil has a specific and unique coil number. We suspect that some of the issues we see in the manufacturing process are related to supplier processes.
We have hundreds of coils in house at any time. Coils are not specified for a job prior to the manufacturing process. In other words, we do not know which coil will be used until it is actually being mounted on the mill.
To accommodate this business process which will never change - ever - I have not been using tblCoils at all. I simply have a field in tblMillInspection where I enter the coil number which is stored in tblMillInspection. Then - once I know the coil number of the coil being used for the inspection that I am currently working through, I make an entry in tblCoils where I can add may other pieces of data that are coil-specific and unrelated to the inspection.
This is obviously not efficient at all.
The two fields in tblCoil that are relevant for my question are Coil_PK (autonumber, primary key) and CoilNumber (the literal string, e.g., R30836). As all coils have unique numbers, I suppose I could use CoilNumber as the primary key but I have chosen not too as that coil number might be repeated a lot of times on a number of different job inspections.
What I would like to do - and don't know how to do - is open my inspection form; enter the coil number; and have that entry continue to show as the coil number on my form but in the backend two things happen: 1) a new record is added to tblCoil which means a new Coil_PK and a new CoilNumber are added to tblCoil and 2) the new coil is referenced as foreign key in tblMillInspections for the current inspection taking place linked to the new record in tblCoil.
FYI - the inspection form is frmMillInspeciton and is based on tblMillInspection.
It is also of note that we may use multiple coils on a single job - or we may only use one. Or we may use partial coils or leave partial coils. It depends on the size of the job. That is also why I do not store coil numbers directly with jobs.
Thoughts and ideas?
Thank You!
Tim