Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
 
 

Access Relationship Seminar
Just about everything there is to know about relationships in Access

 

 

In this seminar, you will learn just about everything there is to know about relationships in Microsoft Access. Setting up proper relationships between your tables is absolutely crucial to building a successful database. Bad table design, and bad relationships between those tables, is disasterous, and will cause you nothing but trouble down the line.

This seminar covers just about all of the different possible relationship scenarios that you will ever come across. We will build eight (8) different database projects so you can see how to set up good relationships between your tables.

Watch this video to learn more about this seminar:

 

Want to Learn More?

You can watch first two lessons plus the final review lesson in our Online Theater. There you will learn more about exactly what's covered. Then continue reading the course outline below if you have further questions. Of course, if you have specific questions about the seminar that aren't covered here, always feel free to contact customer service.

 

 
Seminars - Access Relationships
Description: Learn about the different ways to properly set up relationships in your Access databases.
Versions: I will use Access 2010, however the lessons are valid for all versions of Access back to 2000 with some minor cosmetic differences.
Pre-Requisites: This course is mostly for beginner to intermediate users. Basic knowledge of Access table, query, and form design is a must. You should have at least taken my Beginner Access courses before taking this seminar. A couple of the later lessons are for advanced users. There will be some VBA and a little bit of SQL used, so it would help for you to have taken my Access 300-level courses as well, but that's not necessary. I will explain what you need to know to get the databases working. Most of the course doesn't require any advanced knowledge, however.
Running Time: 3 Hours, 58 minutes
Cost: $99 - Order multiple courses to receive a discount up to 50% off

 

This seminar will teach you about all of the following types of relationships:

1. No relationships
2. One-to-Many
3. One-to-One
4. Many-to-Many
5. Self-Join One-to-Many
6. Self-Join Many-to-Many
7. Reverse Relationships
8. Multiple Relationships

We will build sample databases to track:

1. Children to Parents with their relation (one-to-many)
2. Which parents receive mail on their child's behalf
3. Student data with a secondary optional detail table (one-to-one)
4. Vendors to Products and vice versa (many-to-many)
5. Employees to Supervisors (self-join, one-to-many)
6. Complete family history with relationships (self-join, many-to-many)
7. Organizations to Members (whether companies, families, charities, etc.)
 

We will begin by looking at in table that has no relationships. You'll learn how NOT to build a database. This is basically one table with all kinds of information in it that shouldn't be there.

 

Next we'll build a database to track students and parents. This is a one-to-many relationship where one student can have many parents. We'll also keep track of which parents will receive mail for each student.

 

We will learn about one-to-one relationships. This is handy if you have a lot of data but you only have some information on most of your records. For example, if you have a student database but only have names for most of your students. You need to be able to track additional information such as address, phone number, and so on, however you only have that information for a small group of students. A 1-to-1 relationship saves a lot of space in your database in this instance. Plus you can also put sensitive information in a separate table, such as financial data.

 

Next we will learn how to create many-to-many relationships. These are quite popular but difficult to implement because they involve a third table. You need a junction table to link two other tables in a many-to-many relationship. For example ,we will learn how to track vendors and the products they sell, however each product may also be offered by multiple vendors. This is the basis for a many to many relationship.

 

Next we'll create a self-join table. This is where a table relates to itself. We'll create an employee form that allows us to choose a supervisor for each employee. However, the supervisor is just an employee, so the table relates to itself.

 

You'll also learn how to create a grouped report showing each supervisor in the employees he supervises. This requires building a query using the same table twice.

 

Next you'll create a another self-join table. We will track people and their relatives again, but this time we will set up a many-to-many relationship so each person can relate to each other person in the same table. This allows you to set up complete family trees (great for genealogy databases).

 

Next we will set up something that I like to call a reverse relationship. We'll specify two people, tell the database that one of them is (for example) the other person's uncle, and the database will figure out that the reverse relationship is niece or nephew.

This is one of the more advanced lessons in this seminar. It will use a little VBA programming and some SQL. I will show you everything you need to know to get the database working, but taking my SQL Seminar and advanced Access lessons first would be a great help. But don't worry, I'll show you how to get the database is working. Just follow along a step by step.

 

Finally will make a database to track organizations and members. Now in organization can be a family, company, charity, church, or anything else you want it to be. We'll track members of that organization in a many to many relationship. These can be family members, employees, donors, etc.

We will track multiple addresses for each organization and for each member. We will also build a contact management table to keep track of correspondence with each organization or member. We will build one big form so that we can see all of the information for each organization. We'll do the same thing for each member.

 

So as you can see we cover just about everything there is to know about relationships in Microsoft Access. From the very basic to the advanced, you will definitely know how to properly setup your tables and relationships after watching this seminar.. Of course, if you have any questions about whether or not this seminar is for you, please contact me.

NOTE: There is a NEW lesson on database table normalization found in Access Expert Level 2 that wasn't included in this seminar. It's not necessary for you to learn, but it covers a lot of theory that isn't discussed in this seminar. It was recorded after this seminar was finished.
 

 

Access Relationship Seminar - Outline

00. Intro (7:59)

01. Types of Relationships (13:04)
No Relationships
One-To-Many
One-To-One
Many-To-Many
Self-Join One-To-Many
Self-Join Many-To-Many
Reverse Relationships
Multiple Relationships

02. No Relationships (5:26)
Flat File
The wrong way to build a database
Child and Parents in Same Table

03. One-To-Many Relationships (10:45)
Delete parent data from student table
Create a RelativeTypeT
Create ParentT
Fill in sample data

04. One-To-Many Form 1 (12:38)
Create ParentF Subform
Set up relationship with subform wizard
Turn off navigation buttons
Turn off scroll bars

05. One-To-Many Form 2 (9:53)
Hide IDs
Combo Box for RelativeType
Combo Box Wizard

06. One-To-Many Query (15:34)
StudentQ for Mailings
Query Criteria
Field Name Aliases
OR Conditions
Show ALL students and related parents
LEFT Inner Join
Global Relationships
Cascade Delete

07. One-To-One Relationships (18:33)
Student
Extra Student Info
One-To-One Global Relationship
Command Button to Open 2nd Form
Show Specific Records
Forms!FormName!FieldName
Refer to Value on Another Form
Default Value
One-To-One Subform
Discuss Securing Data with Split DB
Discuss Multiple Back-End Files
One for Users, One for Admins

08. Many-To-Many 1 (11:47)
Products and Vendors
Multiple products per vendor
Multiple vendors per product
Junction table
Cross Reference Table
Vendor Form
Product Form

09. Many-To-Many 2 (14:24)
Products with Vendor Subform
Vendors with Product Subform
ProductVendorF
Product Combo Box
Vendor Combo Box
On Double Click Event
VBA Code to open a form
OnDblClick
Docmd.OpenForm
Where Criteria

10. Self Join One-To-Many (17:00)
Relating a Table to Itself
EmployeeT
SupervisorID
Supervisor Combo Box
String Concatenation
Calculated Field
Employee With Supervisor Q
Grouped Report
Sorting & Grouping Level
Report Showing Supervisor with Employees

11. Self Join Many-To-Many (20:46)
People to People with Relationships
Person table and form
Junction Table
PersonQ with Full Name
Form Subform for People and Relatives
Great for Genealogy Tracking

12. Reverse Relationships 1 (14:23)
Add opposite relationships
If Mother is picked, Child is default
Non-gender specific opposites
AllowAdditions
Do not allow additions to subform
Unbound forms
Control Source
Unbound Combo Boxes
Turn off Navigation Buttons
Record Selectors
Scroll Bars
Modal
Popup
Docmd.Openform
Set value across forms
Forms!FormName!FieldName

13. Reverse Relationships 2 (22:44)
AfterUpdate Event
DLOOKUP Function to find Opposite
SQL
INSERT INTO
Append Query
IsNull
MsgBox
Exit Sub
Docmd.Close
Refresh
DoCmd.SetWarnings

14. Multiple Relationships 1 (14:58)
Main Organization Table
Members Subtable
Great for Charities, Churches
Fund Raising possibilities
Good to track Family Data
Multiple subforms of data
One master form
OrgT for organization
Junction Table
Member Subform

15. Multiple Relationships 2 (21:56)
Address subform on Org Form
Address is for Org OR Member
Address subform on Member Form
OnDblClick to jump from Org to Member
Vice Versa
Contact History Subform
Contacts for both Orgs and Members

16. Review (5:43)
 

 


 

 
 

Student Interaction: Access Relationship Seminar

Richard on 6/20/2012:  In this seminar, you will learn just about everything there is to know about relationships in Microsoft Access. Setting up proper relationships between your tables is absolutely crucial to building a successful database. Bad table design, and bad relationships between those tables, is disasterous, and will cause you nothing but trouble down the line. This seminar covers just about all of the different possible relationship scenarios that you will ever come across. We will build eight (8) different database projects so you can see how to set up good relationships between your tables. This seminar will teach you about all of the following types of relationships: 1. No relationships 2. One-to-Many 3. One-to-One 4. Many-to-Many 5. Self-Join One-to-Many 6. Self-Join Many-to-Many 7. Reverse Relationships 8. Multiple Relationships We will build sample databases to track: 1. Children to Parents with their relation (one-to-many) 2. Which parents receive mail on their child's behalf 3. Student data with a secondary optional detail table (one-to-one) 4. Vendors to Products and vice versa (many-to-many) 5. Employees to Supervisors (self-join, one-to-many) 6. Complete family history with relationships (self-join, many-to-many) 7. Organizations to Members (whether companies, families, charities, etc.) You can learn more about this seminar here: Access Relationship Seminar Of course if you have any questions, post them here!
Tracy on 6/28/2012: Great seminars. Let's see more
will powers on 6/29/2012: is there a way besides going into the relative typeT table like in the video to make the opposite ID? I want to make a database for our parade in town. the idea would be to fill up a float with people and show floats that aren't full yet and floats that are full. then use the reverse to show what people went on what float in the past.

- Will

Reply from Richard Rost:

Well, the floats that are full/not full would just be a simple DCOUNT (or a form footer total) of the number of child records.

Making a query to show who was on which float in the past should be a simple matter too.

Jim Upton on 7/6/2012: Hi Richard

Readlly enjoy this relationship seminar. I will need to watch it again but thats me at being slow getting my head around it.

I would be interested in expantion on any of those comments that you made and adding payments into this and making reports as a address book

Reply from Richard Rost:

Thanks, Jim.

 Chris on 7/11/2012: Not to belabour this and just to ensure I understand. On the cascade deletes, if another record uses the same cascaded data, the common records are not deleted. It is only when there is one remaining record that uses this cascaded record that it is removed from the database. Is this correct? (ref: 15:00)

Reply from Richard Rost:

Cascade deletes come in handy if you delete a PARENT and want all of its CHILD records to be automatically deleted. I don't know what you mean about "common records." Can you explain with an example?

Chris on 7/11/2012: My earlier question was asking: When does the data in a cascading table get deleted when there are several other records pointing to the same cascading record?

As an example - Three people (three records) in PersonT each refer to the same address data located within table AddressT. There exists a global relationship between these two tables using Foreign Key AddressID in the PersonT table and the Primary Key (AddressID) in the AddressT table. In each of the PersonT records, they each refer to the same address record with AddressT.

Will the AddressT record remain in the AddressT table until all three records have been deleted from PersonT?



(as a side note - yes I could try this; but I was asking so that others could also get an answer to this question)

Richard R on 7/12/2012: Chris that's a very good question. See this tip video for my reply. :)
 Bruce on 7/16/2012: Video 13 covers a lot of ground. I am grateful that I have had the opportunity of viewing this critical seminar.

Reply from Richard Rost:

Glad you're enjoying it.

Kevin Robertson on 7/16/2012: Hi Richard,

Completely loved this seminar - learned lots I didn't know. Keep up the good work!

Reply from Richard Rost:

Thanks, Kevin.

  on 7/17/2012: Brilliant AGAIN, Rick.
Couldn't honestly say anything was unknown to me but the real value in these seminars is that you show how to bring it all together.
Actually, I just realised that the one-to-one was a new idea from the point of view of security.
Well done, Rick, keep 'em coming to feed my Access adiction :o)
Regards
Chris

Reply from Richard Rost:

Ha ha. Thanks, Chris. I'm working on more crack for you right now. :)

Tom on 10/12/2012: Richard- I would like to use my choice in a combo box to populate another field without user intervention. The combo box pulls from a table that contains catalog items. Each individual item also contains a number that corresponds with a product type (stored in a product type table) Any thoughts?

Reply from Richard Rost:

Use an AfterUpdate event?

Roderick Barbour on 12/2/2012: I have been questioning the concept that Richard has of making so many copies of each database as we went through the lessons, until I just hit a key or a combination of keys and just lost my whole database. Im an old IT guy and there was always a way of getting a file or document back. But with Access, once its gone it is really gone. WOW!! Great lesson.

Reply from Richard Rost:

There is a method to my madness. :)

Kamal Benlitifah on 2/17/2013: Thanks - Kamal
Jennifer on 2/25/2013: I have asked this somewhere else: what if you have a lot of info about a single student / patient. Eg a unique pt ID but with up to 15 differing tables containing results or clinical findings. Each of these 15 is I think? a 1 to 1 relationships, but there are 15 of them??? What do you call this and how does it work.
Anonymous on 2/26/2013: I set up a table with a unique pt number which was indexed as no duplicates and made it the Primary key. I used the unique number in a 2nd form and made it the primary key, no duplicates, and was told I had broken the integrity rules as this duplicated values?? Relationships lesson 7 time 0259

Reply from Richard Rost:

Don't make it a primary key in your second table.

PETER on 4/23/2013: At minute 2:30 on the video the subform automatically shows only subform contents that match the VendorID on the parent form. That filtering doesn't seem to be occurring when I try to do it on one of my own subforms ... Is there something I missed on the video?

Reply from Richard Rost:

Access should have automatically set up a relationship between your main form and sub form. It's the LINK MASTER FIELD and LINK CHILD FIELD properties in the subform.

PETER on 4/23/2013: Please see my last comment - I just realized that rather than have Access build the parent form, I had built it myself using Form Design (as suggested by you in many other video's). When I let Access build the parent form for me, the subform behaved as expected - only showing subform values relevant to the item shown on the parent. What would I have done wrong in building the parent form from scratch compared to what Access does itself?

Reply from Richard Rost:

You can build the parent form from scratch. Just make sure you set up the link fields properties. Access SHOULD do that for you if your fields are named correctly (the same) on each of the forms.

David Robinson on 5/3/2013: Richard
All I can say your the Best, your courses and the best I have ever seen, easy to follow and you explain everything, I just need more time to get to all the courses I havew purchased and I will be purchasing more.


Thank you very much

Reply from Richard Rost:

Thanks, David. :)

Mark Bee on 5/4/2013: I am having problems with understanding how to have one address table for my customers, that relate to the customers jobs, their invoices and quotes etc... I can have 1 customers with many jobs related to one address, then 1 customer with many address and many jobs. I've been unable this far to work out how to get this to work with out running into duplicating the same address in the address table.

I've tried to make work a junction table to work between the customers, jobs, address, quotes and invoices but end up confused. Even going through this relationship seminar twice I'm unable to see how the examples relate to what I'm trying to achieve.

Can anyone help?

Reply from Richard Rost:

I would have 3 tables: customers, addresses, jobs.

AddressT has CustomerID as a foreign key
JobT also has CustomerID as a foreign key

Now, assuming a JOB will have ONE AND ONLY ONE address, you could just have one AddressID field as a foreign key field in your JobT table.

If a job can have MULTIPLE addresses, then you need to set up a FOURTH table as a junction table which would look like:

AddressesForJobsT
ID: AutoNumber
JobID: Foreign Key to JobT
AddressID: Foreign Key to AddressT
Details...

Mark on 5/7/2013: Hi Richard, Thanks for your quick reply.

A JOB will have ONE AND ONLY ONE address. So I have one AddressID field in my JobT table.

I have a JobAddressSubF for showing and inputing new addresses in the JobF. The Sub form picks up the JobID ok, but I haven't found a way to get the AddressID from the subform to update onto the JobF.

I've tried a combo box on the main form using:

Forms!JobAddressSubF.Form!AddressID

To get it into the main form but haven't got it to work.

So I'm not sure where I'm going wrong here? Or should I scrap trying to get the AddressID over and just work with the JobID as this automatically comes up in the JobAddressSubF.

The other problem I'm having is when the Job address is the same as the customers home address. I have an "AsHomeAddress" option button, which when selected, I need the JobAddressSubF to automatically update to their home address and save the relationship with the JobT/AddressT.

I'm more than happy to have a paid session to get this resolved if needed. But I'm hoping it's something simple that I'm just not seeing yet?

Please can you help?

Reply from Richard Rost:

You should have 3 separate forms: CustomerF, AddressF, JobF. You would select an address from a list of addresses ON THE JOB FORM. You can use them as subforms if you want, but be sure to REQUERY the list if the addresses are changed otherwise they won't show up in the combo on the job form. Or just use separate forms and you won't have that problem. Enter the addresses on the address form. Close it. Open the Job form and pick it.

I wouldn't even bother storing a separate home address in the customer table. Put everything in the address table and just use your "AsHomeAddress" checkbox to indicate it.

Adam Wray on 7/16/2013: Wondering how to change between subdata sheets that are shown when you click 'plus' if you have more than one one-to-many relationship. Also, I deleted one relationship, but still can't access the one I'm looking for.

Reply from Richard Rost:

Go into design mode, table properties, subdatasheet name.

Jim Upton on 7/30/2013: Hi Richard

I have enjoy this seminar. I understand the one to many relationship. On the products and vendors many to many if you then want to add orders for reselling would this go in the Junction Table as well?

Reply from Richard Rost:

What do you mean by "orders for reselling?"

You would but information in the junction table specifically related to THIS PRODUCT FOR THIS VENDOR.

Jim Upton on 8/1/2013: Thank you for your reply. I been having trouble getting my head around a relationship problem. In replying and explaining I have cleared up my dilemma.

Did you know if your in a table and you want to repeat the same data as the line above Ctrl key + @key repeats it for you Many Thanks

Jim Upton

Reply from Richard Rost:

I did not know that. Thank you for sharing. There are a MILLION shortcut keys in the Office apps, and to try and memorize them all, one would go insane. :)

William B on 8/28/2013: Can you do reverse relationships in a form and subform, as you have done in this seminar? I was just wondering if it worked the same way, or if there would be some different SQL or VBA required for it to run correctly when that specific form is open?

Love the seminars by the way!

Reply from Richard Rost:

I don't see why not. It may be tricky though. A reverse relationship is really just a fancy one-to-one relationship, so you wouldn't need a subform.

William B on 9/3/2013: I tried to delete a record (after using the self join many to many and reverse lookup relationships), and I get an error saying the record cannot be deleted because it is related to one or more tables. Is there a way to do this without the user having access to the person with relationships tables?

Thanks a lot. Really have learned a lot from the seminars.

William

Reply from Richard Rost:

If you have referential integrity enforced then you can't delete that record if it has related records. This is a FEATURE of referential integrity. You can either turn it off or force the user to delete the related records first.

Martie Jacobs on 9/5/2013: Reports:
1. I have added a field which displays in the print view of the report only if there is a value in that field - which is exactly what I want, but I don't know how this happened and therefore cannot do the same with the other fields.
2. Format: Make whole word Capitals - cannot find this setting.
Plse help!
Many thanks. Martie

Reply from Richard Rost:

1. I'd need to see your report to tell you for sure. Look for some programming in the BUILD EVENT for the SECTION. Something like:

If IsNull(MyField) Then MyField.Visible = FALSE

2. UCASE(MyField)



Ronald Pero on 9/5/2013: I have created a subform where I will enter customer name & info as part of a main appointment form. My customer table seems to work fine if I manually create a new customer but when I try to create a new customer by entering data into the fields in the subform first I get the error message: You tried to assign the Null value to a variable that is not a variant data type. which seems to be related to the autonumber field in the customer table and then no matter what I do the form wants to move on to the
next new customer and nothing gets saved in the customerID field in the appointment table which contains
the appointments. When I open the customer table the new customer is there and the autonumber field works fine but I can't figure out why it will not enter the customerid into the appointment table. Do you have any suggestions?

Reply from Richard Rost:

You have to have data in the parent form before you can add any data to the subform.

Ronald Pero on 9/5/2013: Please disregard my questions from earlier today. I realized that the first problems I had was that I had accidentally deleted the customerid field on my subform. My second question was wondering why the customerid
field was not saving in the parent form and thus the appointment table but I now can understand that I was trying to make the form/subform relationship work backwards which apparently does not work.

I do still need to figure out how to make this work. So what I need to have happen is to be able to enter the
customer data into the subform and then somehow update the customerid field back in paren form. Based on the reverse
relationship video it looks like I can use the doCmd.RunSQL "Insert Into" command to insert this field back into the
appointment table. Is this possible? and if so, how can I choose a specific record in which to insert the field value? In other words is there any kind of where condition that can be specified when using the Insert Into command?

Reply from Richard Rost:

You really can't do this backwards. You could develop a different form where you can enter in both sets of data and then use some code to populate both, but you can't enter data into a subform until the parent has data.

Julie Martin-Beaulieu on 9/22/2013: I work for a school and all of my student records are associated with their student state ID. It seems to me that I would want to have the releationahip between tables exist with that ID not the key ID. Does that sound reasonable? Or will I create prolems by not using the primary key/auto number?

Reply from Richard Rost:

As I've said in my videos many times, I would still use an Autonumber ID for the relationships in the database. If you want to have that Student State ID saved in the student's record, that's perfectly fine too. You can search or sort on it whenever you want. Autonumbers are just much better and easier for relationships. Nothing wrong with having two fields.

Julie Martin-Beaulieu on 9/22/2013: Hi Richard,

First of all, I need to tell you that I find these seminars and your tutorials to be very helpful. I am understanding the different components and purposes for creating a database in a way that makes sense to me. So thank you.

Regarding relationships and when to use them; I am creating a database to store information on my students. I think I want one database with three different tables. One for students who are in the "early intervention" process, those who are receiving "interventions" and those who are identified as "special education". Within each table I am documenting meetings, student progress, and history of support services provided. Would this be one database with all of this information stored in it, or should I actually build three databases? I want to be able to see which students went through our early intervention process, who ended up qualifying for Special Ed and what interventions they went through. So I think it would be all in one. Is that right?

Thanks,
Julie

Reply from Richard Rost:

I like to keep all of one THING in the same table. So you should have a STUDENTS table (StudentT). In that table, you can differentiate between "early intervention," "intervention," and "special ed" with different classifications (a single field). No need to put these students in three different tables.

Meetings, progress, history of support services provided... these all go in different tables... just like how I break out CONTACT HISTORY from CUSTOMERS in my Access Expert series.

You don't need three different databases. The only way you'd need to make separate database files is you're reaching the 2 GB limit on database size (which I doubt). You may also want different databases if different USERS need to have access to the different student info and you don't want to go through setting up security in the database itself. You could make 3 different database files with different passwords.


Vannak Hou on 1/25/2014: Why can't I use an alisa ParentName:[LastName]&","&[FirstName] for the ParentT (get rid of the FirstName, LastName of ParentT's Column)? I tried but I'm getting a message "The specified '[FirstName]' could refere to more than one table listed in the FROM clause of your SQL statement; even though I selected StudentT in the Table. All I am trying to do is Concatenate the fields together as one. This on 03:54 on the video timer. My database built a bit different using City, State, ZipCode as a separate field. But it works!
Vannak Hou on 1/25/2014: Lol, Richard (timer 0710-0712), funny, I remembered back those days, then when Memory was too costly. I think that time Toshiba or other company in Japan was ruin due to an earth quake. Yep!
Vannak Hou on 1/26/2014: Is there a way to go back to the Combo Box to "resize" the field such as the LastName or the FirstName? Lets say I made the LastName's field size a bit too small after I input data.
vannak on 1/28/2014: 04:23-04:31. can you later adjust the length of the column at a later time once in the Form Menu?
Caryn M on 4/13/2014: Richard,
I have followed your steps in video 11 to create a many to many relationship for a genealogy database. When I open the first individual, I can add relations for that person. When I move to the next individual, the relations don't update for the new person. If I look in my junction table, the individualID is 0 but the relativeID is populated. I've gone through the steps multiple times and I'm not sure why it keeps doing this. Do you have any ideas? Thanks
Caryn

Reply from Richard Rost:

I'd really have to see your database to tell you what's wrong, but if your ID is showing up as 0 that means it's not getting the default value properly.

Caryn mellom on 4/20/2014: I was able to figure it out. For some reason the forms were pointing to the wrong tables.
Wilma W on 5/3/2014: I am thinking that with this way of adding relatives, that if a family has three kids the phone number and whatever other data I choose to record for the parent table would have to be reentered for each child. Am I thinking correctly?
Jim U on 5/14/2014: I have been reading the threads I cannot see anyone with the same difficulty I keep ending up with an indeterminate relationship. I am not sure how or why?
Falakiko Funaki on 6/3/2014: Hi Richard, I enjoy watching your videos and have learned a lot. I am studying at the Auckland University of Technology in New Zealand. I have a project to build an access database with some tables Staff, papers they teach, software packages they use for teaching and also access device for the whether if they login from home or from the University. They are four tables and they all many to many relationship. I have a problem relating them to each other. Can you help me out please? It would be very much appreciated. Thank you

Reply from Richard Rost:

It's impossible for me to tell you what's wrong with your database without seeing it. I cover this stuff in depth in my RELATIONSHIP SEMINAR, or you can send it to one of my TECHS to look at.

Ramona Woitas on 6/23/2014: What would be the best way to setup a personal book library. Would I use junction tables or have tables for author, book, series with relationships

I only want the following information:
Author:
- Name, Alias, Website, Notes

Books:
- Title, Release Date, Finished, Notes
- Novel or Series
- Book # (1 of 1 or 1 of 3)
- Total books in series (3)
- Date Read - Notes

Reply from Richard Rost:

I'd have BookT and AuthorT as the main tables.

You wouldn't need a junction table for these two UNLESS you want to be able to put multiple authors (many-to-many) on a single book (which does happen). For just one author, you'd need an AuthorID field in your BookT (one-to-many).

SeriesT could be another table and you'd need a SeriesID in your BookT table. Here you wouldn't need a junction table unless a book can be part of multiple series - which I don't think ever happens.

Total books in series would be a COUNT() or DCOUNT()

Date read? If you want to track when YOU read a particular book, you could just put this in your book table. If you want to track multiple people reading it, you'd need a UserT (or ReaderT) and then - you guessed it - a junction table.


Ramona W on 6/29/2014: Hi Rick! I have another question and unfortunately not as educated as Chris, so I am scared to mess with my DB too much.

With regards to Lesson 14 & 15 of seminar - Org & Members. I deleted a member from the memberF, which also did the deletion to the memberT. However on the OrgXMemberSubF (now shows a blank space), the MemberXOrgSubF (still shows the org), and the OrgXMemberT (Still contains all of the data).

I have already tried several things, none to which have worked. I went to add DB relationships for the OrgT, MemberT, and the OrgXMemberT - but apparently have not picked the right choices and I am now really confused.

I also built a DB for my books - AuthorT, BookT, SeriesT, and BookXAuthorT - Unfortunately the same thing happened. (Of course an AUTHOR can have more than 1 book & create more than 1 series - A BOOK can have more than 1 author & it can belong to more than 1 series - A SERIES can have more than 1 author & has more than 1 book - Which is why I used a JunctionT)

ie) Series A, has Book 1, 2, 3
ie) Series B, has book 4, 5, 6 which are also the same books as in Series A, Book 1, 2, 3.

Can you please help? Thank you in advance!

P.S. I really loved this seminar especially lesson 14 & 15. I love that you can have multiple relationships and these sub forms that shows you the information pertaining to only that person. I ABSOLUTELY LOVE IT!

IE) Have PersonF (with specific data like DOB) and then sub forms that show the organizations, Other groups, Address, Phone numbers, Supervisors, Contact Notes etc. Then Have OrgF (with specific website and email) and then sub forms showing the people, Address, Phone numbers, and contact notes!!!


Reply from Richard Rost:

Did you have referential integrity set up?

Ramona W on 6/30/2014: No I didn't. You didn't do it in the seminar so I thought the junction tables kind of did it for you.

The reason I thought this is because when I went to add ref integrity, there was something already in there that I never put there, and have no idea where it came from.

ie) 3 tables - MSysNavPane
a-Group Categories
b-Group
c-Group to objects

Anyway, apparently I should have added referential integrity. So would it be:
OrgT, MemberT, OrgXMemberT

OrgT (left join option 2) to OrgXMemberT - Referential Integrity - Cascade Delete?

AND

MemberT (left join option 2) to OrgXMemberT - Referential Integrity - Cascade Delete?

I wouldn't have to worry about address and contact tables would I?


Reply from Richard Rost:

You would need referential integrity with cascade deletes set up if you want to delete the junction table record when a member record (or an org record) is deleted.


Ramona Woitas on 7/6/2014: Ok I give up! I am wasting my weekends trying to figure this out and I can't seem to make it work.

I have tried various ways to add ref integrity to my database and I got some of it working but then other parts didn't.

I am trying to set up a database just like this lesson with OrgF having Members, address, contact notes, phone number sub forms. Same idea for MemberF.

I had it working so that if I deleted a member or org the junctionT information deleted as well. However the address and contact note information was not deleted.

Then I found the video where chris was asking about cascade deletes where 3 people had the same address and tried to incorporate that too. When I tried to add an org, I got a message come up regarding integrity.

Obviously I am seriously screwing up the relationships in my db and I need to know how to do it right! I have:
- OrgT (OrgID, AddressID, PhoneID, ContactID)
- OrgXPersonT (ID, OrgID, PersonID)
- PersonT (PersonID, AddressID, ContactID, PhoneID)
- AddressT (AddressID, OrgID, PersonID)
- ContactT (ContactID, OrgID, PersonID)
- PhoneT (PhoneID, OrgID, PersonID)

I tried to do left joins - type 2 for the OrgT, PersonT to the junctionT and it worked.

But when I tried to follow the video for address, phone, and contact notes, I couldn't add the address etc information to OrgF. I had left joins for AddressT, PhoneT, ContactT to OrgT and PersonT with joint type 1.

Can you please tell me what my relationships should like and what join type to use for each as well?

Hilton Turner on 7/11/2014: When making a "Self-Join Many-to-Many junction table, four ID's are created (see video at 4:40 thru 5:06). The junction table "ID", the "PersonID", the "RelativeID" and the "RelativeTypeID". The junction table "ID" identifies elements within the junction table. The "PersonID" identifies elements within the Person table. The "RelativeTypeID" identifies elements within the RelativeType table. The "RelativeID" does not identify elements in a separate table; it identifies elements in the Person table. Therefore, both the "PersonID" and the "RelativeID" identifies elements in the Person table. I can understand How the "PersonID" is related to the Person table - there is a common name, but I do not understand how the "RelativeID" is related to the Person table - the names are not common. What magical SQL dust causes the "RelativeID" to identify elements only in the Person table and not the RelativeType table???
Mark Gray on 7/12/2014: Richard I could not stand Access before this you have turned my opinion completely opposite I now cannot stay away from it! Thanks, you are a true inspiration!

Mark

Reply from Richard Rost:

Thanks

Mark Gray on 7/15/2014: Please setup that video on how to fix a flat database that would be VERY helpful!

Reply from Richard Rost:

Mark, I covered that in Access Expert 14.

Ramona W on 7/15/2014: Can someone please tell me what the relationships should be regarding the tables noted in my original post. I would be extremely grateful!
Julie Martin-Beaulieu on 7/18/2014: Hi. I am making a database to store interventions and student support services for students. I think I need a one to many table so that I can identify the type of interventions (there are different reading and math programs) each student receives. Some students will receive multiple interventions. My question is: Do I need an intervention ID like you have a parent one? I wouldn't have a unique intervention for each student. I would imagine identifying either reading or math and then having the intervention type be the "many" part to the relationship. I'm just not sure if I need a unique ID for that table. Thanks for your help.
Julie Martin-Beaulieu on 7/19/2014: Hi. I am dropping the junction table into my "parent form" but it continues to show all of the data. What might I be doing wrong? I am following your video step by step so I'm not sure what the problem is.

Thank you for your help.

Julie Martin-Beaulieu on 7/19/2014: You can disregard my last question. I just read another student's comments about not using the design form and now I'm back on track. Thanks!
Christophe Smit on 10/16/2014: I'm trying to set up the relationships for a procycling database on one hand and a soccer table on the other hand; as an excercice. In the procycling DB a rider can switch teams (team x season 2014, team y season 2015). Teams can exsist in season 2014 but not anymore in season 2015.
In soccer DB it is more complex as a player can change teams during the same season. I tried different ways but without result. How should my relationships look like in both DB's?

Reply from Alexander Hedley:

Hi Christophe,
If we convert this to the lessons Rich has taught you could have an Employee and a Company.
The Employee could have a CompanyID that is a Foreign Key to the CompanyT.
You could then have a combo that lists all the Companies and choose one for the Employee.
You could then change this if the Employee was to move Companies.

To show only certain Companies you could use a WHERE clause and filter it given some criteria.

Frank Fish on 10/27/2014: Hi Richard; index 10:55 (approx)although I have 3 records in the StudentF; when I move to StudentID 2 or 3 and then select the "Show Extra Student Info" button I get "#Error" in the StudentID box on the StudentInfoF. (I'm using Access 2013) however; if I place information in the StudentInfoT for the second or third student the information shows up when I press the button. is this just the difference in Access versions or did I miss a step?

Reply from Richard Rost:

This should work regardless of what Access version you have. Have you tried closing and reopening the form?

Frank Fish on 11/28/2014: As with all of the courses I've taken so far Richard seminar is very helpful in rounding out my knowledge base. My comment/request is: Can you add a feature to your classes that allows for a "bookmark" or place keeper? I sometimes have to interrupt my studies and it might be a day or so before I am able to return, a place keeper would be helpful. Thanks. FWFII

Reply from Alex Hedley:

Frank in the bottom left pane in the Theatre there is a Settings section, this has a Last value which saves your last video viewed.

Ramona Woitas on 3/7/2015: Hi Richard! I am trying to get my head around this again. lol.

Relationship Seminar, Lesson 15, at 6:22 you said you have members which is a many to many relationship. I take this to mean that multiple members can belong to multiple organizations. Right?

Then you said that addresses is a one to many relationship, from either an organization or a member. This is where I am confused. A member can have multiple addresses, and an org can have multiple addresses. But the address is only listed in the table once. And the address can only be used by either the member "OR" the organization. Is this right? Do I finally get it?

Reply from Alex Hedley:

Maybe this MS article with the following example may help:

One-to-many relationships

A one-to-many relationship is the most common kind of relationship. In this kind of relationship, a row in table A can have many matching rows in table B. But a row in table B can have only one matching row in table A. For example, the "Publishers" and "Titles" tables have a one-to-many relationship. That is, each publisher produces many titles. But each title comes from only one publisher.

Many-to-many relationships

In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table that is called a junction table. The primary key of the junction table consists of the foreign keys from both table A and table B. For example, the "Authors" table and the "Titles" table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the "TitleAuthors" table.

Carolyn Cwik on 5/27/2015: I understand the Relative Subform and was able to do it to a tee. However, it would be nice to add a relative "on the fly" but am having problems with trying to do so. Is it because of the combining of last name and first name. I have gone over and over the on not in list event but haven't seem to get it to work. Any help would be appreciated. Thanks. Carolyn

Reply from Alex Hedley:

What errors are you getting?

Fernando Solano on 7/21/2015: I'm really enjoying this Access course, it's been over 10 years since i worked with Access, and your lessons are very enjoyable to get myself familiar with this!! i'll will be buying more lessons soon!

Reply from Alex Hedley:

Great to hear it :)

Emad Al-Baghli on 11/21/2015: Hello, Richard, Why did you put Price in Junction table? Price is related to the product, so why do you desice to put it in Junction table instead of Product table?

Reply from Alex Hedley:

This will allow you to add different prices for the same product.

Daniel S on 6/6/2016: Hi Richard, I really have enjoyed everyone of your courses. Thanks for doing such a good job explaining a difficult subject. I am in the relationship seminar having trouble with moving things around on the form. Such as resizing, and relocating the ID fields. I am in design view but it acts like I'm in Layout. I am using Access 2016.
Thanks

Reply from Alex Hedley:

Form Design
Arrange
Control Layout - Remove

Access groups the controls if you made the form automatically.

Matthew Pattison on 6/7/2016: Question for example 3 btn mouse $8

the 45 days later the price changes to $12

how do you handle that without messing up the previous orders?
lesson 8

Reply from Alex Hedley:

You'd need to have a price against an item in the OrderT.
I'm sure this is covered, or it is in another course.
You pick a product from a dropdown, and you copy the values from the combo.
productCombo.Column(1) to the Order Field.
This can then be updated if necessary and is independent to the ProductT.

(Access 302)

Richard Wilson on 6/16/2016: At 5:11 you talk about using a DLookUp for a specific type of address or phone number. I need to know how to do that. I built my original forms based upon tables with (for example) OfficePhone, CellPhone etc as separate fields. I have now gone back and put all the phone numbers in one table with a TypeT containing a TypeID and such things as Office, Cell, Fax, Home etc. Same sort of thing for email, address, Title (this is a DB for teachers in a school where one may be on two or more faculties and be Professor in one and Artist in Residence on another). Anyway, my DLookup for such things as phone numbers no longer works. I need to specify which is Office and which is Cell, etc. I have tried to use a Where condition using an "AND" clause where an ID = Combo ID AND TypeID = 1 (I know I have not done the right syntax here) but I get a "mismatch" error in the second part of the AND clause. I do not understand that since TypeID is a number and "1" certainly is an number. I do NOT put "" around 1 but Access does. Long question, I know, but it is one I am wrestling with right now. Further, since this deals with teachers in schools, I also need to narrow the result to a specific school where the student is enrolled. That may be beyond the scope of this class, but it is my problem. Especially since I have gone back and redone my tables to make phone numbers (for the teachers, at least: thinking about doing it for the Students/contestants as well as the people in the town where the competition is held!!; same for addresses, emails, etc). Anyway, I need to do what you said at 5:11. Excellent Seminar. I have listened to it many times. It takes repeated hearing for me to learn it. But after two years, I am beginning to grasp some of it!!

Reply from Alex Hedley:

Why not just create a Query that uses the CustomerID to filter on (with the others) and have it as SubForm.

Richard W on 6/19/2016: The information is already in a subform. Rick said it is possible to put a subform in a subform but is a problem. Further I was trying to learn DLookup since he talks about its power. But if I have to use a query and a sub-subform, I guess I need to know where I can learn to do that! Thanks for the answer. I am trying to use the DLookup approach since I have, in fact, three groups of people with many to many relationships and I am trying to standardize the way the information on all of them is stored . . . following Rick's advice that "if you have a second telephone number (or email or whatever) you should put it in a separate table" (paraphrased, not quoted).

Reply from Alex Hedley:

Subform
Tip

Jack Tracy on 7/26/2016: Thanks for this lesson, it is really helpful when it works. I'm experience problems with it continuing to work, however.

I've set up this code exactly as described and it works great. Then I save, close and reopen the next day and attempting to run again I get bug errors. I redo the entire process in a brand new set of forms, and again works great. Next day, bug (error 459, pointing to the default input of the "personID" from the underlying form for the reverse relationship), which then leads to a bug in looking up the opposite ID as well.

I'm not sure what the problem is, but I'm not making any edits to the forms or code in question, it simply seems to just stop working over time and I'm not sure why.

Best,
Jack

Reply from Alex Hedley:

If you compile the code in the VBA editor do you get any errors?
Might be worth a compact and repair.
Have you tried deleting and recreating the form?

Kris Cunningham on 10/27/2016: Hello. I am on Lesson 15 of this seminar and I think I finally (after several weeks of off and on studying/questioning) understand how I need to create my database. I do want to make sure I understand, though: I read that many to many relationships are just two one to many relationships joined by a junction table, but that isn t quite right, is it? For example, a one to many relationship would be A can have zero to many of B, but B could only have zero to one of A, correct? Like I read a street can have zero to many houses, but a house belongs to (well, not zero but) only one street. But a many to many says, A can have zero to many of B AND B can have zero to many of A, correct? Like a room can be booked by zero to many guest, and a guest can book zero to many rooms. So, in that way, it s that quite the same, correct? Am I on the right track?

Reply from Alex Hedley:

Sounds good to me.

Kris Cunningham on 10/27/2016: **CORRECTION TO MY QUESTION** I ended by saying, 'So, in that way, it's that quite the same, correct?'. What I meant to say was, 'So, in that way, it's NOT quite the same, correct?'
Kris Cunningham on 10/31/2016: I need to make sure I understand, please: ~5.35 in Access Relationship Seminar Lesson 15 - So, you had both OrgID and MemberID fields in Address table b/c you knew ahead of time you were going to need them on *separate* occasions, e.g., the OrgID to be the Child Link in the AddressSubF to the OrgID in the OrgF and then the MemberID to the be Child Link in the AddressSubF to the MemberID in the MemberF for when you did an example of putting subforms into the MemberF, is that right? B/c when you started adding those 2 ID fields to the Address table, I immediately thought you were making it a junction table in order to have a many to many relationship with the Org AND the Member at the same time, but you said at ~20:43 that you were setting up a one to many relationship between the AddressSubF and the OrgF and then the AddressSubF and the MemberF separately, right? Also, at first, I didn t see why you could not add an AddressID to the OrgXMemberJT (the junction table) and make the Address a many to many between the Org AND the Member b/c theoretically that could happen. In other words, I wanted to filter the addresses to just show those shared by both the Org AND the member, but when I tested it and added an Address combo box to the OrgXMemberSubF, next to the MemberID combo box, the values of the address did not match the members, only the organization. 1) I m getting so confused now, so why did the Address combo box make a relationship with the Org and not the Members in the combo box next to it? All of the ID fields, OrgID, MemberID, and AddressID are in the junction table, OrgXMemberJT. (I based the Address combo on the Address table, pulled over the AddressID and Street fields and stored the value in the AddressID.) 2) To filter like I m suggesting, would there need to be criteria in a query that would be the basis for both combo box fields, MemberID and AddressID? If so, would you just say in the Member ID criteria =AddressID and in the AddressID field criteria under the Or row put = MemberID? I don t think you would want to do this but just for argument s sake. Thank you, again.

Reply from Alex Hedley:

Maybe writing down the values you want to see on paper would be a good way to visualise, then transfer it to the db once you have a grasp.

Sometimes it's hard to look at a table of just numbers, 1 option is to create a Query that joins the Tables to their respective IDs then pull in a Name Field to see what you have.

From that you can take the row you would expect to see and see what filters you would need to apply.
Remember AND across OR down, it's likely you'd want AND across if you are wanting to filter for specific Orgs etc.


Kris Cunningham on 10/31/2016: . In the beginning of the Lesson 16 Review you said, to mention if you would like to see a seminar on how to fix a flat file and build relationships. I would like to see it, especially how you normalize various examples of tables. And would like to see even a mini seminar or however long it would take with more examples of normalization other than what you showed in an earlier lesson. I m having trouble translating the examples I see with how to normalize my data, which pertains to the telecommunications industry. I'll add this suggestion to the survey. Thank you!

Reply from Alex Hedley:

Expert 22 briefly covers this.

Kris Cunningham on 10/31/2016: One other question please: When creating the combo box for the MemberID in the subform - it was based on the Member query and I noticed the dialog box that helps you add the combo box asked 'Choose a field that uniquely IDs the row. Which column in your combo box contains the value you want to store or use in your database?' Then the next screen asks, 'When you select a value in your combo box, what do you want MS Access to do?' And you chose, 'Store that value in this field: Member ID'. Aren't those 2 questions asking the same thing? If not, can you describe in detail what the difference is? Sorry. Thank you, again!

Reply from Alex Hedley:

Say you have a Query that brings in Tables that contain the same value in a Field, i.e. name you wouldn't want to store that as your ID field as how would Access know which Record to choose if it is a duplicate, you need to pick a unique value like an ID or a composite key.
Once you have that you still have a choice as to whether to store that value in your Table. You could choose another.

Kris Cunningham on 11/10/2016: I was starting to normalize my tables and think about the relationships among them. I have a Node table and a DesignStatus (DS) table that I know is a one to many from the DS table to the Node table but am wondering, just out of curiosity, if it is a one to one from the Node table to the DS table.

Someone told me I m defining the relationship incorrectly b/c I ask myself if a node has only one or one to many DS records versus asking myself if each node has only one or one to many DS records. I don t understand the difference, but I see that it is important b/c his way means that each node (many) can only have one DS (one), which changes the relationship from Node table to DS table to a many to one relationship. 1) Which is the right relationship, one to one or one to many from the Node tbl to the DS tbl, and 2) which is the right question to ask yourself when trying to define a relationship?

P.S. A node is used to deliver internet service to a neighborhood and I want to record a node s status at any one time only, so each node can be of one status only. It will start out Not Complete and then change to Complete as the project moves forward, which I will change using a combo box, but I will not be creating multiple DS records for that node.

Norma Killian on 3/13/2017: If I have a client that has multiple projects and each project can have multiple employees with different roles and status how would that be setup if I assign the original project and need to populate the information on one form and the employee(s) complete their portion on another form?

Reply from Alex Hedley:

So are you wanting tasks within a Project assigned to an Employee.

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP