I have made some progress on table structure and have broken the original table1 into 4 tables:
Customer, Contract, Delivery Order, DeliveryLocation, and fields for old contact info that are not used and will eventually be deleted. (Approx. 100 records and 14 columns. Half the columns can be deleted once the data is verified to be retained elsewhere.)
The massive collection of multiple field names (and data) that everyone is telling me is data. (Approx 1200 records and 125 columns) Sadly 125 columns covers only 2 product lines. I have a couple dozen existing product lines to include and more being developed.
Product_Line, Part_Number, Description, ProdID (approx 75 records and 4 columns)
Tracking_Info, Shipdate, ShipID, CustID, SerialID (approx 75 records and 5 columns)
As everyone keeps reminding me, I am still struggling with field names vs Data. This is evident in the number of columns in tblProductData
. Thus, tblProductData
needs to be further broken down into smaller tables.
I thought I could focus on breaking down the 60cm product line which is our smallest product line. The PRS11 product line captures 3x this data and other product lines are even larger.
What do I know about the current table structure:
- A product line typically consists of a fully configured "system" containing multiple parts. Each part requires one or more data points to be captured.
- A product line is defined by a top level part number. This part number provides a list of lower level part numbers (one for each part) that define the components of a "system". 60cm part number is 900339 and provides a list of approx. 15 parts identified by a unique part number.
- Each part has at least one data point (with potential for up to ten data points) that are captured via a bar code scanner during the production, test, and inspection process. 60cm captures approx 25 data points (from 15 parts).
- Each data point is unique to the specific part (Identified as a part number). Some parts have only a checkbox verifying part is (or is not) provided. Other parts have data (one or more data points) scanned in via a bar code scanner.
- Once data is entered, a report is printed defining the exact configuration of the system. This report is used by our customer to accept product. A copy of the 60cm Inspection Report is attached. The red circles identify collected data. The blue circles identify associated field names.
- The current table structure for tblProductData has a field for each unique data point. I have attached a list of the current field names for 60cm as shown in the design view of the table. No need to tell me the 60cmis data. The only reason I haven't removed it from the filename is so I can easily differentiate between the 60cm fields and the PRS11 fields while I try to break down the table structure. Once I figure out the best structure I can change the names
- Each part "may" contain one or more (but not all) of the following common data items to capture: MO, Rev, Checkbox, SN (Serial Number), Contract, UID, 3D Code, Qty, FirmwareConfig, SoftwareConfig.
- User will be scanning the data into a form as directed by a test or inspection procedure specific to the product line. This may impact the form design.
- The user will also be printing the report (see Inspection Report attachment). Changes to the report require formal customer approval which can be time consuming and costly.
- I already have a lookup table put together for both 60cm and PRS11 part number lists. These tables are not currently in use.
Note: I cannot
lose any of the historical data. It must be readily accessible for input and reporting as I update the structure. Updates to the data happen most days. At most, I can lock users out for a couple hours.
Any suggestions on what the next step should be to break this table down?
Thank you again for the great support.