Basic Relationship Question (1 Viewer)

[Ali Edwards]

This is something in Access I just don't get! Please help me understand.

I have two tables, tblPersonnel (Names,clock numbers etc) and tblVehicle (Vehicle make, model, registration etc)

If I make a select query including all the fields from both tables and type data into say the Surname field only, it creates a record in the tblPersonnel but not in tblVehicle. When I close and reopen the query it shows no records. I want it to add a related table in tblVehicle even if there is no data in it yet.

I have a primary key in tblPersonnel (PersonnelID) which is an autonumber field. I have a secondary key in tblVehicle (VehicleID) which is a number field. I have created a relationship from tblPersonnel.PersonnelID to tblVehicle.Vehicle ID (One to many because a person may have the use of more than one car).

Why is it then that I can't add a record in my query which creates a record in both the tables unless I insert data into a field from tblPersonnel AND a field from tblVehicle in the query? I would like to be able to add the vehicle data after populating the tblPersonnel.

Many thanks for any advice.

Ali

 

[Graham T]

Ali

Your tables should contain the following

tblPersonnel: PersonnelID (Primary Key), FName, SName etc..

tblVehical: VehicalID, Make, Model, PersonnelID (Foreign Key)

tblPersonnel will link to tblVehical via the PersonnelID in a One - to - Many relationship. One Personnel can have the use of Many Vehicals

Your Data Entry Form will contain the One Side (Personnel) and the Many Side (Vehicals)

You can then populate a Person without having to populate the vehical table until a later date.

I hope this helps

Graham

 

[Ali Edwards]

Graham,

Thank you for your help. I have done exactly as you suggest (and double checked!) but as before the data is only passed to the tblPersonnel. If I close the query right after adding records and then reopen it no records are displayed. I built a form on the query but the same thing happens. Also, if I open the tblVehicle I can't add a record because a related one is needed in tblPersonnel (understandable because of the relationship).

In the query datasheet, if I add data into a field from tblPersonnel, the primary key field for tblPersonnel automatically enters a number because it is the primary key field set to autonumber (PersonnelID). Shouldn't the primary key field for tblVehicle also automatically insert a number, thus creating a record in tblVehicle (albeit blank)?

Many thanks,

Ali

 

[Pat Hartman]

Vehicles should be allowed to exist without being assigned to people. Are you also sure that you don't have a situation where the same vehicle can be used by two people? Also, do you need to keep a history of who had which vehicle during a particular period of time?

If you modify your query to change your join type to "LEFT", you will then be able to see personnel even if they do not have a vehicle assigned.

 

[Ali Edwards]

Thank you Pat.

We have an improvement!
I now have my two tables [tbl.Personnel] and [tbl.Vehicle] and they are related exactly as described in Graham's post. I have created a query [qryMaster] which contains all the fields from both tables.
I can now add data to one field from tblPersonnel in this query and it creates a record (blank) in tblVehicle, but if I delete a record in the qryMaster it only deletes the record in tblVehicle and not tblPersonnel.

I have a one-many relationship from tblPersonnel.PersonnelID (primary key) to tblVehicle.PersonnelID (foreign key). The 'enforce referential integrity' and both cascade update/delete related records are checked. in the 'Join type' (I'm not sure what you mean by 'LEFT') I have three options and I have selected option 2.

1. Only include rows where the joined fields from both tables are equal.
2.Include all records from tblPersonnel and only those from tblVehicle where the joined fields are equal.
3.Include all records from tblVehicle and only those from tblPersonnel where the joined fields are equal.

In the qryMaster, the relationship and join between the two tables is the same.

There will not be a situation where the same vehicle could be used by two people and there is not a need for any history.

Do you have any ideas why my record will not delete from the tblPersonnel?

many thanks once again!!

Alistair

 

[Pat Hartman]

Yes. A query that joins the one-side table and the many-side table, can ONLY delete records from the many-side table. If you want to delete rows from the one-side table, create a query that does not include the many-side table. Since you have cascade delete enabled, related rows from the many-side table will be deleted when you delete their parent row from the one-side table even though the many-side table is not included in the query.

 

[The_Doc_Man]

I would be careful here, I think.

I didn't see this mentioned except by Pat, and you might have missed her question. So I'm going to belabor the issue a bit.

You may actually have a worst-case relationship: the many-to-many relation, which Access cannot do in a single step.

Does it ever occur that (a) a single person is allowed the use of more than one vehicle and (b) a single vehicle can be shared by more than one person?

If there is even one case for both (a) and (b), you have the many-to-many case. This is managed in Access by having what is called a 'linking table.'

tblPersonnel
- PersonID (PK)
- other personal data

tblVehicle
- VehicleID (PK)
- other vehicle data

tblPerVeh
- PersonID (foreign key)
- VehicleID (foreign key)

Then you enter the combination of person X and vehicle Y to the tblPerVeh table IF that combination is allowed. If not, then don't make that particular entry.