Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-15-2019, 12:13 PM   #16
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,419
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Table Design & Relationships

Also,

Quote:
PRS11 (as it stands in the database today) is not data...PRS11 product line consists...
Your so close to the data, you've lost sight of what actually is data. PRS11 represents a product line, therfore it is data. Data goes in as values in fields, not in field names. It goes into a field that describes in general what that data is a specific example of. In this case you should have a field named: [ProductLine]

plog is offline   Reply With Quote
Old 11-15-2019, 09:24 PM   #17
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,303
Thanks: 15
Thanked 1,597 Times in 1,517 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

Quote:
If all the components were captured in one table, the table might have a couple of hundred columns.
You are really struggling with the concept of data being rows rather than columns. There is no reason in the world that I would ever need to know all the names of all the components of all the products in order to define the schema. Let's abstract this a little. If you were defining a database for a school would you create a separate table for each family and name the columns based on the names of the children in the families so Familyx has columns named Sue, John, Joe and FamilyY has columns named Mary, Susie, Jimmy, Al, Hal, David, and 12 more? This essentially what you are doing.

Does Boeing keep a separate table for each model plane? Does Pratt&Whitney keep a separate table for each model engine? Does Clairol keep a separate table for each product line so shampoo is one table, Hair dye is another, Conditioner a third and then we get into the cleaning products. The answer in all cases is no. Boeing only makes planes and Pratt only makes engines but Clairol makes a multitude of different personal and home care products with completely different formulations and component lists. Yet they all use a single product table.
__________________
Bridge Players Still Know All the Tricks

Last edited by Pat Hartman; 11-15-2019 at 09:29 PM.
Pat Hartman is offline   Reply With Quote
Old 11-15-2019, 10:09 PM   #18
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,708
Thanks: 93
Thanked 1,708 Times in 1,581 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Table Design & Relationships

One of the most important skills that you need to learn is alternation of scope. There will be times when you need to get into the weeds. There will be time that you have to look at the overgrown field as a single thing. Right now, you are focusing on the weeds and not recognizing the overgrown field.

When you make tables for the purposes you have described, you are building a business model of some sort. The idea is to build abstractions so that you can treat them all in the same way at some level. You put together those things that are treated similarly. Maybe the detailed contents are different, but you probably treat each product line in the same way at the high-altitude viewpoint. And that is where all of those products you are naming become simple data records in a single table that doesn't contain a serial number or part number as a table name OR as a field name.

As my managers often did for me, I'm suggesting that you try to step away from where you are standing and look at the big picture. Not trying to be a downer here, but if you cannot step away and see the problem in overview, you will not be able to make this project work in a reasonable time frame because you will rapidly become overwhelmed with an intractable number of tables.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 11-18-2019, 07:25 AM   #19
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:
Your so close to the data, you've lost sight of what actually is data.

You are really struggling with the concept of data being rows rather than columns.

Right now, you are focusing on the weeds and not recognizing the overgrown field.
OK, I get the hint.
I am struggling with what is data vs what should be considered field names. And I thank everyone for pointing me in the right direction.

However, If I am looking at the following fields for the table:

Quote:
tblComponents
comp_ID, autonumber, primary key
ID_ProductLine, number, foreign key to Product line table
comp_Type, string, type of component--it sounds like you want to jam this in a field name
comp_Value, string, this is holds the value you are putting in your table currently

...I am also struggling with how I present the fields to the person who will be entering the data so I am collecting the correct system configuration for the shipment. As an example, I have a form "Form-60cm Dish" that captures scanned data and inputs it in a specific field.
I.e. Item 4 Scan1 enters scanned serial number data into the field for 60cm-900338-SN.
Item 4 Scan2-Contract enters scanned contract number data into the field for 60cm-900338-Contract.


I already know the table structure needs to be laid out before any form is created. This was set up as a sample to show the specific info the 60cm product line must capture based on a documented inspection instruction that tells the person how and what to capture. So no need to beat me up on this. .

My struggle comes from how do I associate the scanned serial number to the specific part number being scanned?

If I have this correct, the scanned data would be collected in the tblComponents table. Each scan would go into a new line
comp_ID, autonumber, primary key
which is the primary key for this table. This part makes sense to me.

I would have a field for each data type to be collected.
comp_Value, string, this is holds the value you are putting in your table currently

I.e. In the case of Item 4 Scan1 I would have a field called SN (Short Text). Item 4 Scan2_contract would be a field called contract(Short Text). Item 1 900339-MO would be a field called MO (Short Text)
Since I have multiple parts that collect SN, Contract, MO, etc. I would need some way to associate the scanned data to the component part number. Is it correct to assume the part number is in the ProductLine table?

Now we have a second table that comes into the picture:
ID_ProductLine,
This is a number field (long integer?) that directly links as a foreign key to a Product line tables primary key.
This is where my struggle starts. Is this one table per product line similar to the Table-60cm_pn_lookup? Or will this be one table that includes all product lines? Would the primary key be the unique part number or should this be an autonumber field with part number as a seperate field name?


comp_Type, string, type of component--it sounds like you want to jam this in a field name
I would think the type of component (or component name) to be included in the Product line lookup table. A Product line consists of multiple part numbers. Each part number has a part name.
Think of a product line as a full computer system with a part number for individual components.
1234=CPU
2345=monitor
4567=keyboard
6789=hard drive,
9876= installed software version,
etc.
PuzzledNH is offline   Reply With Quote
Old 11-18-2019, 09:48 AM   #20
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 464
Thanks: 21
Thanked 92 Times in 91 Posts
mike60smart will become famous soon enough
Re: Table Design & Relationships

Hi

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

Fields.PNG

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

PRS11-930424-SN
PRS11-930424-Contract
PRS11-930424-MFR
PRS11-930424-UID
PRS11-930424-Part
PRS11-930424-3D_Code
mike60smart is offline   Reply With Quote
Old 11-18-2019, 10:24 AM   #21
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,419
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Table Design & Relationships

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:

Quote:
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.
plog is offline   Reply With Quote
Old 11-18-2019, 12:39 PM   #22
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 mike60smart View Post
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.

PuzzledNH is offline   Reply With Quote
Old 11-18-2019, 01:06 PM   #23
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 464
Thanks: 21
Thanked 92 Times in 91 Posts
mike60smart will become famous soon enough
Re: Table Design & Relationships

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.
mike60smart is offline   Reply With Quote
Old 11-18-2019, 01:06 PM   #24
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 plog View Post
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 is offline   Reply With Quote
Old 11-19-2019, 07:08 AM   #25
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 mike60smart View Post
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.
PuzzledNH is offline   Reply With Quote
Old 11-19-2019, 07:18 AM   #26
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 464
Thanks: 21
Thanked 92 Times in 91 Posts
mike60smart will become famous soon enough
Re: Table Design & Relationships

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 is offline   Reply With Quote
Old 11-19-2019, 07:22 AM   #27
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 464
Thanks: 21
Thanked 92 Times in 91 Posts
mike60smart will become famous soon enough
Re: Table Design & Relationships

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.
mike60smart is offline   Reply With Quote
Old 11-20-2019, 10:24 AM   #28
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,303
Thanks: 15
Thanked 1,597 Times in 1,517 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

Quote:
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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-26-2019, 02:15 PM   #29
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 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 is offline   Reply With Quote
Old 11-26-2019, 03:23 PM   #30
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,303
Thanks: 15
Thanked 1,597 Times in 1,517 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

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman 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 12:52 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