can't understand access behaviour with relationship creation

M

merlin777

Registered User.
Local time
Today, 13:46
Joined
Sep 3, 2011
Messages
193
I’m in the early stages of learning access and I’m hoping someone can explain what’s going on here for me.

I created 2 tables, appointments and therapists. The therapists table will provide the name of the therapist to go into the appointment table.
I created a relationship between the 2 tables, from firstname field to firstname field. It wouldn’t accept one from secondname field to secondname field.
When i tried to change the datatype for names in the appointments table to lookup wizard I got an error saying a relationship already existed so I deleted the relationship and tried again successfully.

When I now looked at the relationships, access was showing 3 tables – 2 versions of therapists table and 1 of appointmentstable – with a single relationship from firstname to idfield between appointmentstable and one of the therapists tables and another between secondname in appointmentstable and idfield in the other therapists table.

It’s given me a few questions I hope someone can answer for me:

I thought you had to define relationships yourself. Why can’t you change datatype to lookup wizard without deleting relationships?
Does this mean you can’t define any relationships until you’ve chose all datatypes that might be lookup wizard?
Finally, why did access show 2 versions of the therapists table with only one relationship to each? I’m sure I’ve seen 2 tables with more than one relationship between them?

Very basic questions I know but I have to start somewhere!

Many thanks!
 
Oh - one other question. I tried deleting the relationships that access created and the lookup wizards still work so why are the relationships needed for this?
 
The relationships aren't needed.

Sent from my SM-G925F using Tapatalk
 
Wow - now I'm confused....

If you don't need them, why did access create them for me?

And why do we need relationships at all in that case?:confused:
 
They can be helpful, but as you are finding, they cause confusion whilst you are developing your dB.

Sent from my SM-G925F using Tapatalk
 
Uncle Gizmo said:
The relationships aren't needed.

Sent from my SM-G925F using Tapatalk
Click to expand...
I am very surprised if a little shocked by you comment. I agree that "relationships" aren't mandatory but by the same token, neither is normalisation. The lines in the relationship view define constraints on the tables. If you've done your design properly and understand your primary/foreign key relationships then these relationship lines should be implemented as a matter of course. If you don't implement then you run the risk of developing a database that is out of sync with itself. If you can't implement relationships because you don't understand them then brush up on your database design knowledge. Not implementing these relationships (constraints) is just too bad a habit not to do from the beginning - just like not normalising. Get your table design right before worrying about the front end - which includes constraints.

As a side benefit, Access does a nice job of showing the constraints in diagram fashion. That in itself is immensely useful in checking your design structure.
 
Wow - thanks for that. Must be one of the most useful threads I've seen on the topic.

Much is made of access being a relational database so when you wrote that creating relationships could wait until later or may be unnecessary at all then these things seemed at odds with each other. Then your clarification of what relational actually refers to made things a lot clearer. I have to say that many of the texts you read just don't make it clear and that's a lot harder for a beginner.

A couple of questions:

- if I start the database without creating relationships then what might cause me to add them later?

- why did access create relationships for me when I used the lookupwizard datatype if they were not necessary?
 
merlin777 said:
I’m in the early stages of learning access and I’m hoping someone can explain what’s going on here for me.

I created 2 tables, appointments and therapists. The therapists table will provide the name of the therapist to go into the appointment table.
I created a relationship between the 2 tables, from firstname field to firstname field. It wouldn’t accept one from secondname field to secondname field.
When i tried to change the datatype for names in the appointments table to lookup wizard I got an error saying a relationship already existed so I deleted the relationship and tried again successfully.

When I now looked at the relationships, access was showing 3 tables – 2 versions of therapists table and 1 of appointmentstable – with a single relationship from firstname to idfield between appointmentstable and one of the therapists tables and another between secondname in appointmentstable and idfield in the other therapists table.

It’s given me a few questions I hope someone can answer for me:

I thought you had to define relationships yourself. Why can’t you change datatype to lookup wizard without deleting relationships?
Does this mean you can’t define any relationships until you’ve chose all datatypes that might be lookup wizard?
Finally, why did access show 2 versions of the therapists table with only one relationship to each? I’m sure I’ve seen 2 tables with more than one relationship between them?

Very basic questions I know but I have to start somewhere!

Many thanks!
Click to expand...
Don't use lookup wizards (if I understand correctly what you mean)

Your therapists table should have a unique key. You could have the key made up of two fields i.e. firstname and surname. But there is always the risk here of people changing their name or two people having the same same. It's a bad choice for a key. Instead, add an autonumber column and set this as the primary key.

In your appointments table you need a foreign key column - this is the column that defines the "number" of the therapist.

You can then join the foreign key column in the appointments table with the primary key column in the therapists table.

As far as lookups go, you do this in the form design e.g. using a combo box. Don't try and set it up in the table.
 
stopher said:
I am very surprised if a little shocked by you comment. I agree that "relationships" aren't mandatory but by the same token, neither is normalisation. The lines in the relationship view define constraints on the tables. If you've done your design properly and understand your primary/foreign key relationships then these relationship lines should be implemented as a matter of course. If you don't implement then you run the risk of developing a database that is out of sync with itself. If you can't implement relationships because you don't understand them then brush up on your database design knowledge. Not implementing these relationships (constraints) is just too bad a habit not to do from the beginning - just like not normalising. Get your table design right before worrying about the front end - which includes constraints.

As a side benefit, Access does a nice job of showing the constraints in diagram fashion. That in itself is immensely useful in checking your design structure.
Click to expand...

I appreciate your comments, stopher but now that leaves me slightly confused again. Relationships aren't working the way I thought they did - please could you have a bash at answering my questions in the original post to see if it throws some light for me?
 
merlin777 said:
A couple of questions:

- if I start the database without creating relationships then what might cause me to add them later?
Click to expand...
You could have appointments for therapists that don't exist for example.

merlin777 said:
- why did access create relationships for me when I used the lookupwizard datatype if they were not necessary?
Click to expand...
Because Access is encouraging you to define lookups at tabular level and in order to do that it must know the relationship to the lookup table. But avoid using them. Read here.
 
stopher said:
Don't use lookup wizards (if I understand correctly what you mean)

Your therapists table should have a unique key. You could have the key made up of two fields i.e. firstname and surname. But there is always the risk here of people changing their name or two people having the same same. It's a bad choice for a key. Instead, add an autonumber column and set this as the primary key.

In your appointments table you need a foreign key column - this is the column that defines the "number" of the therapist.

You can then join the foreign key column in the appointments table with the primary key column in the therapists table.

As far as lookups go, you do this in the form design e.g. using a combo box. Don't try and set it up in the table.
Click to expand...

Thanks - I'm going away to grapple a bit with that.

I am using unique ID fields for both tables.

I don't quite understand how I get the therapists name to appear in the name field in the appointments table without using a lookupwizard. Do you put a number in there instead of looking up the name from the therapist table and if so, for practical purposes how do you know which number?

So, in this case where I have two name fields, I should join the 'firstname' column in the appointmentstable to the therapistid column in the other table and the 'secondname' column (field?) also to the therapistid column in the other table?
 
I attach an example. Note that the combo in the form has two columns, one is not visible (the therapistID) and the other is the full name of the therapist.
 

Attachments

merlin777 said:
Thanks - I'm going away to grapple a bit with that.

I am using unique ID fields for both tables.

I don't quite understand how I get the therapists name to appear in the name field in the appointments table without using a lookupwizard. Do you put a number in there instead of looking up the name from the therapist table and if so, for practical purposes how do you know which number?

So, in this case where I have two name fields, I should join the 'firstname' column in the appointmentstable to the therapistid column in the other table and the 'secondname' column (field?) also to the therapistid column in the other table?
Click to expand...
See the example I posted
 
stopher said:
I attach an example. Note that the combo in the form has two columns, one is not visible (the therapistID) and the other is the full name of the therapist.
Click to expand...

You must be psychic. I think you've answered another question I posted separately.

So, in the appointments table I don't use two separate name fields, just one for the whole name?

I'm not quite sure why that now means I can join to the therapistid field and nit their individual first an last name fields?
 
merlin777 said:
So, in the appointments table I don't use two separate name fields, just one for the whole name?
Click to expand...
The table are related by a number not by a name.

merlin777 said:
I'm not quite sure why that now means I can join to the therapistid field and nit their individual first an last name fields?
Click to expand...
I've added a query to the example called qryShowJoinedTables that shows how typically you use records from two tables.
 

Attachments

You must log in or register to reply here.

Similar threads

E
Relationships between tables in access
2
Replies
35
Views
2,323
MajP
MajP
M
Adding attributes to a 1:N or 1:1 relationship. Consecuences?
Replies
16
Views
820
gemma-the-husky
gemma-the-husky
D
Relationships
2 3 4
Replies
69
Views
1,711
LarryE
LarryE
B
Importing from Excel
Replies
7
Views
608
AndT
A
B
Expanding related tables and splitting database
Replies
10
Views
391
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom