Quick Queries #53
By Richard Rost
3 months ago
Self-Join Relationships for Emailing Related Contacts
In this Microsoft Access tutorial I will show you how to send email to multiple recipients using related tables, set up self-joined relationships and combo boxes for linked records, work with query joins and union queries, explain transactions, discuss referential integrity in split databases, talk about upgrading MDB to ACCDB file formats, provide answers about Access Database Cloud for Mac users, and clarify the use of TempVars in your projects.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Quick Queries, recordset loop, send email with CDO, secondary contact table, additional person ID, combo box contact form, self-join relationship, union query, referential integrity, split database, SQL transactions, RunSQL vs ExecuteDatabase, Access Database Cloud, ACCDB upgrade, tempvars vs public variable
Transcript
Quick Queries are all the questions that get posted, comments, all that good stuff that don't need a video on their own, but I like to put them all together into a bundle. That's what we call Quick Queries, and we do it every Friday, and it's pretty cool.
Let's see where we're starting first. First up today, we've got an email from Brent, one of my Gold members, and he's trying to do a recordset loop and send emails to everybody in his database. I've got several videos on how to do this. Here, I'll give you some links.
First up, send email with CDO. This lets you send email directly from your Access database by connecting directly to the mail server without having to use a program like Outlook. It's much, much better. Check it out. You can use a recordset to loop through records to send an email. See here: send an email using the technique that I showed you in the other video to the person who's ever in the query, like your customer, for example.
Brent posted a whole bunch of code here, which isn't really relevant, but the problem is he wants to send it to a second person, and that person is listed in a secondary contact table. Of course, all the guys on the site helped him out. Kevin and Raymond and everybody, and he was able to figure it out, but I wanted to just show you what the solution ended up being because this is actually pretty important, and whether you're sending email or not, this is pretty cool. So let's take a look at how this would look in the TechHelp template.
Here's the customers. Every customer has an email address and every customer has contacts. Now, let's say as part of these contacts you can tag another person. Then you want to include that person in your email, and the person is another customer associated with them, another person in your database.
I don't know why I went into design mode first. We have to add it to the table first, so in the contact table, we could add another person. Let's call it "additional person ID." That's not a number. There's a number of type long and a literal foreign key, and that is linked back to another customer. So, save that.
Now, in our contacts, we can add an additional person onto here. Design view. There we go. Open that up. Let's drop a combo box in here, so we can pick another person. Drop it right in here. We're going to do that, and then we're going to get data from a customer, and then we're going to bring over this. Use Customer First Last Q. Let's do that. Table or query, it's going to be queries, Customer LFQ. That's a query that joins together last and first names, so you can see them both in the combo box.
We'll bring over both of those, sort it, whatever. LF is fine. This is what it looks like. We're going to store that in the additional person ID, and then finish. Delete that label.
Now, you can tag a person with each contact, another person. Save that and close it. Now, for me, let's say I'm going through here, I want to tag Jane Cobb. I want to tag Wesley Crusher, and this one down here, I want to tag Killy Fry. Let's say on Jim Kirk's contacts, he's going to tag Alex Lifeson and Miles Wilbribe.
Now, the key is, I need a query that is going to allow me to loop and send email to all of those people, the main person and the other people that they're referencing. So now you can make a query, create query design, bring in the customer, bring in the contact table, and then bring in the customer table again.
This relationship here, we're going to make this a left join so that you see all of the customers whether or not they have contacts. If they don't have any contacts, they won't show up. That's what a left join does. Right outer joins. Now we're going to join this table by the additional person ID. This relates to another customer record, and then also do the same thing.
I want to see the customer ID, maybe their first name, last name if you want to, email address, and then if you want to see who their additional people are, then bring in this customer ID and that email address. Now, when you run it, you'll get one record for each of the customers on the left and then one record for each of the people that they're joined to.
You're going to get some duplicates, so you have to filter that out in your loop. If you don't want to see all the ones over here that are blank, you can adjust the query otherwise. Now, here you can see, there's that one, there's that one, there's that one. That's called a self-joined relationship. It's basically a table relating back to itself. It's going through an intermediary table here, but it's the same thing.
I show you how to deal more with self-joined relationships in my genealogy videos. Basically, it's a person to his father, to his mother, to his children. That's a self-join. It's a customer to a customer, basically. That's also why in a lot of databases, I tell you if you've got customers and vendors and employees and all these different types, they're all people. Just put them all in the same person table and then just denote what they are.
This person's a customer, and this person, because people can also be multiple things. The same person can be an employee and a customer and technically a vendor, and it's all the same person. So, I would make that a many-to-many relationship with a junction table, and then you can pick all of the different things, all the different categories that person falls into.
If you run a company, one of your employees could be a vendor. Maybe you sell his biscuits on the weekends, and he comes and buys your fruit baskets. No reason for three separate tables.
How you handle the loop here is up to you, and that's more a matter of what your email is supposed to be doing. You want to email each of these people, and then if they have this, send the same email to this guy related with some information about the primary customer, or do you just want a unique list of people?
If you just want a unique list, there's no reason to do this join. You could do that and then union that list in two. That's another option. There's something called a union query. You could say: just give me all the primary email address people and then all the secondary email addresses, put them all in one big query, aggregate it so you don't get any duplicates, and then send to that list.
That's another option. But if you want to send an email where there's information that's going to this guy related to this guy, like maybe he's part of the family and you want to say that "Oh, you and Jim Kirk are blah blah blah," then I would just run my recordset down this, ignore duplicates, so literally right in your loop, you ignore different duplicates, and then if this field shows off, also send to that one.
There are so many ways you could do this. There are like 15 ways you could do this. But you've already figured it out. I just wanted to share this with other people. So, there we go.
Let's head over to YouTube, and Edwin's got a question about Fitness 25. Now, as of today, only Fitness 21 has gone public, so Edwin's a member, he's a little ahead. I just want to say that. This goes out to everybody: Wait until I'm done with Fitness 28 before you ask me any questions about the filtering in that log table, because I've changed it a lot between now and the next couple of lessons.
All the fitness people who are listening, and the rest of you who are not watching the fitness database series, you're missing a really cool database series. It's got lots of cool stuff in it. Just because you don't care about fitness, if you care about databases, watch this because it's really good. It's one of the best series I've done, lots of cool stuff in it. I'm already up to level 28, and there's probably going to be 40 or 50 at least, and just cool stuff. So check it out. But, yeah, don't worry too much if you've got problems with the filtering at this point. It gets better, so wait until you finish 28.
Speaking of Fitness 20, Georgia says at time index 801 you said: "You could speak it if you want to if you remember." What do you mean? Well, in one of the members' extended cuts, I showed you how to make the database speak, and you can add that too if you want to. Just go watch this video. That's what I said - you can speak it if you want to make the database speak the error message or whatever that was at that moment.
Yes, Dingus Baddas caught one of my Back to the Future references, where he walks into the Cafe 80s, where it's always morning in America, even in the afternoon.
Easy Speak 101: Thanks for a solution when referential integrity fails sometimes without error message. Yeah, I don't rely on referential integrity ever. Ever since I started working with split databases and the fact that I've got databases that have multiple split tables, you lose the ability to do referential integrity if the tables are in different database files, not to mention if you've got some data in SQL Server and some is local. So I always work on a coding solution for that if I want to have a user delete something, which I try not to let them do. But if they do, then I make sure I delete all of the child records manually myself and don't give them any opportunity to delete stuff any other way but using my button.
That's why I say you never let your users into your tables because they can make a mess of things.
Next up, Kristoff says he wanted to know - I mentioned a video on transactions, and he wanted to check and make sure it's available somewhere. Now, I'm not sure what type of transactions you're talking about, but yes, I've got videos on it.
If you're talking about tracking bank transactions, watch this video. But I think you're talking about SQL transactions since you were asking about my RunSQL versus ExecuteDatabase video. So yes, Microsoft Access Developer 49 covers transactions. It's a little bit more in-depth than I usually go into for a TechHelp video, but yes, we cover it in here and show you everything.
A transaction, for everybody else who's wondering, is when you've got a series of SQL statements and all of them have to succeed or else none of them will succeed. For example, let's say you're doing a bank transfer that involves two SQL statements. You're taking money out of one account and you're adding money to another account. That's two different SQL statements.
You can wrap those inside of a transaction so if either one of those fails, then they both fail, the whole transaction fails. If you can't pull money out of one of them, then you don't want to deposit it, or if there's a problem depositing it, maybe the account's closed, then the first one gets rolled back. That's what transactions are. If you're curious, check it out: Access Developer 49. It's on my website. I'll put a link down below.
Nigel One writes that he's finally catching up with the series and is seeing lots of things that can transfer to other databases. That's exactly the point.Thank you very much for posting that. That's what I'm trying to tell everybody else.
If you're not watching my Fitness Database series, you're missing out. There is lots of cool database stuff in there. You don't have to be building a fitness database to benefit from it, so check it out. Thanks, Nigel.
Camerick TV says, "Is Access Database Cloud browser based and compatible with Mac? If not, what database solution is available to Mac users?"
First of all, I highly recommend Access Database Cloud if you want to share your database with other people around the world and you don't want to bother with setup. If you don't want to have to deal with setting up SQL Server and migrating your tables, Access Database Cloud is a good solution. With Access Database Cloud, you are basically setting up an instance of a computer in the cloud that you can log in to through your browser. Yes, through any browser - PC or Android. I use it on my Android phone all the time. Your Mac should access it just fine. It's basically a screen share, and you can set up multiple instances. You have multiple people that all have their own instances, and it's really cool. Check this video out for more information. I'll put a link down below, but yes, it will run on your Mac.
As far as whether Access itself will run on a Mac, it only will if you're running an emulator like a Windows emulator. In fact, Alex has a couple different things you can use, like VirtualBox. I think there's something called Parallels. I am not a Mac user. There is a really big, long thread on my website about this subject. I'll put a link to this down below. I'm not a Mac user. I haven't used a Mac since high school. I think I used an Apple II or something even then, but no, I don't use Mac Pro or Apple products mostly, so that's the help - ask Alex that one.
Next up, Green Niche says, "I have two MDB databases created before 2000. I use them day in and day out and have extended, amended, and added code over the years. I've never had any bother opening, but should I change them to ACCDB?"
Yeah, I would. Take a day and back everything up first, of course, but I would upgrade them to the newer format. There are some benefits, and I'm not going to go into all of them, but I definitely would. ACCDB is a lot less likely to get corrupted, for example. It's not hard—just create a blank new ACCDB file and import all the objects. That's the easiest way to do it.
Now, Out by 50 is saying that he's got some qualms with calling tempvars variables. Yeah, sometimes when I'm in the middle of making a video, I might refer to it as a variable. It's not technically correct, you're right, but you can think of it like a variable. There are a lot of pros and cons, as you know. My big thing is they survive Access throwing up an error and crashing, whereas a public variable will not. And yes, it being visible from SQL is a big plus too. I might have called it a variable, but okay, sorry, my bad, you got me.
And with that, we're going to end another Quick Queries Friday. Thanks for watching. I hope you learned something. Live long and prosper, my friends. Enjoy your weekend. I'll see you next time.
TOPICS:
Sending email using CDO in Access
Adding a foreign key for additional contacts
Creating a self-join relationship in Access
Linking contacts to additional people via combo box
Building a query to combine main and additional contacts
Setting up left joins and self-joins in Access queries
Filtering duplicate records in Access queries
Using union queries to combine email lists
Advantages of a single People table for multiple roles
Implementing many-to-many relationships with a junction table
Explanation of SQL transactions in Access
Converting MDB files to ACCDB format
Using Access Database Cloud on Mac and browser
Access compatibility solutions for Mac users
Advantages and usage of TempVars versus variables in Access
COMMERCIAL:
In today's video, we're discussing a variety of questions sent in by viewers, like how to use a recordset loop to send emails to both primary and secondary contacts in your Access database, and ways to build self-join relationships using a contact table. You'll also hear tips about splitting databases, why upgrading from MDB to ACCDB files is a good idea, how transactions work in Access, and some advice for using Access Database Cloud on a Mac or mobile device. We also talk about tempvars, making your database "speak," and give a quick shoutout to the ongoing Fitness Database series. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the main purpose of using CDO to send email from Access?
A. To send email directly from Access without relying on Outlook
B. To speed up data entry in Access forms
C. To secure database backups
D. To import data from Excel
Q2. What is the function of a Recordset loop in the context of this video?
A. To loop through forms and print reports
B. To loop through database records and perform actions like sending emails
C. To update table designs programmatically
D. To enforce referential integrity between tables
Q3. When adding an "additional person ID" to the contact table, what is its main purpose?
A. To store a secondary email address for a contact
B. To reference another person in the customer table, creating a link between contacts
C. To create a primary key for the contact table
D. To backup customer data
Q4. What is a self-joined relationship in databases?
A. It links two different databases together
B. It relates a table back to itself, usually through a foreign key
C. It prevents duplicate records in a table
D. It splits a table into multiple smaller tables
Q5. What type of query can you use to combine results from two different sets of data into one, such as primary and additional email addresses?
A. Append query
B. Update query
C. Union query
D. Crosstab query
Q6. Why is it suggested to store all people (customers, vendors, employees) in a single table?
A. To enforce referential integrity across multiple tables
B. Because people can have multiple roles and categories, it reduces redundancy
C. To increase query speed
D. To simplify backup processes
Q7. What is a junction table used for in a database?
A. To store temporary calculated data
B. To manage a many-to-many relationship between two tables
C. To store database queries
D. To backup the database schema
Q8. If you want to make sure users do not accidentally delete important data from your tables, what approach is recommended?
A. Allow open table modification and trust users to be careful
B. Use coding to handle deletes and remove child records as needed
C. Only use database-level referential integrity and let users handle the rest
D. Allow users to use the Access delete button directly on the table
Q9. What is a database transaction?
A. A process of updating a single record
B. Multiple SQL statements that must all succeed or else all changes are undone
C. Sending email to multiple customers
D. Importing data from another database file
Q10. What solution is suggested for running Access on a Mac if native support is not available?
A. Use Access Database Cloud or a Windows emulator like Parallels or VirtualBox
B. Convert Access files to Excel workbooks
C. Switch to using Google Sheets
D. Use Access for Mac available in the App Store
Q11. What is the main benefit mentioned in the video about upgrading from older MDB files to ACCDB format?
A. Improved speed of data entry
B. ACCDB files are less likely to become corrupted
C. MDB files cannot store numbers
D. ACCDB files can only be accessed online
Q12. How are TempVars different from public variables in Access?
A. TempVars cannot survive errors or crashes
B. TempVars survive Access errors and have wider scope, including visibility from SQL
C. TempVars are slower to use than public variables
D. Public variables can be accessed from SQL but TempVars cannot
Q13. What is recommended if you want to send different emails to each person, possibly including information about their related person?
A. Use a unique aggregated email list
B. Send only one email to each customer
C. Loop through the recordset and send emails including related information using the self-joined query
D. Use crosstab queries to manage emails
Q14. Why should you not let users have direct access to tables in Access?
A. It makes the database run faster
B. Users may accidentally delete or change important data
C. It saves storage space
D. Users may create new queries
Q15. What is a left join used for in Access queries, as discussed in the video?
A. To show only records that have matching entries in both tables
B. To show all records from the main table and only those from the joined table that have a match
C. To delete unmatched records
D. To merge tables into one
Answers: 1-A; 2-B; 3-B; 4-B; 5-C; 6-B; 7-B; 8-B; 9-B; 10-A; 11-B; 12-B; 13-C; 14-B; 15-B
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.
Summary
In today's Quick Queries video, I am gathering together some of the questions, comments, and messages that do not quite require their own full-length video, but which are still useful for everyone. I like to bundle these together every Friday, and there is always something interesting that comes up.
Let's begin with a question from Brent, who is working on sending emails to everyone in his database using a recordset loop. I have several detailed videos explaining how to do this, including instructions for sending email directly from Access through CDO. This allows you to send messages without relying on external programs like Outlook, which is much more efficient. You can run a recordset loop and send an email to each recipient by pulling them from a query, such as your Customers table.
Brent needed to include a second recipient in his emails, pulling that extra email address from a secondary contacts table. Several helpful folks on the site discussed it with him, and they found a solution, but I wanted to point out the best way I have found to handle these situations, because it is a useful technique even if you are not sending emails.
Think of a typical Customers table, where each record has one primary email address. Each customer might have multiple contacts, and sometimes you want to tag an additional person to that contact—for example, relating one customer record to another through an "additional person" field. This is a classic self-join situation in database design.
To set this up in Access, you would first add an "Additional Person ID" field to your Contact table. This would be a long integer acting as a foreign key related back to your Customers table. With this in place, you can set up a form with a combo box allowing you to pick another customer for each contact. Using a query to concatenate first and last names, you get a nice display in the combo box. Now, each contact can be linked to any other person in your database.
For example, you might tag Jane Cobb or Wesley Crusher as secondary contacts for one customer, and Alex Lifeson or Miles Wilbribe for another. What you need next is a query that lets you send messages to all the relevant people: both the main contact and any additional tagged contacts. In query design, you bring in the Customer table, the Contact table, and a second instance of the Customer table, which you join through that Additional Person field. Using a left join ensures you pull all your primary customers, even if they do not have additional contacts.
This structure does result in a self-joined relationship, as your table relates back to itself through an intermediary. I discuss similar structures in my genealogy videos, where you might relate an individual to their parents or children. Whenever you have customers, vendors, and employees in the same organization, you may want to store them all in a single "People" table and use a junction table to categorize them, as any one person might belong to several categories. There is rarely a need for three separate tables if a single table can represent everyone with appropriate flags.
When running your email loop with this type of setup, you could either generate a unique list of people to avoid duplicates, perhaps by using a union query to pull all primary and secondary email addresses into one aggregated result, or you could process each record and handle duplicates as they arise. Your approach depends on whether each message is unique to either person, to the relationship, or if you simply want to mail everyone just once.
Now, switching to a question from Edwin about one of my Fitness series videos—only Fitness 21 was publicly available at the time, but members were following along with advanced lessons. The key advice I give to everyone working with the Fitness series is to wait until you finish lesson 28 before worrying about filtering in the log table, because that section changes significantly throughout those lessons. And even if you are not interested in fitness, this series covers a lot of useful Access database techniques, so I highly recommend giving it a look regardless of your primary focus.
Georgia asked about a comment I made in Fitness 20: "You could speak it if you want to if you remember." This referred to the ability I demonstrated in a members-only extended cut to have the database "speak" text aloud, such as error messages, using the built-in speech functionality. The option is there if you want the database to talk to you.
Someone else spotted a Back to the Future reference I made, and I always enjoy when viewers notice those little nods.
I also received thanks from Easy Speak 101 for explaining workarounds for referential integrity issues. Personally, I never rely on referential integrity, especially once you split your database across multiple backend files or bring in external data sources like SQL Server. Referential integrity can break down in these scenarios, so I always prefer to handle related deletions with my own coded routines. I never let users access the tables directly, and I control all deletions through properly programmed buttons that handle child records explicitly.
Kristoff wrote in about transaction support in Access. If you are dealing with standard business transactions, there is one set of routines. If you want to use SQL transactions for things like "all-or-nothing" updates, I cover that topic in detail in Access Developer level 49. Transactions let you execute multiple related SQL statements as a group so that either all of them succeed, or none do. This is vital for things like transferring funds between accounts, where you must ensure the withdrawal and deposit both occur or neither does.
Nigel wrote to say he has been learning a lot from my Fitness series videos and finds the skills transferable to other database projects. That is exactly the point. There are plenty of database lessons and techniques covered, and you do not need to be building a fitness database specifically to benefit from the instruction.
Camerick TV asked whether Access Database Cloud is browser-based and compatible with Macs. Access Database Cloud is an excellent solution if you need to share your Access application globally without dealing with complex server setups. It works as a remote desktop that you can access through any browser on PC, Android, or Mac. I use it on my Android devices frequently. Access itself will not run natively on a Mac unless you use a Windows emulator like VirtualBox or Parallels. I am not a Mac user myself, but there is a long thread on my website with advice from others who have gone through the process.
Green Niche wondered whether to convert old Access MDB databases (pre-2000) to the newer ACCDB format. I strongly suggest you do. Make backups, of course, but ACCDB is far less prone to corruption and offers a number of technical improvements. The process is usually as simple as creating a new ACCDB file and importing all your objects.
There was a final suggestion about how I refer to TempVars. Technically, TempVars are not true variables, but in practice, you can think of them as such. Their main advantage is that they survive errors that would wipe out public variables, and Access can make them accessible from SQL as well. Sometimes I use "variable" as a shorthand term, but you are correct about the distinction.
That wraps up another Quick Queries Friday! For a complete video tutorial with step-by-step instructions on everything discussed here, you can visit my website at the link below. Live long and prosper, my friends.
Topic List
Sending email using CDO in Access
Adding a foreign key for additional contacts
Creating a self-join relationship in Access
Linking contacts to additional people via combo box
Building a query to combine main and additional contacts
Setting up left joins and self-joins in Access queries
Filtering duplicate records in Access queries
Using union queries to combine email lists
Advantages of a single People table for multiple roles
Implementing many-to-many relationships with a junction table
Explanation of SQL transactions in Access
Converting MDB files to ACCDB format
Using Access Database Cloud on Mac and browser
Access compatibility solutions for Mac users
Advantages and usage of TempVars versus variables in Access
|
