Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-08-2019, 05:56 AM   #1
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 13
Thanks: 2
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Table Design & Relationships

I was provided a database (with inadequate limited data already dumped in by a predecessor) and told to make it work. The end goal is to include all product lines and their component details at the time of shipment to a customer. Currently, the database includes one table with 1200 records. Each record contains multiple fields of data and appears to have originated from an Excel spreadsheet.

In most cases, each SHIPMENT would include multiple records. Each record would capture the details of each component shipped in a single system (product line). The product line includes multiple component part numbers that stay the same. Each component part needs to capture the following variable data: serial number, revision, software info, notes, etc. The customer and tracking info is duplicated for each line.

I have the following chunks of data I need to work with:
A customer may have multiple Purchase orders.
A purchase order may have multiple delivery addresses.
A purchase order may have one or multiple product lines.
A shipment may have one or more systems containing multiple serialized components, -and/or- individual components going to one address.
Each product line will have multiple serialized components and other info (revision, software version, etc.) to track.
Each component will have an independent serial number (and/or other info to track).
Each shipment will have a top level system serial number (and/or other info to track).
A product line (or component) can be shipped to multiple customers (or ordered from multiple purchase orders.

My initial thoughts were to have each product line as a separate table since the components stay the same and only a few fields for each component (serial number, revision, software version, notes, etc.) might vary from shipment to shipment. If I know how to format one product line (such as the 60cm dish in the provided sample) I am sure adding another product line will be fairly straight forward.
Other tables I was considering include one to capture the tracking info, ship date, contract shipped under, and destination. A Customer table is probably warranted (address, phone, contract/PO number, notes, etc.). Right now I am just trying to keep data capture going (record updates) as I figure all this out.

A copy of the database in question is attached.

This is my second database project. (The previous project was a few years back when in college. Thus this is a real struggle to pull this together. Any suggestions on table layout are very welcome.

Thank you in advance for your help.
Attached Files
File Type: zip Shipment Warranty-v.4.2.zip (272.4 KB, 6 views)

PuzzledNH is offline   Reply With Quote
Old 11-08-2019, 09:29 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,367
Thanks: 0
Thanked 742 Times in 727 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Table Design & Relationships

you can breakup the 1 table into its relative components.
run some make table queries from the source table to create the new tables and atomic values:

POs,
Addresses, shipto, mailto
prod line,
etc.
Ranman256 is offline   Reply With Quote
Old 11-08-2019, 12:21 PM   #3
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 13
Thanks: 2
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Table Design & Relationships

Quote:
Originally Posted by Ranman256 View Post
you can breakup the 1 table into its relative components. run some make table queries from the source table to create the new tables and atomic values:
Hi Ranman256,
You completely lost me. What are make table queries and atomic values?

PuzzledNH is offline   Reply With Quote
Old 11-08-2019, 12:37 PM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,888
Thanks: 114
Thanked 2,979 Times in 2,710 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Table Design & Relationships

In case he doesn't respond, I think Rahman was suggesting making several new tables to hold each 'section' of the underlying data.

Here's another suggestion with the same aim.
Use the Analyse Table feature in Database Tools. It will recommend ways of structuring your data better to remove repetition. If you follow the advice, the end result should be 'normalised' data which is how it should be structured. Same idea but Access will do the work for you. If that doesn't mean anything to you, I suggest you do an online search and read about it.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 11-08-2019, 08:38 PM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,238
Thanks: 15
Thanked 1,591 Times in 1,511 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

I can't make any sense out of table1. Looks like everything to the right of email has a data value as the column name and the values are all empty so I have no idea what they are for.

You started out with a description of the types of data you have. Now try to map the columns to each of the entities you defined.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old Today, 05:32 AM   #6
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 13
Thanks: 2
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Table Design & Relationships

Quote:
Originally Posted by Pat Hartman View Post
I can't make any sense out of table1. Looks like everything to the right of email has a data value as the column name and the values are all empty so I have no idea what they are for.

You started out with a description of the types of data you have. Now try to map the columns to each of the entities you defined.
Field labels to the right of Customer Email are part number designations for individual components.

I.e.: 60cm-140087-MO
is looking for the manufacturing order (-MO) we used to build a 6 pin configured connector (140087) for the 60cm product line (60cm).

The 60cm- would be a specific product line. The database I provided was only configured (or set up) to capture one product line (and a few components). My predecessor had just started data capture and had not used these fields yet. The database will have multiple product lines. Each product line will have it's own starting designation, set of component part numbers, and similar data to capture.

The 6 digit number 140087 is the component part number.

Characters after the (-) dash indicate the data type being collected for this part number.
-MO is a manufacturing Order
-Rev is the part revision or software version
-Contract is is to capture contract data from a bar code scan.
-3D code is to capture data from a bar code scan.
-SN is the component serial number.

Those items with a yes/no data type would be checked off to indicate the item was shipped with the system.

Thus a full 60cm system may (or may not) require a data capture for each 60cm part number if the component is shipped as part of the system.

Hopefully this makes sense.

I am finding that as I answer these questions I am readjusting how I think the data structure should be. So thank you for all the prodding.

I have most of the data fields for a second product line defined and entered in Table1. (No data, just field names and data types.) I figure that once I can normalize the data, the effort would just move into a different table. (or so I hope.) I will try to map the entity types to the columns and post in the next day or so. Thank you.


Last edited by PuzzledNH; Today at 05:52 AM.
PuzzledNH 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 11:53 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