Running Totals (1 Viewer)

[davegoodo]

Just a small side note, when I opened up the Relationships window, the tblRaceTransactions was not connected to the other 3 tables.

 

[MajP]

Go ahead and fix that.

 

[davegoodo]

Go ahead and fix that.

Yeah thanks, I just might do that.

My only wish is that I had some VBA issue to access your VBA Savant ability.

You've helped me with the Running Total problem, that's why I came to the forum so, that's a win.

I think I'll bow out with what I've gained,

Thanks for your help.

 

[MajP]

Yeah thanks, I just might do that

It is not really optional. A good database has proper relationships established to ensure referential integrity and data integrity. Some people think it has to do with joining tables, it does not but that is a secondary effect in Access.

This is a very good article by @isladogs that any good Access developer should fully understand

Relationships1

This article discusses the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity. Three methods of adding relationships are discussed. The article also explains how Access uses the MSysRelationships system...

www.isladogs.co.uk

In this case once you establish referential integrity it will ensure that no Transaction can be made without a related horse race. This would be an orphan transaction. You cannot delete a race unless you delete the related transactions.

 

[davegoodo]

It is not really optional. A good database has proper relationships established to ensure referential integrity and data integrity. Some people think it has to do with joining tables, it does not but that is a secondary effect in Access.

This is a very good article by @isladogs that any good Access developer should fully understand

Relationships1

This article discusses the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity. Three methods of adding relationships are discussed. The article also explains how Access uses the MSysRelationships system...

www.isladogs.co.uk

In this case once you establish referential integrity it will ensure that no Transaction can be made without a related horse race. This would be an orphan transaction. You cannot delete a race unless you delete the related transactions.

Thanks MajP, I'll read it. I know I need to set up relationships but I haven't always done that. My experience with this forum has taught me that attention to this kind of detail is important for the long run viability of the database. I'll study this article.
Much appreciated

 

[Pat Hartman]

Relationships are defined in the relationships window of the database that houses the physical tables. So, in a split database (which is best practice), the relationships can only be defined in the BE since that is where the tables reside. If you go to the relationships window in the FE, you will not see the actual relationships which doesn't mean that they aren't there and being enforced. If you join tables in the relationship window in the FE, you can create a visual of the linkages but you are not creating a relationship and you cannot enforce Referential Integrity.

Joins are used in queries to specify how to match the records in one table to those of another. Although most of your joins will mirror the relationships, you will have instances where they don't. This is most likely to happen with imported or temporary data which haven't been defined to have relationships. The rule for a join is only that the columns being joined must match on data type. The join doesn't need to make logical sense. For example, if you import a spreadsheet and the user was sloppy doing the data entry and sometimes put the first name in the last name field and the last name in the first name field and other times did the entry correctly, you have a mess to clean up. One way is to create a query that joins the first name column of the imported data to the last name column of your customer table. In normal circumstances, this would make no sense but if you are looking for bad data, this could help you to find it.

RI is used to enforce rules on defined relationships. When you check the RI box on the join between tblCustomer and tblOrder and select the CustomerID from both tables, what you are telling the database engine is that it is not valid to have a CustomerID in tblOrder that does not already exist in tblCustomer. Also, if you try to delete a row from tblCustomer that has related rows in tblOrder, the database engine will not allow it because that would orphan the records in tblOrders. However, even with RI enforced, if there are no orders for a customer, then RI will allow you to delete the customer because deleting him would not orphan any records in the Orders table and therefore not violate any data integrity.

Once you enforce RI, you also have two other options you can use - Cascade Update and Cascade Delete.

Cascade update is very rarely used since most tables will have a PK that is an autonumber. Since you cannot change an autonumber value, there is no change to cascade. Access will allow you to still check the box but it would never do anything. However, there are some tables where you might elect to use a natural key such as a State table. In this case, you could make the two digit abbreviation the PK for the state table so when you are looking at raw data in any table that has an address, you see CT for Connecticut rather than a number. Since the PK is not an autonumber, you could change its value. Granted, states are not likely to change their names but they could. In this case, if you change the abbreviation for CT to LR (Long River), if cascade Update is selected, the change would be propagated to every row in every related table where CT was formerly used. On the other hand, if Cascade Update is not checked, then the database engine would not allow you to change the state's abbreviation.

And finally, we have Cascade Delete. This one is delicate. Only a few relationships make sense to allow deletes to cascade. For example, for the relationship between Customer and Order, you would never want to cascade the delete. It could delete active, unfilled orders as well as historical orders. But moving down the hierarchy, tblOrders has a child table called tblOrderDetails. This is the table that contains one row for each item ordered. Normally you would not want to allow orders to be deleted but perhaps your business rule says that if an order is cancelled before it was filled, then it is OK to delete it. In this case it makes sense to check Cascade Delete because no row in OrderDetails makes any sense unless it is connected to its parent order. Therefore, if you delete an Order, you also want to delete the related order details. But you would NEVER enforce Cascade Delete on relationships with the State table. If the big one hits California, you don't necessarily want to delate all addresses in California.

Bottom line -
Enforce RI - always
Enforce Cascade Update - for non-autonumber PKs
Enforce Cascade Delete - when it makes sense to delete all child records when a parent is deleted. But always think carefully through this since you can delete a lot of data if you make a mistake. That doesn't mean that you shouldn't do it, just make sure you understand why you are doing it.

 

[davegoodo]

Relationships are defined in the relationships window of the database that houses the physical tables. So, in a split database (which is best practice), the relationships can only be defined in the BE since that is where the tables reside. If you go to the relationships window in the FE, you will not see the actual relationships which doesn't mean that they aren't there and being enforced. If you join tables in the relationship window in the FE, you can create a visual of the linkages but you are not creating a relationship and you cannot enforce Referential Integrity.

Joins are used in queries to specify how to match the records in one table to those of another. Although most of your joins will mirror the relationships, you will have instances where they don't. This is most likely to happen with imported or temporary data which haven't been defined to have relationships. The rule for a join is only that the columns being joined must match on data type. The join doesn't need to make logical sense. For example, if you import a spreadsheet and the user was sloppy doing the data entry and sometimes put the first name in the last name field and the last name in the first name field and other times did the entry correctly, you have a mess to clean up. One way is to create a query that joins the first name column of the imported data to the last name column of your customer table. In normal circumstances, this would make no sense but if you are looking for bad data, this could help you to find it.

RI is used to enforce rules on defined relationships. When you check the RI box on the join between tblCustomer and tblOrder and select the CustomerID from both tables, what you are telling the database engine is that it is not valid to have a CustomerID in tblOrder that does not already exist in tblCustomer. Also, if you try to delete a row from tblCustomer that has related rows in tblOrder, the database engine will not allow it because that would orphan the records in tblOrders. However, even with RI enforced, if there are no orders for a customer, then RI will allow you to delete the customer because deleting him would not orphan any records in the Orders table and therefore not violate any data integrity.

Once you enforce RI, you also have two other options you can use - Cascade Update and Cascade Delete.

Cascade update is very rarely used since most tables will have a PK that is an autonumber. Since you cannot change an autonumber value, there is no change to cascade. Access will allow you to still check the box but it would never do anything. However, there are some tables where you might elect to use a natural key such as a State table. In this case, you could make the two digit abbreviation the PK for the state table so when you are looking at raw data in any table that has an address, you see CT for Connecticut rather than a number. Since the PK is not an autonumber, you could change its value. Granted, states are not likely to change their names but they could. In this case, if you change the abbreviation for CT to LR (Long River), if cascade Update is selected, the change would be propagated to every row in every related table where CT was formerly used. On the other hand, if Cascade Update is not checked, then the database engine would not allow you to change the state's abbreviation.

And finally, we have Cascade Delete. This one is delicate. Only a few relationships make sense to allow deletes to cascade. For example, for the relationship between Customer and Order, you would never want to cascade the delete. It could delete active, unfilled orders as well as historical orders. But moving down the hierarchy, tblOrders has a child table called tblOrderDetails. This is the table that contains one row for each item ordered. Normally you would not want to allow orders to be deleted but perhaps your business rule says that if an order is cancelled before it was filled, then it is OK to delete it. In this case it makes sense to check Cascade Delete because no row in OrderDetails makes any sense unless it is connected to its parent order. Therefore, if you delete an Order, you also want to delete the related order details. But you would NEVER enforce Cascade Delete on relationships with the State table. If the big one hits California, you don't necessarily want to delate all addresses in California.

Bottom line -
Enforce RI - always
Enforce Cascade Update - for non-autonumber PKs
Enforce Cascade Delete - when it makes sense to delete all child records when a parent is deleted. But always think carefully through this since you can delete a lot of data if you make a mistake. That doesn't mean that you shouldn't do it, just make sure you understand why you are doing it.

Thank you for such a comprehensive article, I've taken a copy of it.
Much appreciated

 

[Pat Hartman]

You're welcome. It is hard to find concise explanations of stuff like this. It is quite easy to go on for pages, especially if I were to provide examples. I only just scratched the surface. The rules are logical and discoverable if you make some tables and play with the settings.