Table Design & Relationships

[PuzzledNH]

How did you envision that scanned serial number getting to the correct field? "60cm", "900338" and "SN".

In the normalized world this might be achieve by either direct input for each piece of data--> user selects 60cm from a drop down, 900338 from a drop down and SN from a drop down and then scans;

The data was originally captured on an excel spreadsheet and appears to have been imported into the database as is without any modification to the format. I believe the original intent was to have a field for every piece of data being captured and have forms set up for each product line. I.e. the 60cm dish form would display only those fields associated to the 60cm product line. The PRS11 form would only allow entry into those fields associated to PRS11. Etc. The data would then be direct input by hand or by scanner onto fields displayed from the appropriate form. My Predecessor only had one form set up, so this is just a guess.

I am stuck with the resulting data structure and am trying to determine how to maintain data integrity, keep the database updated with ongoing shipment info, and capture new data (Lower level component info) that was not in the original excel spreadsheet.

Additionally, the organization has a lot of data redundancy. Much of this same info is also scanned onto a Word document used to log inspection results and then provided as a report to the customer. My goal is to capture the info only once in the database and print the customer report from the database.

 

[PuzzledNH]

delete all of the PRS11 items which you currently have as fields in"Table-PRS11_ Data.

The structure would be:-
-PRS11ID - FK (Linked to List of all PRS11 Items in tblPRS11Data
You would then have a Combobox on the Form based on this structure that allows you to select 1 or More PRS11 items and then add relevant data.

Hi Mike,
This doesn't make sense to me. If I delete all the PRS11 fields in TablePRS11Data what am I associating PRS11ID to?
What fields should be in TablePRS11Data (and in the other tables)?

I am still struggling with what the structure should be.
I.e. How many tables, what fields should be in each table, and how the structure will capture the data.

I originally thought I might be dealing with three tables:

• TblCustomer (This would contain fields for customer, address, contract numbers, ect.)
• tblShipment (This would contain fields for tracking number, carrier, date shipped, serial numbers shipped, etc.)
• tblproducts (This would contain fields to capture the product configuration information scanned from the bar code labels such as Rev, UID, 3D code, etc.)

Today I am wondering if the use of product line added confusion to the picture. If I take product line out of the picture (a product line is just a group of categorized parts) I am left with a couple hundred part numbers (One part number per part). At time of shipment, each part requires us to collect certain data. Part 1234 may only need one data item collected. Part 4567 may require 9 items to be collected. If I have one part that needs to collect 5 items of data and a second part that only collects one item of data how do I set this up? This is my current struggle.

Again, my Access experience is limited to a SB course 10 years ago and only one DB made at the time of the course.

• 300 part numbers
• Each part number will have specific data items to collect
• 95% of parts will be collecting similar scanned data items (Rev, UID, 3Dcode, MO_Number, Serial_No, Contract, etc.)
• Some parts will collect one of these data items, some parts may need to collect 10 data items.
• A shipment will consist of a set group of part numbers (we call that set a product line)

Thanks again for any assistance.

 

[mike60smart]

Hi

If you look at your table "Table-PRS11_ Data"

You have a field for each PRS11 item.

This is the standard layout for an Excel Spreadsheet. In other words a Lot of Columns Horizontally.

In access you need to think Narrow but Long.

I suggested the following structure:-

tblCustomerPOItems
-CustomerPOID - PK - Autonumber
-CustomerID - FK (Linked to tblCustomers - PK)
-PRS11ID - FK (Linked to List of all PRS11 Items in tblPRS11Data
-Description

This allows you to select a PRS11 item using a Combobbox

So you would be creating multiple Rows with Each Row being a PRS11 item

 

[mike60smart]

Hi

You also say:-

"At time of shipment, each part requires us to collect certain data. Part 1234 may only need one data item collected. Part 4567 may require 9 items to be collected. If I have one part that needs to collect 5 items of data and a second part that only collects one item of data how do I set this up?"

If you have a Part that might have 1 or more data items then you would cater for this with a 1 to Many structure.

A table for storing the Individual Part and then a table to store All Related Data Parts for the Part Selected.

 

[PuzzledNH]

I tried to use the Database Tools - Analyze Table and split Table1 into two tables to separate Customer info from the remainder of the data. I ended up with a Query called Table1 and Two Tables Customer and Table2. Table1 was renamed Table1_OLD. The Customer table is linked via ID (PK) to Customer_ID (FK) (1 to many) in Table2. The data in the tables looks OK. However, I have a couple questions:

1- Is the new Table1 Query now a permanent part of the database or is it only used during the initial splitting of the tables?

2-The form that was created to input data into the original Table1 Table has a number of fields that indicate invalid control property: control source, No Such field in the field list. Why do only some of the fields have this error message? If the original Table1 was renamed, why are some fields not showing an error?

Thank you again for the help.

 

[PuzzledNH]

I have made some progress on table structure and have broken the original table1 into 4 tables:

tblCustomer 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.)

tblProductData 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.

tblProductLine Product_Line, Part_Number, Description, ProdID (approx 75 records and 4 columns)

tblShipment 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.

 

[PuzzledNH]

I am still working to break down tblProductData

One idea I am poking at is based on the fact that each part number collects a maximum of 10 possible bits of data that would need to be retained. Each part number "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.

This would reduce the table into 10 columns if I can figure out how to set up a table that takes the tblProductData field names and put them into a part number field in a new table. Does this make sense or am I still way off track?

Thanks.

 

[PuzzledNH]

Converting the old data is not a problem once you define the schema.

Many of the COLUMNS in the ProductData table will become ROWS in the components table. I think one of my earlier suggestions told you how to start with splitting this table. If you can post a database with this table that includes the actual data presented on the document you posted, that will help us to help you to sort this out. If the document is actually a report that the app produces, include that report also.

Hi Pat,

A copy of the test database is attached.

I have one form Form-60cm Dish that users are currently using to input or scan data into the database. Each new product line will have a similar data entry form. (FormShipment and associated subform are being experimented with and not fully operational, so can be ignored.)

I also have one report Report-60cm Inspection Report that is in current use. This generates the same document presented in my previous post. It uses QuerySerialNumber as a lookup to print the report for that particular system. Serial numbers that would provide "accurate" data in this report would be in records 1166 through 1207 in the tblProductData System_SN column. Most other records are for other products so would result in no data in the report. Once fully developed, those other product lines will have a report presenting similar info.

Thank you.

 

[mike60smart]

Hi Dave

Your data in tblProductData are multiple columns that refer to a number of Systems.

It is a spreadsheet because you have Columns for every field therefore creating a very Wide view of the Data.

You must in Access think very Narrow so that every Column in tblProductData will become a Field in a table.

If you create a table that lists the Various Systems ie

Table structure:-
tblSystems
-SystemID - PK - Autonumber
-System
This will list all of the systems:-
60 Cm Dish
xxx
yyy
Etc....

Then create a related table that Contains all of the Parts for All of the Systems

Table structure:-
tblSystemParts
-SystemPartID - PK - Autonumber
-SystemID - FK - Number (Linked to PK from tblSystems)
-SystemPart - Text

You can then create a Form where you Select the Specific System.

Then a Subform would be displayed containing only those System Parts that Relate to the Selected System.