Access 2003 Table Relationships

[Acceesbility]

Hello

It's a while since I used MS Access.

I am posting this question under the General tab as I think the table tab is more to do with tables only.
If I'm wrong please move it appropriately.

If I drag the primary key field from one table to anothers Foreign Key, and click "Create" a line is drawn linking the tables.

Can anyone explain to me with respect to a One to Many relationship, what actually creates the relationship for Access 2003 to recognise and display the infinity symbol?

Also once relationships have been established, I thought creating queries would be straight forward but for some reason Multi-table mdb's never worked for me.

Any help to understand and reason what's going on under the bonnet with respect to Relationships, would be much appreciated.

 

[jdraw]

Can you tell us a little about you first table -the one with the Primary Key- and your second table where you identified the ForeignKey? What do these tables represent?

Is your question about PK and FK and relationships?
If so then see if this helps

If your question is specific to the way M$oft creates the graphic, then I have no idea. The graphic is to identify that 1 record in Table 1, is related to 1 or more records in Table 2.
eg.
A Person may have 1 or many Hobbies.
A Teacher teaches 1 or many Courses.
A Customer makes 1 or many Orders.

 

[CJ_London]

a line without an infinity sign is effectively an unmanaged or indeterminate relationship - i.e. you can have 'orphan' records in the child table which do not have a matching record in the parent table.

to answer this
what actually creates the relationship for Access 2003 to recognise and display the infinity symbol?

If you right click on the line and edit the relationship and then tick the enforce referential integrity option then you are setting a rule that you cannot have an orphan record and you will see the 1 and infinity signs when you click OK. If the child table has existing orphan records then you will not be able to complete this action. The other point to bear in mind is the parent field of the relationship has to be indexed, no duplicates - usually it is the primary key.

You can also further manage the relationship by selecting one or both cascade options although the update related fields is unlikely to be used if you have autonumber primary keys (because you can't change them).

The cascade delete option has it's uses - if you have a parent invoice header record with child invoice line records, if this option is ticked, deleting the header record will automatically delete the related line records - otherwise you would need to delete the lines before you delete the header - but then you should not delete data anyway if at all possible.

And to carry that further on, if you also had a product table, also with a one to many relationship with the invoice line record, you would have a conflict if you tried to delete the product record because you would also need to delete the line records because they would be orphans to products - but then the header parent would lose its 'children'. Which is 'legal' but probably not what you would want.

Relationships themselves have nothing to do with joins in queries, they manage the tables. Although they look similar (in the same way as a datasheet view of a table and a datasheet view of a query) they are different 'under the hood'. Easily demonstrated if you manually tried to create a child record without completing the child part of the relationship or manually delete a parent record without first deleting the child records (unless cascade delete is selected)

Whilst editing the relationship, you can click on the join type button to see the properties you can set for a join - but these are not relationships even though you can set them in the relationships window.

Queries will often follow the relationship flow in terms of the join lines, but they don't have to. For example if you wanted to see all invoice header with lines, the join would be a 'normal' join. But this will not return headers without lines - if you want to see them you would need a left join. The fact the relationships appear in the query grid is more an aide memoir rather than a rule to be followed religiously.

I thought creating queries would be straight forward but for some reason Multi-table mdb's never worked for me

This may be due to poor table/relationship structure, hopefully the above will shed some light

 

[Acceesbility]

Thanks for the replies jdraw and CJLondon.

CJ London, I think you covered what I was asking.

Can you point me in the direction of any available download tutorial .mdb's that might demonstrate / explain all the table relationship options in a practical manner that I could work through?

Thanks again.

 

[CJ_London]

Can you point me in the direction of any available download tutorial

Sorry, no, but googling 'table relationship tutorial' produced all these

https://nortonsafe.search.ask.com/w...95-2E5E-4A36-A86A-77F8C83F2436&doi=2016-09-01

 

[Acceesbility]

Ah Google is your friend !
I wondered being and Access site there might be such a resource.

I'll take a look.

Thanks

:banghead:

 

[jdraw]

You might try watching some free video tutorials by Dr.Daniel Soper that will help you with the concepts.

Intro to Database
The Relational Model
Data Modelling and the ER Model

Good luck.

 

[Acceesbility]

jdraw, thanks for the links.

I had missed the other link in your first post.
How to define relationships between tables in an Access database

Thanks for that too.

 

[jdraw]

Good luck with your project.