Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-12-2019, 03:05 AM   #1
Cark
Newly Registered User
 
Join Date: Dec 2016
Posts: 95
Thanks: 47
Thanked 1 Time in 1 Post
Cark is on a distinguished road
Advice needed on Table Layout and Relationships

I have a bit of a peculiar database and form that I am trying to create to store lots of different bits of information in one nice and holistic database. Each Tab has different bits of information that are submitted from around the business that all contribute to an analysis of a Part Number. In the future, I will end up wanting to be able to search for a Part Number and then be able to find out what Reliability, Engineering, Finance, Design, Sales and Aftercare all have to say with regards to Part "XXXXX".

I know I will need to collect the data fields I have added on my Form in a Table, but I don't have a clue how I would go about arranging my Tables and the Relationships to get this to work.

None of the data from the tabs will ever overlap and the only connection will be to the Part Number. Therefore will I need a table for the Part Numbers and then an individual table for each of "Reliability, Engineering, Finance, Design, Sales, Aftercare"?

The attached database has a form called OverViewForm where I am trying to convey how I hope the Form will look. The Text Boxes are not linked to tables yet. The Tables in the database are just my vague attempts at getting this to work and I don't think they will be much use in helping to assist me, therefore I would stick with looking at OverViewForm for an idea of what I am trying to achieve.

Let me know if any further details / diagrams / images are needed to get across what I am trying to achieve.
Attached Images
File Type: png Capture.PNG (45.0 KB, 22 views)
Attached Files
File Type: accdb TableLayout.accdb (1,004.0 KB, 20 views)

Cark is offline   Reply With Quote
Old 06-12-2019, 04:58 AM   #2
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,954
Thanks: 80
Thanked 1,568 Times in 1,456 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: Advice needed on Table Layout and Relationships

Quote:
Therefore will I need a table for the Part Numbers and then an individual table for each of "Reliability, Engineering, Finance, Design, Sales, Aftercare"?
That would certainly be a valid approach. IF you take that approach, I would make the info on each of your tabs come out through a sub-form, perhaps except for a tab that lets you put in / maintain anything that was purely related to the part itself independent of what everyone else thinks. That way, your data gathering is on the one form (as you suggest that you wanted). The "Parts" data on that tab is part of the main form so needs no extra binding. The Feedback data on the other tabs would be one child form per department with child tables driving the sub-forms using the part unique ID as your linking field to keep the sub-forms synchronized. (And of course a formal relationship based on part ID field.)

The only question I might have is whether you can ever have more than one entry per department for a single part. I.e. can the Reliability group enter TWO failure incidents separately on the same part? That might affect the design of the sub-forms. You also would worry about the relationships between the part table and the department tables, but that should always be one/many. Because a 1:1 relationship has a nasty side effect if someone doesn't have an opinion. But a 1:many will include "none" where nothing has been posted yet, and will not break anything if you are careful to test for nulls.

Other than that, I don't see any major error in the way you are thinking at the moment. One man's opinion, of course, but "YES" to the quoted question.
__________________
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.

Last edited by The_Doc_Man; 06-12-2019 at 05:04 AM.
The_Doc_Man is online now   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Cark (06-12-2019)
Old 06-12-2019, 07:10 AM   #3
Cark
Newly Registered User
 
Join Date: Dec 2016
Posts: 95
Thanks: 47
Thanked 1 Time in 1 Post
Cark is on a distinguished road
Re: Advice needed on Table Layout and Relationships

I read your message almost immediately after you posted the replay, but wanted to just check in Access before replying. Everything seems to be coming together nicely with an individual table for each department (tab) and working as intended.

Thanks for the advice/recommendation/confirmation, I have used subforms in the each of the tab pages and done it in such a way that Reliability can fill in 2 records of data for a particular part number.

Cark is offline   Reply With Quote
Old 06-12-2019, 07:17 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,088
Thanks: 36
Thanked 735 Times in 718 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Advice needed on Table Layout and Relationships

Hi. When you said:
Quote:
Each Tab has different bits of information that are submitted from around the business that all contribute to an analysis of a Part Number.
Is that like saying the first tab might say Color = Blue while the second tab might say Height = Tall? Or, was it more like first tab say Color = Blue while the second tab might say Color = Red?
__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
Old 06-12-2019, 10:14 AM   #5
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,119
Thanks: 3
Thanked 459 Times in 452 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Advice needed on Table Layout and Relationships

In looking at your database, I can see 3 separate entities - Parts, ProgramCategories, and Incidents. (I'm assuming because of the field, Incident_ID, that there can be multiple incidents for any one Part.)


I don't see where Programme fits into your scheme. Is this another name for Part, or an additional entity.


As regards to putting Part fields on different tabs, there is nothing wrong with that (although I personally prefer not to use tabs unless there is not enough screen space to show everything). You don't need to have sub forms for these fields although you will need a sub form to display multiple instances, such as Incidents.
Cronk is offline   Reply With Quote
Old 06-13-2019, 01:28 AM   #6
Cark
Newly Registered User
 
Join Date: Dec 2016
Posts: 95
Thanks: 47
Thanked 1 Time in 1 Post
Cark is on a distinguished road
Re: Advice needed on Table Layout and Relationships

@theDBguy -
Quote:
Is that like saying the first tab might say Color = Blue while the second tab might say Height = Tall? Or, was it more like first tab say Color = Blue while the second tab might say Color = Red?
- Yes like the first configuration you mentioned. One tab will take down details like Color = Blue and then the other tab will have data that doesn't relate to Color in the slightest e.g Height = Tall.

@Cronk -
Quote:
In looking at your database, I can see 3 separate entities - Parts, ProgramCategories, and Incidents. (I'm assuming because of the field, Incident_ID, that there can be multiple incidents for any one Part.)
- Yes there can be multiple incidents per Part so subforms looks like it is the way forward. I've been able to get that working so far.

@Cronk -
Quote:
I don't see where Programme fits into your scheme. Is this another name for Part, or an additional entity.
- I was flipping between Part and Programme. Since getting some of early advice in this thread, I have tweaked it and now I am just using the word Part. I should have changed it to Part before I uploaded it... my bad.

It's all going well so far thanks to you peeps.
Cark is offline   Reply With Quote
Old 06-13-2019, 07:18 AM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,088
Thanks: 36
Thanked 735 Times in 718 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Advice needed on Table Layout and Relationships

Quote:
Originally Posted by Cark View Post
@theDBguy - - Yes like the first configuration you mentioned. One tab will take down details like Color = Blue and then the other tab will have data that doesn't relate to Color in the slightest e.g Height = Tall.
Okay, there is an ongoing discussion in another thread on a similar topic. Try doing a search on "Entity Attribute Value Model." Good luck!


__________________
Just my 2 cents...

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.
theDBguy 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
Payroll database- advice needed on relationships/normalisation RCurtin Tables 14 06-07-2018 08:41 PM
Advice on Contacts and Telephone Table and Relationships??? hullstorage Tables 1 10-10-2011 06:47 PM
advice needed for table layout/design for a newbie jas118 Tables 8 06-05-2010 02:49 PM
Creating new layout for tables and need advice... SimonSezz Tables 2 02-05-2009 09:56 PM
Is a new table needed? Advice needed sueviolet Tables 4 09-11-2002 10:54 AM




All times are GMT -8. The time now is 07:28 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