error when creating relationships

[merlin777]

newbie here...
I'm getting the error 'relationship must be on the same number of fields with the same data types' and i'm not sure what to do.

i'm trying to add a relationship between an appointments table containing a therapist field to the id field of a therapists table (which is the primary key).

in relationship view i'm dragging the therapist field and dropping it onto the id field (pk) in the therapists table and i get this error.

it worked earlier when i had mistakenly made the therapist field a number field but now i've changed it back to a text field (had to remove the relationship first) i get this error.
help!

btw, i renamed the therapist field too - it was origially 'idTherapist'. I had misunderstood what was needed and thought it should have been an id number. Told you i was new...

 

[JHB]

Are both field of same type?
Text to text and number to number!
If you can't get it post you database with some sample data, (zip it) + the name of the tables and the fields.

 

[merlin777]

the appointments table field called 'therapist' is text and is not a primary key
the therapist table's id field is the primay key, the default field generated by access and a number format.

I think I must be missing something basic. I thought you could link the primary key id field with another field in another table to create a relationship? how can you do that if the field is not a number because the id field is always a number?

 

[CazB]

The fields must be of the same type - both numbers, or both text - or you can't link them.

What most people would do is add an ID field to the therapist table and then link the TherapistID (PK) from the therapist table to another TherapistID (FK) field in the appointments table - in other words, link them on the ID number, not the name.

 

[merlin777]

both tables already have the default id field - can i link those? It sounds as though you are suggesting a therapist id auto number field in both tables but if i made them both primary keys then i couldn't have another primary key for the appointments table.

If it helps I'll list the tables here:

tblAppointments
ID - primary key, autonumber
day
appointmentstart
appointmentend
therapytype
therapist
patient
notes
location

tblTherapist
id - primary key, autonumber
company
lastname
firstname
jobtitle
email
notes
photo

tblPatients
id - primary key, autonumber
firstname
lastname
(and some personal detail fields)

Those are the tables I have at the moment. I'm intending to add a therapy type table too.

 

[Galaxiom]

tblAppointments.therapist = tblTherapist.id
tblAppointments.patient = tblPatients.id

The field types need to be the same on each join.

 

[merlin777]

tblAppointments.therapist = tblTherapist.id
tblAppointments.patient = tblPatients.id

The field types need to be the same on each join.

let's see if understand that correctly:

In my tblAppointments I use the therapist field as the therapist id and change the data type to number data type.
In my tblAppointments I use the patient field as the patient id and change that to number data type

Then do I use therapist field in tblAppointments as foreign key to make a relationship with the id field in tbblTherapist which is set to primary key (and similar with the patient field)?

If I have that correct so far, how do I deal with the error I get when I try to make the therapist field an autnumber field? Access won't allow me two in one table.

 

[Galaxiom]

If I have that correct so far, how do I deal with the error I get when I try to make the therapist field an autnumber field? Access won't allow me two in one table.

All correct before this point.

Don't make the FKs autonmumber in Appointments. They should be Long Integer.

 

[merlin777]

ahhhhh... I think the mists are clearing. Thank you so much!