Form with lookup tables (1 Viewer)

jaryszek

Registered User.
Local time
Today, 03:07
Joined
Aug 25, 2016
Messages
756
Hi Guys,

i have relationships like here in attachment.

I am wondering what is the best approach to connect tblPeopleMachine and tblMachine with lookup tables.

I thought to create Subform for tblMachine with comboboxes for lookup values.
But problem is that i have to connect this with exact Man from tblPeopleMachine.

For example Paul can have Machine (or not) and this will be Machine with 4 Cpu and 128 RAM.

How to create appropriate form in order to achive this?

Best,
Jacek
 

Attachments

  • Screenshot_19.jpg
    Screenshot_19.jpg
    52.8 KB · Views: 71
  • DatabaseExample.accdb
    548 KB · Views: 47

plog

Banishment Pending
Local time
Today, 05:07
Joined
May 11, 2011
Messages
11,613
Looks like you've overnormalized--there's no reason for lookup tables, especially in this situation. Why not just let people input a number for both of those?

Even if you wanted to limit the numbers they can use, you still don't need tables for that. You simply set it up in the table to only allow users to select values from a list and then input the valid numbers there.
 

jaryszek

Registered User.
Local time
Today, 03:07
Joined
Aug 25, 2016
Messages
756
Hi plog,

thank you. This would be a good idea but...
The values from lookup tables i am using also in other tables in my model.
So i have to keep these like now.

Specially i simplified my model to show what i have to do, i do not want to go further into details of my model. Only technical solution.

Best,
Jacek
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:07
Joined
Jul 9, 2003
Messages
16,245
Looks to me like you have the relationships between the machine, the RAM and the CPU round the wrong way.

Sent from my SM-G925F using Tapatalk
 

jaryszek

Registered User.
Local time
Today, 03:07
Joined
Aug 25, 2016
Messages
756
Uncle Gizmo what do you mean?

relationships are fine. Or maybe not? Maybe i should create from Machine PK one to many to RAM and Cpu juntion table?

Best,
Jacek
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:07
Joined
Jul 9, 2003
Messages
16,245
I assume you only have one CPU and one RAM per machine?

Sent from my SM-G925F using Tapatalk
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:07
Joined
Jul 9, 2003
Messages
16,245
Uncle Gizmo what do you mean?

relationships are fine. Or maybe not? Maybe i should create from Machine PK one to many to RAM and Cpu juntion table?

Best,
Jacek
Ah! No, I see! My mistake!

Sent from my SM-G925F using Tapatalk
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:07
Joined
Jul 9, 2003
Messages
16,245
I'm still confused by your question, however looking at your database sample, I can't see any way to connect the subform to the main form. You don't have any relationship between people and machine. In other words you need a field in either one or the other which will store the person or the machine or something like that. I'm thinking you might need an intermediary table to record the match but other than that I don't know.
 

jaryszek

Registered User.
Local time
Today, 03:07
Joined
Aug 25, 2016
Messages
756
You don't have any relationship between people and machine

hmm table tblPeopleMachine is juntion table between People and MAchine and this is many to many ? And here i am storing People and Machine.

Hmm problem is not so easy as i see...

Best,
Jacek
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:07
Joined
Feb 28, 2001
Messages
27,001
best approach to connect tblPeopleMachine and tblMachine with lookup tables.

First, that table tblPeopleMachine IS a junction table except that you don't need the PK on it that you have: PeopleMachineID. It is redundant. Instead, the two fields PeopleID and MachineID can be combined to form a Compound Prime Key (i.e. the PK is two fields).

Your business rules would define whether both a person can share a machine with another person and/or whether a person can have two machines assigned. If NEITHER is allowed (i.e. man/machine assignments are unique), then you can put a unique index on the individual ID fields on that table even though the individual fields are NOT the PK.

Then, don't bother with sub-forms. If you have a list of machines and a list of people, then on the assignment form, I would use a combo box to select a person and/or a combo box to select a machine. Sub-forms would be overkill.

BUT if you have a person-table form OR a machine-table form used for maintenance of the underlying single-entity table, you could even add the combo box on that form to identify the man/machine assignment. Many ways to handle that problem. But try to not overthink it.

The picture that I am examining appears to have the directional relations correct. In particular, one thing to avoid is that even if you have a one person/one machine rule, do not make the links to the junction table one/one because that would affect what you would be able to do with the individual entity tables. One/many relationships include one/zero cases (i.e. a person doesn't have a machine assigned yet or a couple of spare machines are unassigned yet) so the one-many relationships are correct there.

Finally, I need to comment on this statement:

i do not want to go further into details of my model. Only technical solution.

But, you see, mapping the database to match the business model includes the technical nature of the model, not just the technical solution in the database. If you are trying to correctly map your business model then you hope to "make the map the same as the territory" and that means you MUST be open to reconsidering the nature of the mapping. The alternative is that if you run your business off of a mis-modeled database, you face the danger of having the database run the business in a way other than you really wanted. In the USA, we call this "letting the tail wag the dog." It is a bad thing.
 

jaryszek

Registered User.
Local time
Today, 03:07
Joined
Aug 25, 2016
Messages
756
Hi The Doc Man.

thank you.

First, that table tblPeopleMachine IS a junction table except that you don't need the PK on it that you have: PeopleMachineID. It is redundant. Instead, the two fields PeopleID and MachineID can be combined to form a Compound Prime Key (i.e. the PK is two fields).

this is interesting. Everywhere on internet courses you can find that many to many juntion table are created like i created. Are you sure that you have right ?

-->
For logic it seems that you have right.
I created coumpund primary key like in attachment.

Your business rules would define whether both a person can share a machine with another person and/or whether a person can have two machines assigned. If NEITHER is allowed (i.e. man/machine assignments are unique), then you can put a unique index on the individual ID fields on that table even though the individual fields are NOT the PK.

So PKs here are not necessary?

Then, don't bother with sub-forms. If you have a list of machines and a list of people, then on the assignment form, I would use a combo box to select a person and/or a combo box to select a machine. Sub-forms would be overkill.

this is a good idea!

But, you see, mapping the database to match the business model includes the technical nature of the model, not just the technical solution in the database. If you are trying to correctly map your business model then you hope to "make the map the same as the territory" and that means you MUST be open to reconsidering the nature of the mapping. The alternative is that if you run your business off of a mis-modeled database, you face the danger of having the database run the business in a way other than you really wanted. In the USA, we call this "letting the tail wag the dog." It is a bad thing.

Thank you. I agree but sometimes if you are going into business model and details it last weeks and you do not have expected results.
I wrote about my model but this is hard to explain in written way.

Your answer gave me solution - without loosing time for not necessary discussions.

Best,
Jacek
 

Attachments

  • Screenshot_20.png
    Screenshot_20.png
    95.3 KB · Views: 34

jaryszek

Registered User.
Local time
Today, 03:07
Joined
Aug 25, 2016
Messages
756
I did some researches and there are 2 schools: One is with compound key and one in way like i did it.

And there is no difference with performance.

Best,
Jacek
 

Users who are viewing this thread

Top Bottom