Advice needed on Table Layout and Relationships (1 Viewer)

Cark

Registered User.
Local time
Today, 09:21
Joined
Dec 13, 2016
Messages
153
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.
 

Attachments

  • TableLayout.accdb
    1,004 KB · Views: 106
  • Capture.PNG
    Capture.PNG
    45 KB · Views: 102

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:21
Joined
Feb 28, 2001
Messages
26,999
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.
 
Last edited:

Cark

Registered User.
Local time
Today, 09:21
Joined
Dec 13, 2016
Messages
153
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:21
Joined
Oct 29, 2018
Messages
21,358
Hi. When you said:
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?
 

Cronk

Registered User.
Local time
Tomorrow, 03:21
Joined
Jul 4, 2013
Messages
2,770
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.
 

Cark

Registered User.
Local time
Today, 09:21
Joined
Dec 13, 2016
Messages
153
@theDBguy -
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 -
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 -
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:21
Joined
Oct 29, 2018
Messages
21,358
@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!
 

Users who are viewing this thread

Top Bottom