|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Widows & Orphans By Richard Rost 
5 years ago
Widow / Orphan Protection, Prevent Missing Data Learn how to prevent orphaned records in your Microsoft Access databases. These are records on the "child" side of a relationship that are missing a parent. Alaina from Seattle, Washington (a Platinum Member) asks: I'm working with your TechHelp Free Template (thank you, by the way) and I've noticed that it's possible for a user to enter order detail items without having first created an order. This causes them to be lost in Limbo. How can I prevent this? MembersMembers will learn how to prevent widows, which are parent records that have no children. If you want to force a user to enter line items in an order before allowing the order to be saved, use this technique.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
LinksInvoicing: https://599cd.com/Invoicing
IntroIn this video, we will talk about how to prevent orphaned records in your Microsoft Access databases, focusing on order and order detail tables. I will explain what orphaned records are, show how they can occur when entering line items without a parent order, and demonstrate how to use table settings to enforce referential integrity and stop users from creating these unlinked records. We will also discuss best practices for data deletion and alternatives like cascade deletes. This tutorial is based on the free TechHelp template and answers a viewer question about managing data integrity in Microsoft Access.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, we are going to talk about Widow and Orphan Records. I am going to show you how to prevent orphaned records in your Microsoft Access databases. What is an orphaned record? An orphaned record is if someone tries to type in a line item, for example, in an order without specifying the order ID, without giving it a customer and a description and all that other stuff that creates the order record. This way, you have just got order details floating around out there without an order ID on them. Today's question comes from Elena in Seattle, Washington, one of my platinum members. Elena says, I am working with your TechHelp free template. Thank you, by the way. You are welcome. I have noticed that it is possible for a user to enter order detail items without having first created an order. This causes them to be lost in limbo. How can I prevent this? Let me show you. Here is the free TechHelp template. You can download a copy of this on my website. If you would like to, there is a link down below the video. It is absolutely free. It has got a customer list with customer form. On the customer form, there is an order form. Now, the way you are supposed to do it is, you put a description in, parts, and pick a customer. Then you put in some items in here: underboard, one at 30, and so on. I have a whole video on how this was built. Again, links are down below. However, it is possible to come into orders, and you are at a blank new order. You come down here and you just type in stuff; line items without selecting stuff up here. Notice this still says new. This means an order ID has not been generated yet. Because an order ID has not been generated, this line item has no order to attach itself to. If I move to a new record, stuff is in there. But if I close this and go back in, there is no stuff. What happened? Let's take a look at the order detail table. Look at that. Stuff has no order ID. It is blank. It is null. It has not been assigned yet. How do we fix that? I will go into order detail ID in design view. Find the order ID. Right now, it is set to zero for a default value. Let's get rid of that, and let's set required to yes. What this means is we are going to start off with no value. It is going to start off null. But we are going to make it required, which means the user is not going to be able to enter in an order detail item unless it has got an order above it. Save this. Now it says data integrity rules have changed. Existing data may not be valid for the new rules. In other words, the data that you currently have in the table might not match your new rules. It says it might take a long time. We only have a handful of records, so just say yes. If existing data violates the new settings, are you sure you want to do this? Go ahead. We have data in here that does not match the new rules, so we have to delete that one. To find those records, if you have tons and tons of data in your database, I have another video that I did recently called Missing Data, How to Find Missing Data. That is if you have a relationship set up between two tables, like the order table and the order detail table, and you are missing data on one side of it. It creates something called an outer join. I will put a link to that video down below in the links section too. It is called Missing Data. Now if I come into my customer form and go to orders, and I go to add a new order, if I come down here and start typing in a product, look what happens. You must enter a value in the order detail T order ID field, which means there is no order ID specified in here. If I keep typing in, that is fine, it will let me. But if I try to leave this record, I get that warning message again. Nothing that I do; I cannot leave this record at all until it has a value. You are going to have to hit escape, come up here, and pick a person. Now it has generated an order ID. Now I can come down here and attach an item to it. That is how you prevent what are called orphans. You simply make the related field in your subform, on the many side, you get a one-to-many relationship. If you are not familiar with relationships, go watch my relationships video. I will put a link to that down below. Watch Relationships. Orders to order details is a one-to-many relationship, one order to many details. You simply make the primary key for the order table, which is the foreign key in the order details table, required, and that way you cannot have orphaned records. It is just not allowed. The database will not allow it. Now, you can still create orphaned records if you delete the order. If I delete this order for right now, I am creating an orphaned record in there. It will be a detail item without a parent. How do you prevent that? There are a couple of ways you can. First of all, I strongly recommend in most cases that you do not delete data. If this order is now invalid, make another field up here. Call it active or call it valid order or whatever you want to call it. Just check that as canceled, for example. I have another whole video on why you should not delete data, order data, customer data, all that stuff. Keep it in your database. Just mark it canceled. It is better. Trust me. Go watch that video too. It is called Don't Delete Data. But if you are going to delete data, let's say it is temp data and you do not really care about it, you can set up something called cascade deletes. It involves setting up referential integrity. Then, cascade deletes is if I delete the parent, it deletes all the children. That is dangerous because if you delete a customer, for example, it will delete everything related to that customer. I do not like doing this. I have a video that shows you how to do it though. It is called Cascade Deletes. I will put a link down below if you want to go watch that, but I do not like doing that. One problem with cascade deletes is that you lose the ability to have referential integrity in a database with linked tables. So if you want, you can set up a manual way to delete the child records. For example, if you delete an order, delete the details, but you have to do it yourself manually with a little bit of VBA code or at least an action query. I will show that in an upcoming video. I have not covered that yet. In my Developer class, I have shown how to copy an order, which involves copying all the details. That is in my Developer Level 25 lesson. I will put a link to that down below too. I will cover deleting an order and deleting all of its children in a future video. Now, how do you go the opposite direction? We have covered preventing orphans. Orphans are detail items that have no parent. How do you prevent widows? What if you want to say, you cannot put an order in without items down here? I do not want any blank orders in my system. If you are going to put an order in the system, you have to have at least one item down here. That involves a little programming, and I will cover that in the extended cut for the members. Want to learn more? In the free version, in the free video you just watched, I showed you how to prevent orphans where you have records that are typed into the detail section that are missing the parent. In the extended cut, I go the other way. If you want to make sure that you do not have orders that have no line items, if you want to make sure that each order has at least one line item on it. This could be anything that has a one-to-many relationship, whether you are dealing with customers and contacts or vendors and products or whatever. I use orders and order detail items because everyone can relate to this. But I have talked to a few people that have said, what about, I have a project and I have to have at least one subcontractor on it. I do not want them to save the project without a subcontractor. You have to use this technique. It involves a little bit of programming, a couple lines of code, not much. We are going to use the OnUnload event check with a DLookup to see if there are any sub-records, child records. If not, say, you have to enter at least one item, would you like to cancel the order? In case they decide they do not want to do it. Otherwise, they will not be able to close the form. That is all covered in the extended cut for members. Remember, as an up-get access to all of my extended cut videos, gold members can download this database. How do you become a member? Click the Join button below the video. After you click the Join button, you will see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I have built in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. Platinum members get all of the previous perks plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more. Do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more. If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select all to receive notifications when new videos are posted. Click on the Show More link below the video to find additional resources and links. You will see a list of other videos, additional information related to the current topic, free lessons, and lots more. I no longer send out email notifications when new videos are posted, so if you would like to get an email every time I post a new video, click on the link to join my mailing list. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It is over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar. It is also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. Click here to watch my free Access beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is an orphaned record in the context of Microsoft Access databases?A. A record in a parent table without any child records B. A record in a child table that lacks a parent record reference C. A new record that has all fields blank D. A record that has been deleted but not removed from the database Q2. In an Order and Order Detail system, how can orphaned detail records be created? A. By deleting the order after entering order detail items B. By entering order detail items without having a valid order ID assigned C. By saving an order with no customer information D. By entering duplicate order numbers in the order table Q3. What design change did Richard demonstrate to prevent orphaned records in the Order Detail table? A. Setting a default value for Order ID to zero B. Marking the Order ID field as required C. Allowing blank values for the Order ID field D. Removing the Order Detail table completely Q4. What happens if existing data in the Order Detail table does not match new data integrity rules? A. Access will automatically fix the data B. The data will be deleted automatically C. You will need to delete or fix offending records manually D. Access will ignore the new rule for old data Q5. What does setting the Order ID field as "required" in the Order Detail table accomplish? A. It allows details to be added without an order B. It prevents saving a detail record without a valid parent order C. It automatically generates an Order ID if one is not supplied D. It disables data entry in the Order Detail form Q6. What is a one-to-many relationship in database design? A. One child record relates to many parent records B. One parent record relates to many child records C. Many parent records relate to many child records D. Many tables relate to one database Q7. What is cascade delete in Microsoft Access? A. Deleting only child records and keeping the parent B. Deleting all records in the database C. If a parent record is deleted, all related child records are also deleted D. Deleting one record at a time manually Q8. Why does Richard recommend not deleting records such as orders or customers? A. It is illegal to delete customer data B. Deletion takes too long in Microsoft Access C. It is better to mark records as canceled or inactive for data integrity D. Microsoft Access will not allow delete operations Q9. What programming technique was suggested to ensure every order has at least one line item (preventing widow records)? A. Using cascade deletes in relationships B. Setting up a default line item in the order C. Writing code using the OnUnload event to check for child records before closing the form D. Making all fields required in the order table Q10. What does referential integrity ensure in a relational database? A. Each table has a unique primary key B. Child records always have a corresponding parent record C. All tables can be freely deleted D. Data can be duplicated as needed Q11. What should you do if you want to find records that violate referential integrity or are missing related data? A. Use cascade deletes B. Run a query with an outer join to find missing data C. Rebuild your entire database D. Use only forms for data entry Q12. If you want to make sure deleting a parent record also deletes all child records, which option should you select in the relationship window? A. Enforce referential integrity only B. Cascade update related fields C. Cascade delete related records D. Ignore referential integrity Q13. What is a drawback to using cascade deletes, especially with linked tables? A. It can be slow to run queries B. You lose the ability to enforce referential integrity C. It always causes data corruption D. It disables data entry in all tables Q14. How can you manually handle deleting child records when a parent is deleted? A. Use VBA code or an action query to delete child records first B. Access deletes them automatically C. Set all fields in the child table to allow null values D. Remove referential integrity settings Q15. Why does Richard use orders and order details as his main example for one-to-many relationships? A. Because everyone is familiar with orders in real life B. Because Access only supports order tables C. Because these tables require no special setup D. Because it is the only way to explain relationships Answers: 1-B; 2-B; 3-B; 4-C; 5-B; 6-B; 7-C; 8-C; 9-C; 10-B; 11-B; 12-C; 13-B; 14-A; 15-A DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone focuses on how to prevent orphaned records in your Microsoft Access databases and discusses the related concepts of both orphan and widow records.To start, let's define what an orphan record is. In the context of a database like Access, an orphaned record occurs when a related record such as an order detail is entered without being linked to its parent record, like an order itself. For example, if someone enters a line item for an order without first creating the main order and specifying a customer or description, the line item is left unattached. These records effectively float in limbo without a valid order ID. This issue was highlighted by one of my students, who noticed in the TechHelp free template that it's possible for users to enter order detail items without creating the corresponding order first. As a result, these details are not properly linked to an order and can get lost in the database. Let me walk you through how this happens and how to correct it. In the free template database, the proper workflow is to start by filling in the main order fields: add a description, select a customer, and only then begin entering items. However, Access does not stop users from adding line items before the main order record is created and an order ID generated. When this happens, details are entered with no order ID, and these lines are effectively lost after you leave the form since they are not attached to anything. If we look directly in the order details table, you will find these records lacking an order ID. They're sitting there with blank, or null, values for the order ID, which means they are orphaned. To prevent this, you need to enforce a rule in your database's design so that order details cannot be entered without a corresponding order. This is achieved by making the foreign key in the order details table, the OrderID, a required field. In table design view, set the "Required" property of the OrderID field to "Yes" and remove any default value. This ensures that no detail items can be saved unless they are attached to an order with a valid ID. Once you've changed this property, Access will warn you that data integrity rules have changed, and existing records might not be valid. If you have old records that violate these rules, like orphaned details, you will need to delete them or otherwise address the missing parent records. If your database is large and you need help identifying invalid records, check out my "Missing Data" video, which goes into detail on how to find and fix them when you have invalid relationships, such as missing parent records. With this rule in place, if a user tries to enter a new line item in the order subform without first generating an order ID (for instance, by not selecting a customer or filling out the main order form), Access will not allow the detail record to be added. The user receives a message that a value is required for the OrderID field and cannot proceed until the order information is fully entered, preventing orphaned records at the source. This process relies on setting up proper one-to-many relationships in your database, with the primary key of the main table (orders) acting as the foreign key in the related table (order details) and making that foreign key required. There is another scenario to consider: what happens if the order (the parent record) is deleted? This can leave behind orphaned detail records as well. The best practice is not to delete data from your database. If you need to invalidate an order, add a field (such as "Active" or "Canceled") and mark the order instead of deleting it. This preserves historical data and maintains data integrity. I have a separate video titled "Don't Delete Data" that explains why this approach is better. If you truly need to delete orders and want any associated details to be deleted automatically, this can be done by enabling cascade deletes through referential integrity in your tables' relationships. However, be careful with this feature since deleting a parent record will remove all related child records, which is irreversible and can cause loss of important data. If you decide to use this method, I have another video called "Cascade Deletes" that explains how to set it up, though I generally recommend handling deletions manually, especially in databases with linked tables. This manual approach involves writing a simple piece of VBA code or an action query to delete child records when a parent is deleted, and I plan to cover that process in a future lesson. Now, let's talk about the reverse problem: preventing widow records. This is when a parent record such as an order is saved without any child records or line items, which you might not want. For example, you may want to ensure that every order has at least one order detail. Preventing this scenario requires a bit of programming. In the extended cut for members, I will show you how to use the Form's OnUnload event to check, with a DLookup function, if child records exist. If not, you can prompt the user to either add at least one item or cancel the order before closing the form. This technique applies to any one-to-many relationship where you want to enforce at least one related record on the many side. To recap, in this video I explained how to block orphaned detail records by making the foreign key in your table required, ensuring that no detail item can exist without a parent record. In the extended cut, you can learn how to prevent parent records (orders) from being saved without at least one child (order detail). If you are interested in getting access to the extended cut with more in-depth solutions, or if you would like to download the sample database featured today, consider joining as a Silver or Gold member on my website. Gold members get downloadable files as well as access to my Code Vault and other exclusive materials, while Platinum members get all that plus my complete beginner and some expert courses in Access, Word, Excel, Visual Basic, and more. As always, my free TechHelp videos will continue to be available. If you found this helpful, you can support the channel by liking, commenting, and subscribing so you never miss new releases. To find more resources, including the sample database, free lessons, and relevant links to all the topics mentioned here, you can visit my website. If you would like notifications by email for new content, make sure to join my mailing list. For those just getting started, my Access Level 1 course is free and covers all the basics of Access database building. It is available on both my website and YouTube channel, and Level 2 is just one dollar or free to channel members. If you have your own question you'd like answered in a video, visit the TechHelp page on my website to submit it. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListDefinition of orphaned records in Access databasesHow orphaned records are created in order-detail tables Identifying orphaned records in the order detail table Setting the Required property for foreign keys in table design Enforcing data entry rules to prevent orphan records Responding to data integrity rule changes in Access Fixing existing orphaned records after rule changes Demonstration of error messages when entering orphan detail records Establishing one-to-many relationships between tables Making foreign keys required to enforce referential integrity Discussing risks of deleting parent records and creating orphans Alternatives to deleting data, such as marking records as inactive Overview of cascade deletes and related dangers Manually deleting child records using queries or VBA |
||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access widow, orphan, referential integrity, prevent missing parent, entry into subform, relationships, related subform PermaLink Widows & Orphans in Microsoft Access |
