Is Learning about relationships in Access necessary? (1 Viewer)

khodor

Member
Local time
Today, 20:55
Joined
Feb 2, 2021
Messages
39
Hello all, I created an Access file to set student information and I made the first table for basic student information and another table for the time forms that the student sends to me daily, and in this table I made the student’s name (number) and it is taken from the first table by lookup wizard, so Access built an automatic relationship, do I need to know more about Access relationships or that is enough?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:55
Joined
Oct 29, 2018
Messages
21,358
Hi. Access may try to help you to get the job done right; but sometimes, it could try to help too much that the result ends up being wrong. So, yes, I would say you should learn about proper relationships, so you can at least make sure what you end up with is correct.
 

isladogs

MVP / VIP
Local time
Today, 18:55
Joined
Jan 14, 2017
Messages
18,186
Hello all, I created an Access file to set student information and I made the first table for basic student information and another table for the time forms that the student sends to me daily, and in this table I made the student’s name (number) and it is taken from the first table by lookup wizard, so Access built an automatic relationship, do I need to know more about Access relationships or that is enough?
I think you are referring to having a lookup field in your table. This will look like a combobox in that table field.
If so, be aware that this is widely considered to be a bad idea. See this article to understand why: Table Lookup Fields - Mendip Data Systems

I would also recommend you understand the purpose of relationships. They are not the same as query joins.
The main reason for setting relationships is so you can impose referential integrity. See Relationships- Mendip Data Systems
 

khodor

Member
Local time
Today, 20:55
Joined
Feb 2, 2021
Messages
39
Hi theDBguy,
Thanks for you help, I will learn about it.

Hi isladogs,
Thanks for your Help.
I have read the two articles completely and the good news is that I was working on Excel and from a short time I moved to work on Access and I did not transfer data yet to Access.
To be honest, I was very comfortable working on Excel, but recently I faced many problems because excel became very slow and closes suddenly, so for these reasons I had To transfer the work to excel
Thank you so much, I have benefited a lot from reading these articles and understood the problem with what I was doing.
 

khodor

Member
Local time
Today, 20:55
Joined
Feb 2, 2021
Messages
39
The reason that made me ask my question is that when I started learning on Access, I downloaded a ready-made file from the Access website about student data, and I did not see any constructed relationships in it, so I thought it was possible to Ignore it.
 

isladogs

MVP / VIP
Local time
Today, 18:55
Joined
Jan 14, 2017
Messages
18,186
The MS templates vary significantly in quality and, unfortunately, several include examples of very poor practice. That is partly because they are often designed mainly to show off Access features including such horrors as lookup fields in tables, multivalued fields and attachment fields. All best avoided.

If you want to look at some more examples of databases for schools, I also have a number of commercial schools apps on my website.
Free evaluation versions are available if you just want some ideas.
Note that in the case of split databases such as those on my site, the relationships will be in the backend file
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:55
Joined
Feb 19, 2002
Messages
42,971
Relationships are always defined in the BE with the data since that is the only place that Referential Integrity can be enforced and RI is the goal of relationships. Sometimes, you might see relationships in the FE between linked tables but they are documentation only. They cannot be enforced.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:55
Joined
Feb 28, 2001
Messages
26,999
The question is, do you really NEED relationships? The answer is that you HAVE them whether you realize it or not. Access relationships just help to formalize matters. But if you have a WHERE clause that suggests that you want records from table A that match records from table B, then you are exploiting something you know that relates the two tables.

Relationships help you in that if you have declared relationships, Access can use the declarations to make the form, query, and control wizards seem "smarter." Do you need them? NO. But once you learn about them and start exploiting them, you will ask yourself how you ever lived without them.
 

isladogs

MVP / VIP
Local time
Today, 18:55
Joined
Jan 14, 2017
Messages
18,186
Nobody needs relationships just for automatically adding query joins etc.
To be clear, as already stated, their primary purpose is to enforce referential integrity
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:55
Joined
Feb 28, 2001
Messages
26,999
My point, Colin, was that it sure was EASIER if you have relationships declared. I did say you didn't need them. But of course, if you want to enforce relational integrity, they are a sine qua non. You are indeed right about their need in that case.
 

Isaac

Lifelong Learner
Local time
Today, 11:55
Joined
Mar 14, 2017
Messages
8,738
I will say that you can probably avoid the Relationships issue for some time and be OK.

I have created quite a number of non-trivial Access databases without paying any attention to the Relationships window whatsoever.
Of course, to handle integrity, I did so on the form level if needed - but I rarely if ever allow Deletes in the first place, much less a Parent delete.

Edit: I should add something. I am aware that, to many new developers (and maybe even to some expert developers), the Relationships feature is a chance, a way, a method whereby they.....mentally digest & even develop their concepts of the normalization of the database. It's where they prove the normalization level to be appropriate and sufficient. And that's totally fine. It's just that sometimes new developers get confused and seem to think that Relationships tool is as necessary as normalizing your data structure is. Because some people use them as a way of visualizing and proving their good data structure it gets confused as being part of that - but really it could have been a word document or a napkin, as long as it's done right. I say that, of course, without mention of referential integrity purposes--because I already commented on that, above.
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 18:55
Joined
Jul 9, 2003
Messages
16,244
There was a discussion on Relationships a few years back and deportas made some excellent observations. It is well worth revisiting this thread...


 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:55
Joined
Feb 19, 2002
Messages
42,971
I will say that you can probably avoid the Relationships issue for some time and be OK.
I wouldn't encourage people to do something you know to be poor practice. It is always easier to to it right than it is to do it twice.

And I strongly disagree with Uncle's assessment that Relationships are not necessary. They are even more necessary for newbe's than for experienced people. At least experienced people know enough to enforce rules in code. But, the problem with enforcing rules in code that the db engine can enforce for you is that developers who come after you might not realize what you have done. So even if you are totally conscientious about doing it "right", and never forget to enforce rules, your rules won't ever be automatically enforced in new forms or queries built by others. Also, doing it in code always means that you may have to do the same thing in multiple places and we know how that works.
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 11:55
Joined
Mar 14, 2017
Messages
8,738
I wouldn't encourage people to do something you know to be poor practice. It is always easier to to it right than it is to do it twice.

And I strongly disagree with Uncle's assessment that Relationships are not necessary. They are even more necessary for newbe's than for experienced people. At least experienced people know enough to enforce rules in code. But, the problem with enforcing rules in code that the db engine can enforce for you is that developers who come after you might not realize what you have done. So even if you are totally conscientious about doing it "right", and never forget to enforce rules, your rules won't ever be automatically enforced in new forms or queries built by others. Also, doing it in code always means that you may have to do the same thing in multiple places and we know how that works.
I don't know it to be poor practice, and apparently neither do quite a few other people. I don't mind you disagreeing, but please don't tell me what to or not say in my own posts. I see more confusion from people trying to use a window that they get no benefit from than I do see people benefitting from referential integrity, and I don't allow people to delete parent records in the first place. Note that I'm not going to say "neither should you", I am only going to speak for myself.
 

isladogs

MVP / VIP
Local time
Today, 18:55
Joined
Jan 14, 2017
Messages
18,186
I agree with @Pat Hartman on this.
I could create databases with table level lookup fields, MVFs & attachment fields and for some time I wouldn't have problems.
However once those problems became an issue it would take a lot of effort to fix. Much longer than avoiding the issues in the first place

Similarly with relationships. If these are implemented from the start it will save a lot of time & effort in the long run
Working around this as described above is illogical and will take longer to implement and a lot more code
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:55
Joined
Feb 28, 2001
Messages
26,999
Pat, I use relationships constructively and want them to be present. So I agree they are "good practice" actions. BUT for newbies who don't yet fully understand normalization, they are a deep, dark hole that frightens them - and Access works reasonably well without explicit relationships for those folks who have relatively simple situations. Granted, the more complex the problem, the more likely it will be that you really DO need relationships., and thus I definitely favor having them. But until you learn WHY you want to normalize your DB, you won't grasp relationships completely. I wouldn't bear down too hard on Isaac because I remember a time (dimly) when I didn't bother with relationships that much either. OR I created spot relationships in the upper portion of the query design grid. Not system-wide, but localized relationships. And Access is powerful enough to work on that basis until you DO eventually learn better.

Not all of us have databases that require Boyce-Codd normalization or 5th-normal form normalization. Not all of us have to manage 15-way JOINs (though the Navy DID have that with their personnel system).
 

isladogs

MVP / VIP
Local time
Today, 18:55
Joined
Jan 14, 2017
Messages
18,186
I'm sorry but I have to disagree...again
1. You cannot create relationships in the query design grid. You create joins.
There is no such thing as a 'spot' or 'localised' relationship, A join is not the same as a relationship
2. When I first started working with databases, I also knew nothing about normalisation, relationships or referential integrity.
After a while, when issues occurred I had to backtrack and undo a lot of the database structure to fix issues that had become intractable as the database became more complex.

It is far better to build a sound foundation at the start than have to go back and deal with the structure later.
That doesn't mean having to understand all the levels of normalisation as a theoretical construct.
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 05:55
Joined
Jan 20, 2009
Messages
12,849
And I strongly disagree with Uncle's assessment that Relationships are not necessary.
One hundred percent agree. Like unique indexes they are tools that ensure the integrity of the data,

While forms can have all kinds of facilities to prevent bad data being accepted, best practice is to always protect the database from storing incoherent data right down at the table level. Data can be inserted and updated directly to the table by people trying to restore things or make corrections. However competent they may be, anyone can make a mistake.
So I agree they are "good practice" actions. BUT for newbies who don't yet fully understand normalization, they are a deep, dark hole that frightens them - and Access works reasonably well without explicit relationships for those folks who have relatively simple situations.
All the more reason to understand them. Fear is the primary enemy of all learning. The more something frightens you the more it should be tamed through gaining knowledge. Ignore stuff like that at one's peril.

2. When I first started working with databases, I also knew nothing about normalisation, relationships or referential integrity.
After a while, when issues occurred I had to backtrack and undo a lot of the database structure to fix issues that had become intractable as the database became more complex.

It is far better to build a sound foundation at the start than have to go back and deal with the structure later.
That doesn't mean having to understand all the levels of normalisation as a theoretical construct.
Exactly. (My emphasis).
 

Isaac

Lifelong Learner
Local time
Today, 11:55
Joined
Mar 14, 2017
Messages
8,738
It could be that I've put the Relationships to limited use because I don't allow records to be deleted almost ever, I'm willing to acknowledge that it might be different if you do. My main point was that a) it's been 15+ years and I'm still OK without them, and b) they seem to serve more harm than good when people are new. But you're right - of course - with enough learning, everything beneficial is beneficial...if it actually is.
 

Users who are viewing this thread

Top Bottom