table relationship problem

[wideboyrob]

sorry to do this on my first post, but i did search for some answers and found it hard because all of the questions are all related to specific projects on here.

so here is mine

tblVolunteer
volunteer ID (autonum)
surname
forename
DOB
email address
phone number
date of contact
crb check (yes/no field)
level
notes

tblSport
sport ID (text, unique five letters)
sport name

tblPreference
Volunteer ID (autonum)
Sport ID (autonum)

the premise is that the volunteers have preferences in which sports they like to do, and one volunteer can have many sports that they prefer. however, i can create the one-to-many relationship for sport and preference tables, but when i go to create the relationship between the volunteer and preference tables, no only does it automatically become a one-to-one relationship, but i get a warning box "invalid field definition "volunteerID" in defintion of index or relationship"

what does this mean, and why isnt the relationship a one-to many? do i need to use lookup tables?

thanx in advance

rob

 

[Norie]

In tblPreference the foreign keys Volunteer ID and Sport ID should not be Autonumbers, they should be typed as Number.

You should also add, as a primary key in tblPreference, PreferenceID. This would be an Autonumber.

 

[wideboyrob]

still not working!

ok,

so i changed the fields in tblpreferences to number format, and added a pref.ID field which is autonumber format.
then when i try and drag sportID from tblSport to tblPreferences and make it enforce ref. integrity and cascade update, it says
"relationship must be on the same number of fields with the same data types".

Plus the link between volunteer ID is still only one-to-one in that case!

sorry, need more help! thanx for the quick reply though!

rob

 

[Norie]

What number format did you use? It should be Long Integer.

Sorry just noticed that sport ID is not an autonumber.

Either add an Autonumber field or change the foreign key in the preferences table to the same format as the Sport ID field,

 

[wideboyrob]

nearly there!

ok,

that seems to have done it, i now have the relationships. however, the one between tblVolunteer and tblPreference is a one-to-one relationship. by my design, this shouldnt be the case, as one volunteer can have many sports, so there will be duplicate volunteer id's in tblPreferences.

and going back, it looks like it i dont need a primary key in tblPreferences, is that right? i guess its a "compound key" that is formed from the combination of the volunteer id and sport id?

once again, thanx for helping me out norie!

 

[Norie]

Rob

You don't really need a primary key in the Preferences but it might come in useful later and it is definitely should not be a compond key.

Are you sure you are setting up the relationships correctly?

It's a while since I've used Access properly (apart from answering questions on this forum) but I can't recall Access forcing the type of relationship.

Could be wrong though.

 

[Andrew Fergus]

Rob

Go into the design of tblPreference, select the 'Volunteer id' field, change the indexed property from 'Yes (No Duplicates)' to 'Yes (Duplicates OK)'

If this doesn't fix it then post another reply.

Plus I agree with Norie that the unique autonumber id on the preferences table may come in handy later.

HTH, Andrew

 

[wideboyrob]

Phew!!!

thanx guys, a combined effort and its all sorted.

it was me not allowing duplicates on the volunteer ID field!

ok, so now, i need a sub form in my volunteer form that lets me enter the multiple sports that each volunteer might prefer. does this involve lookup tables?

rob