Table Design & Relationships (1 Viewer)

plog

Banishment Pending
Local time
Today, 08:18
Joined
May 11, 2011
Messages
11,611
First and foremost, I wouldn't concern yourself with forms at this point. They are the last portion of Access development. After you set your tables, you should move on to Reports and the queries that support them. There's no point making beautiful forms if your just throwing your data down a hole from which you can't effectively retrieve it. Reports and the queries that support them make sure your tables are capturing your data correctly and that you can retrieve it in the manner you need.

With that said, let's explore this:

My struggle comes from how do I associate the scanned serial number to the specific part number being scanned?
...
I.e. Item 4 Scan1 enters scanned serial number data into the field for 60cm-900338-SN.

How did you envision that scanned serial number getting to the correct field? Even in your unnormalized database, at some level a person had to let the computer know that the next piece of data was to be associated with "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;

or via a subform--> user enters 60cm and 90038 on the main form, then every item in the subform inherits that data and the user selects SN and then scans in the data in the subform.

Your tables and their relationships dictate how your forms operate. The way your forms operate does not dictate your tables and their relationships.
 

PuzzledNH

Registered User.
Local time
Today, 09:18
Joined
Nov 4, 2019
Messages
36
Hi

In your table "PRS-11-Data" you have the following fields together with

So when you create your Form for Data Input what value would you Input for each of the following fields:-

Hi Mike,
Below are some sample data (in Bold) that could be entered in the field along with a brief description on how we would enter it.

PRS11-930424-SN
BN12345 , BN34567 , 12547839BC12345
Serial numbers are scanned in using a bar code scanner and will vary from part to part and product line to product line. This particular part (reference the first two examples above) has a 2 character alpha and 5 character numerical serial number that cannot have duplicates.Other parts may be tracking manufacturer serial numbers and an unknown number of characters. (reference the third example above)​

PRS11-930424-Contract
FA8823-18-F-0001 , GX-1478
This could be a contract number or purchase order and may vary from shipment to shipment. One customer may have multiple contract numbers. The info for this particular field is scanned in from a bar code applied to the part. It would then be compared against a contract number in the customer table to confirm it contains the correct info.Multiple part numbers may ship on one Contract.​

PRS11-930424-MFR
71HU5, Robertson Machine
The info for this particular field is scanned in from a bar code applied to the part. The five characters are assigned by our customer and must match the assigned number (I am thinking this would be a field in a lookup table ProductLine table?).​

PRS11-930424-UID
71HU5 BH0028 , 71HU5 AC0147
The info for this particular field is scanned in from a bar code applied to the part. The first 5 characters are assigned by our customer (See above), The characters after the space should match the part serial number. This is a check to verify the data on the bar codes and the data on the contract all match and results would be output on a report for the customer.​


PRS11-930424-Part
930424
The info for this particular field is scanned in from a bar code applied to the part. It would then be compared against the six digit part number (a field in the ProductLine table?) to verify it contains the correct info.​


PRS11-930424-3D_Code
[)>DDMFR 71HU5Part No 930424SER NO BH0028
The info for this particular field is scanned in from a bar code applied to the part. Sections of the scan would then be compared against the UID, part number, serial No, etc. that were scanned for this part to verify it contains the correct info.​

Hopefully this makes some sense.
Thank you for the help.
 

mike60smart

Registered User.
Local time
Today, 13:18
Joined
Aug 6, 2017
Messages
1,899
Hi

OK , So what you need to do is delete all of the PRS11 items which you currently have as fields in"Table-PRS11_ Data.

This is just Excel in Access.

You need a table for Purchase Order Items that is linked to Customers.

The structure would be:-

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

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.
 

PuzzledNH

Registered User.
Local time
Today, 09:18
Joined
Nov 4, 2019
Messages
36
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

Registered User.
Local time
Today, 09:18
Joined
Nov 4, 2019
Messages
36
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

Registered User.
Local time
Today, 13:18
Joined
Aug 6, 2017
Messages
1,899
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

Registered User.
Local time
Today, 13:18
Joined
Aug 6, 2017
Messages
1,899
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:18
Joined
Feb 19, 2002
Messages
42,970
The structure would be:-

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

@puzzled,
In #22, you listed a number of field names. Looking at them they are formatted as:
prefix-suffix. The prefix is the name of some product line but the suffix is the generic name for the attribute.

Try making a table with all the prefixes removed so you have only the suffixes. Compare the tables for product 1 to product 2 and the others, Some will be common to all product lines. Move those fields to a new table called tblProductLine. Replace all the moved columns with a single field named ProductLine. Now we can work on the remaining fields to see if we can find any commonalities. If we can't, your best design plan might be an entity - attribute schema. It is most common in applications where items have disparate attributes and adding a new item can easily add new attributes so you don't want to use the normal method of assigning each attribute to a column because that blows up the number of tables you need or if you try to put the attributes in a single table, then you end up with large numbers of empty columns since sets of columns are only used in certain situations.
 

PuzzledNH

Registered User.
Local time
Today, 09:18
Joined
Nov 4, 2019
Messages
36
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:18
Joined
Feb 19, 2002
Messages
42,970
The splitter wizard relies on being able to recognize patterns of duplicated data. It has no way of identifying repeating groups (columns that should be rows) nor can it identify column names that represent data. The wizard is just not smart enough. Even very experienced people sometimes cannot "see" column names that are data. Some of us however, have worked in enough industries and seen a huge variety of both back office and line of business applications to have some sense of what is going on with your app.

I hope you don't mind me saying this but you are in over your head here. Your predecessor didn't understand database design and left you with a mess. There is no way to fix what you have. You are trying to make what he left you work but you really need to step away from the keyboard and get a handle on the business and its data before you can try to create a meaningful schema.

I still have no idea what products your company builds. With the exception of the left most columns of the spreadsheet, the names used to refer to attributes are simply gobbledygook.

Making planes is not much different from making bicycles and both have a lot of similarities to making shampoo although shampoo is more like making aspirin or steel since formulas are somewhat different from bills of material. I'm guessing that a lot of the complication of your process is due to customization but trust me when I tell you that no two dreamliners come off the line with absolutely identical components since some items can be substituted in the build phase based on availability. One of the apps I created for Boeing managed the "as designed", "as planned", and "as built" configurations for ALL their aircraft in ONE set of tables.

You never said whether the original developer was an employee or a consultant but he didn't know what he was doing. I'd like you to ask your management for permission to hire an expert designer to at least help you come up with a schema and some plan for proceeding. You might learn enough during the process to take it from there. You don't technically need an Access person to define the schema since anyone who understands databases can come up with a schema that will work whether the BE is Access (Jet/ACE) or SQL Server or Oracle. It would be easier for you if the person were an Access expert but those are few and far between. If you live in a big city, you might get lucky and find someone who can work on site with you or you can try your luck with one of the experts here. I would avoid like the plague any people who develop web applications since they rarely are competent database designers.
 

PuzzledNH

Registered User.
Local time
Today, 09:18
Joined
Nov 4, 2019
Messages
36
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.
 

Attachments

  • 60CM Inspection Report1.pdf
    83 KB · Views: 157
  • 60cmfieldsCapture.JPG
    60cmfieldsCapture.JPG
    60.6 KB · Views: 137

PuzzledNH

Registered User.
Local time
Today, 09:18
Joined
Nov 4, 2019
Messages
36
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:18
Joined
Feb 19, 2002
Messages
42,970
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.
 

PuzzledNH

Registered User.
Local time
Today, 09:18
Joined
Nov 4, 2019
Messages
36
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.
 

Attachments

  • Shipment Warranty-v.5.1.test.zip
    835.1 KB · Views: 141

mike60smart

Registered User.
Local time
Today, 13:18
Joined
Aug 6, 2017
Messages
1,899
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.
 

Users who are viewing this thread

Top Bottom