Table relationships for Combobox

[Lukael]

Hello everyone,

I have one problem that Is killing me, and I'm an Access beginner.

Problem description:

I have Main table with fields also for Maintainance_Man, Technic_Man and User.

Then I have second table with fields _Name,_Surname.

Those three fields in first table should have comboboxes with drop-down list of all names of people from second table, to select name and add value in field.

So basically I'm just pulling data from second table to fill It in first table, in order to create a record.


Question:

1. Is the proper relationship between table many-to-many or one-to-many ?

2. Or Can I just add combobox to form of Main table, link It to records (Name,Surname) in second table and with Change_Event add values to
fields Maintainance_Man, Technic_Man and User ??

P.S: Solution has to be done this way - If I delete fields in Maintainance_Man, Technic_Man and User, the Name and Surname fields in second table should be left intact.



I hope I make any sense

thanks for help !

[June7]

Depends. What is main table for? Is it something like WorkOrders? Each record can have only 1 Maint, 1 Tech, 1 User but each Maint/Tech/User can be associated with multiple work orders?

You will need 3 comboboxes on the form to select a value for each field.

However, if you want to normalize data structure, you will need another table called WorkOrderDetails:

DetailID
Table1_FK
Category (Maint, Tech, User)
Table2_FK

This would be a many-to-many relationship. This allows any number of Maint/Tech/User to be associated with a work order. And will need form/subform arrangement for data entry/edit.

Is Man shorthand for something?

[orange]

Work through this tutorial to learn about table design. What you learn can be used with any database.
You have to do the work, and there is a solution included.
This is also important.

Note:

I just read June's response, and I think I misunderstood your design. I was thinking you had some sort of lookup in the table. If you are getting names from a separate table, you will need 3 copies of that table. But you said table and she is saying Form---I'm a little confused. (but that's nothing new..)

[Lukael]

Thanks for response June7.

This table is for informations about machinery in factory, fields are :

Machine_id(autonumber)
Machine_Name
Machine_purpose
Maintainance_Man
Technic_Man
User

For those 3 I just need comboboxes with list of all names to put them in fields. And NO, there is no workOrders - each machine can have only one Maintainance man, technic and User.

[Lukael]

Orange,

thanks for these tutorial. I will definitely take a look !!!

[June7]

Okay, change WorkOrders to MachineInfo. Same principles apply.

I NEVER build lookups in table and because you said combobox I immediately thought about form setup. Best to work with forms and not directly with tables/queries.

[Lukael]

So if I understand you, this cannot be done with one-to-many relationship If I want Comboboxes ? Subform is not desired view...

[June7]

If you want to keep track of maintenance history for each machine and who did what then definitely need a 3rd table as this is a many-to-many relationship.

tblMachineMaintenance
MachineID_FK
MaintenanceDate
Maint_Table2_FK
Tech_Table2_FK
User_Table2_FK
Comments

This is still not fully normalized but might serve your purposes.

[Lukael]

Maintainance/Tech/User is in 1 Table, you wrote Table2 (which contains names and surnames only). Mistake or did you mean to construct 4th table with only these fields ?

[June7]

Not a mistake. Maintenance/Tech/User would no longer be in Table1. They would be in the new 3rd table (a junction table) that associates machine with people for each maintenance event.

If you want to fully normalize data structure, there would be a 4th table.

You should probably complete the tutorials then come back when you have a better understanding of relational database principles.

You really need to give us a better idea of what this db is for. I am still guessing it is for maintenance history. This type db has been a topic in numerous threads.

[Lukael]

Ok, I will do as you said and study a little bit more. Thanks for help !