Relationship Issues

[Seb]

Hi guys

I'm kinda an intermediate Access developer.....but am still grasping a few things.
And the one question I do have is what is the use of relationships when you can pretty much do everything anyway with queries, SQL statements, etc

 

[stopher]

Hi guys

I'm kinda an intermediate Access developer.....but am still grasping a few things.
And the one question I do have is what is the use of relationships when you can pretty much do everything anyway with queries, SQL statements, etc

You are right in one respect. You can create queries completely independantly of the relationships. However, the purpose of relationships is not to help you create queries.

Relationships are necessary to enforce referential integrity on you database i.e. keep all your data in different tables consistent. Consider this example. We have SalesPerson to show which sales people work in which region (only one region per person). But we also have a Region table to give more info about the region. More importantly, the Region table is a list of valid regions.

Region:
RegionID (PK)
RegionName
RegionManager

SalesPerson:
SalesPersonID (PK)
RegionAssignedID (FK)

So how do you ensure that the only entries used for RegionAssignedID in SalesPerson are from the list Region ? The answer is you apply a relationship. This ensures a user can't assign a SalesPerson to a fictitious Region.

Furthermore, if someone decides to delete a region then the referential integrity enforced by the relationship won't allow this unless there are no sales people assigned to that region (otherwise they would be left homeless and you wouldn't know it!).

Also, if you choose to do a cascaded delete or update then the relationship is the only way the database knows which records to update/delete. In the above example, if I have selected the Cascade Delete Related records then deleting a region will delete the SalesPersons in that region also. Personally I think this is quite scary so never use Cascade.

hth
Stopher