Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-03-2019, 01:17 PM   #31
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 34
Thanks: 4
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Table Design & Relationships

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.
Attached Images
File Type: jpg 60cmfieldsCapture.JPG (60.6 KB, 15 views)
Attached Files
File Type: pdf 60CM Inspection Report1.pdf (83.0 KB, 11 views)

PuzzledNH is offline   Reply With Quote
Old 12-06-2019, 07:26 AM   #32
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 34
Thanks: 4
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Table Design & Relationships

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 is offline   Reply With Quote
Old 12-06-2019, 08:59 AM   #33
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,600 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Table Design & Relationships

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 12-06-2019, 10:52 AM   #34
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 34
Thanks: 4
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Table Design & Relationships

Quote:
Originally Posted by Pat Hartman View Post
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.
Attached Files
File Type: zip Shipment Warranty-v.5.1.test.zip (835.1 KB, 4 views)
PuzzledNH is offline   Reply With Quote
Old 12-06-2019, 11:34 AM   #35
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 466
Thanks: 21
Thanked 93 Times in 92 Posts
mike60smart will become famous soon enough
Re: Table Design & Relationships

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.

mike60smart is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Could use some help on table (relationships) design 88scythe Tables 6 12-18-2012 11:49 PM
Table Design/Relationships Dartos Tables 8 09-12-2010 04:47 PM
Table Relationships and design bowks14 Tables 3 02-20-2009 01:52 AM
table design and relationships... snowman Tables 12 03-25-2006 07:56 PM
Help with Relationships and table design: Bill Harrison Tables 7 01-12-2005 06:44 AM




All times are GMT -8. The time now is 05:57 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World