Many to Many relationship issues.

[tonycl69]

I have a vehicle database. I have 3 tables set up which are populated from a previous database I inherited., in one table I have vehicles PK unitid, in the other I have drivers PK driverid, I have the third which is the joining table which is also populated, which has the Unit/drivers in, the joining table has the hireID as PK and also the driverid and unitid so all three have the PK key next to them. The hire table has multiples of both driverid and unitid because a vehicle have more than one driver at different times and a driver can have a vehicle at different times. I have joined all 3 together correctly, then have created a qry bringing all 3 to the query and opulling out vehicle, drivername, startdate, enddate, hireID, driverid and unitid.then create a form based on the query which I can choose to have either the vehicle as the main form or the drivername etc, the information displayed is fine and works great. However, my issue is, when I add a new driver in the subform to the vehicle form I get a new driverid for an existing driver, I dont want this, how can I stop this from happening. Do I need to rethink my approach, I have also tried it with blank tables and the same thing happens I get duplicate names which is what I want but I get a new driverid everytime which I dont want. Because when I load the other form for the driver it doesnt show me the vehicles that driverid has been driving, hope have made sense.

 

[jzwp22]

the joining table has the hireID as PK and also the driverid and unitid so all three have the PK key next to them

There can be only 1 PK per table unless you have created an index involving all three fields and are using that combination as the primary key.

Out of curiosity, are the driverID and unitID in the junction table set up as lookup fields?

 

[tonycl69]

I created the join table and created the 2 fileds driverid and gave it a number type and the unitid and gave that a number field then joined them to the unit table and the driver table, what do you mean by look up fields?

 

[mdlueck]

If a M-M relationship is needed, I have found it helpful to implement a "Link" table between the two M tables. So in your case:

Vehicle <---> VDLink <---> Driver
=======       ======       ======
id            id           id
              vid
              did

Place a primary index on all id fields. id fields should be AutoNumber fields.
Place a unique index on vid/did to make sure multiple links do not get created between the same records.
If you need to track link specific details, then add those fields to the Link table.

 

[tonycl69]

i have already created a link table and i have the id for the link table and have got the other 2 id fields in there too. when you say primary index, what do you mean, do you mean no duplicates. The id of the link table is autonumber, the other 2 are just number tpyes, and you mention a unique index do you mean yes(no duplicates)?

 

[tonycl69]

I must be missing something because when I create a form with the vehicle table as main and hire table as sub, when I add a driver from the hire table i am given a new driver id not the driver id in the driver table.

 

[jzwp22]

what do you mean by look up fields?

A lookup field is set up in the table using the lookup tab in the lower pane of the table design view. Having lookups at the table level is generally not recommended. This site provides details

then have created a qry bringing all 3 to the query and opulling out vehicle, drivername, startdate, enddate, hireID, driverid and unitid.then create a form based on the query which I can choose to have either the vehicle as the main form or the drivername etc, the information displayed is fine and works great

Using the query and creating a single form will work fine for displaying but it won't work for data entry. Typically, you would base the main form on one of the two tables (vehicle or driver) depending on how you want to add new data. You would use a subform on the main form. The subform must be based on the junction table. In that subform, you would use a combo box based on the table that was not used for the main form. So in other words, if you base the main form on the driver table then the combo box in the subform would be based on the vehicle table. Or if you base the main form on the vehicle table, the combo box would be based on the driver table.

 

[tonycl69]

No i'm not using lookup fields. Ok I will give that a try thanks I will let you know the outcome.
In the combo box in the subform do I need to store the result or just use it?
What is the combo boxes source, the drivername or the whole driver table.
I have created a combo box in the subform based on the other table, so I have a main form created from the vehicle table then used the subform wizard to create a subform and then used the combo wizard to create a combo using the driver table and drivername as its source. how am doing so far.
But that is working

 

[jzwp22]

In the combo box in the subform do I need to store the result or just use it?

You need to use it to bind it to the applicable field in the junction table. For example, if you combo box is based on the vehicle table, then the combo box must be bound to the unitID field of the hire table.

 

[mdlueck]

i have already created a link table and i have the id for the link table and have got the other 2 id fields in there too.

Sounds good so far.

when you say primary index, what do you mean, do you mean no duplicates.

Correct.

The id of the link table is autonumber, the other 2 are just number tpyes, and you mention a unique index do you mean yes(no duplicates)?

Correct.

Differences in terminology between RDBMS's.

 

[mdlueck]

I must be missing something because when I create a form with the vehicle table as main and hire table as sub, when I add a driver from the hire table i am given a new driver id not the driver id in the driver table.

Access is a bit odd in how it handles binding forms to complex table schemas. It gets its thinking scrambled by such.

I have chosen to bind my forms to FE temp tables, issuing complex SQL, downloading records into one FE temp table per bound form. Here is a post where I describe my design:

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

It would work with Access FE/DB databases as well, just that Access has no true remotely executed Stored Procedures and what not.

 

[tonycl69]

You need to use it to bind it to the applicable field in the junction table. For example, if you combo box is based on the vehicle table, then the combo box must be bound to the unitID field of the hire table.

I know this is a daft question but how does one do that, do I go to properties and then where or is at the wizard level i do that.

 

[jzwp22]

You can do it either way. The combo wizard will step you through the process--I do it this way. I've attached a screen shot of the specific wizard step where you assign the value of the combo box to the field (its the second option & you need to specify the corresponding field)

 

[tonycl69]

Thanks for that I think I have cracked it I missunderstood the binding phrase its the crontrol source of the combo that sould be the id of the subform, I get it lol so cheers

 

[tonycl69]

How do I finsih this thread as solved

 

[jzwp22]

I looked in the FAQ, but could not find anything. I would guess that it would be in the Thread Tool menu (located just above the first post on the page)