Access 2007-2016
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  

< Previous: Access Expert 6

Next: Access Expert 8 >

Access Expert Level 7

Expert Microsoft Access Tutorial - 1 Hour, 37 Minutes
This Microsoft Access video tutorial picks up where Expert Level 6 left off. This class focuses on Many-to-Many Relationships. You will learn how to place customers into multiple groups, work with table lookups and junction tables, send letters to groups of customers, format a report to print post cards, track products from multiple vendors, and more. Topics include:
  - Many-to-Many Relationships
  - Table Lookup Wizard
  - Multi-Valued Fields
  - Assign Customer to Multiple Groups
  - Junction Tables
  - Prevent Duplicate Records in a Query
  - Format a Report for Post Cards
  - Products from Multiple Vendors
  - Track Unit Cost and Profit

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.


Access Expert Level 7
Description: Access Expert Level 7
Versions: Recorded with Access 2013. Also use with 2007 and 2010.
Pre-Requisites: Access Expert Level 6 strongly recommended
Running Time: 1 Hour, 37 Minutes
Cost: $24.99

This class picks up where Expert Level 6 left off. We will continue working with the letter writer that we started in the previous classes. We will begin, however, by focusing on Many-to-Many Relationships. You will learn what they are, when you should use them, and how to set them up. First, we'll see how the Access Table Lookup Wizard works (and why you shouldn't use it). We'll also learn about multi-valued fields.


Next, we'll learn the correct way to set up Many-to-Many Relationships using a Junction Table (also called a Cross-Reference Table). This is the proper way to set up your relationships. We'll set up a junction table to track customers into multiple groups.


You'll be able to open the Group form and see all of the customers in that group AND open the Customer form to see all of the groups that customer is in.


With our groups in place, we'll be able to send letters to just customers in the selected groups. We'll add this functionality to our Letter Writer form.


We'll also learn some new manual report formatting techniques. We'll format a report to print post cards (from scratch). We'll learn about columns, margins, row and column spacing, and lots more.


Finally, we'll go over another example of many-to-many relationships. We'll track products and vendors. Each vendor supplies us with multiple products, of course, but each product can be sold by multiple vendors. We'll store a unit cost for each product, and this will allow you to comparison shop to pick the lowest-cost inventory.


This is the seventh class in the Access Expert series. If you want to understand many-to-many relationships, then this is definitely the right class to take. Of course, if you have any questions about whether or not this class is for you, please contact me.


Complete Outline - Access Expert Level 7

00. Intro (8:08)

01. Lookup Values in Tables (17:46)
Add Group Field to CustomerT
Lookup Data Type
I will type in the values
Limit to List
Cons of Lookups in Tables
NOT Supported by SQL Server
Allow Value List Edits
Allow Multiple Values
Row Source Item List
Semicolon Delimited Items
Two column text value list
Bound Column
Column Count
Lookup from another table
SQL Statement directly in table
Multi Valued Fields
Allow Multiple Values
Cons of MultiValue Lists
Delete Relationship to GroupT

02. Many to Many Relationships 1 (13:10)
Multiple groups per customer
Cross-reference table
Junction table
Customers to Groups
Vendors to Products
Group Subform on Customer Form

03. Many to Many Relationships 2 (6:25)
Group Form
Show Customers in Group
Copy CustomerGroupSubF
Create GroupCustomerSubF
SHIFT-ENTER line break in label
Groups button on Main Menu
04. Letters By Group (16:36)
IncludeInMailing Field in GroupT
RefreshRecord in Macro
Duplicate customers in mailing
Quick overview of Aggregate Queries
Group By
Min Value

05. Post Cards (12:02)
Create Report Design
Shape Outline Transparent
Landscape v Portrait
Page Setup Dialog
Number of Columns
Row Spacing
Column Spacing
Column Size
Same as Detail
Down, then Across
Across, then Down
Resizing with the Rulerbar
Button from Letter Form

06. Vendors to Products (17:16)
Build ProductF Form
Vendor Table
VendorXProductT Junction Table
Profit Calculation

07. Review (5:10)


Keywords: Many-to-Many Relationships, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, lookup values in tables, limit to list, row source item list, multi valued fields, allow multiple values, many to many, cross-reference table, junction table, aggregate query, duplicate records, group by, min, post cards, landscape, portrait, columns, row spacing, column spacing, margins, vendors to products, unit price, unit cost, profit

Student Interaction: Microsoft Access Expert 7

Richard on 6/18/2013:  Microsoft Access Expert Level 7 is 1 hour, 37 minutes long and focuses on Many-to-Many Relationships. You will learn how to place customers into multiple groups, work with table lookups and junction tables, send letters to groups of customers, format a report to print post cards, track products from multiple vendors, and more. Topics include: - Many-to-Many Relationships - Table Lookup Wizard - Multi-Valued Fields - Assign Customer to Multiple Groups - Junction Tables - Prevent Duplicate Records in a Query - Format a Report for Post Cards - Products from Multiple Vendors - Track Unit Cost and Profit Click here for more information on Access Expert Level 7, including a course outline, sample videos, and more. This course was recorded using Access 2013, but is also valid for Access 2007 and 2010 users. This class follows Expert Level 6.
Bruce Reynolds on 6/18/2013: Thanks for letting all of us know about this "feature" that Microsoft has introduced into Access 2013. They did something similar to the graph types in Excel with the introduction of Excel 2007 and 2010. Too much razzle and dazzle and it totally ignored the principles of displaying data in graphs.

Reply from Richard Rost:

I assume you're talking about the lookup wizard and/or multi-valued fields? Yeah. Microsoft is trying to make it easier for novices to build databases while ignoring proper database design principals.

Bruce Reynolds on 6/18/2013: Who did Microsoft find to introduce the feature of allowing multiple values in one field in a table? This must have been extremely difficult for the C++ or C# programmer to pull off. I'll bet that there were Relational Database experts who tried to discourage this programmer (or team of programmers) during the production of this questionable "feature".

Reply from Richard Rost:

I know. Tell me about it. It's awful. I'll bet the decision was made by a suit who doesn't know databases. "Uhm, we want to make Access easier for the layperson." No dude! It was FINE the way it was! What's really crazy is that the name of the hidden junction table that Access creates to form the relationship is a really odd sequence of numbers and letters. There's a way to get to it with VBA code, but it's not pretty.

Bruce Reynolds on 6/18/2013: So, you're using the form to edit and add mamy to many relationships? Can this also be done globally in Tools, Relationships?

Reply from Richard Rost:

There really is no official, direct "many-to-many" relationship type in Access. It's just a combination of two one-to-many relationships - which I didn't bother to actually set up in the relationships window. It's not necessary unless you want to enforce referential integrity... which is really the ONLY time I bother with the relationship window.

Eleanor Mason on 6/18/2013: No big deal just wanted to bring to your attention Time stamp 17:30 Your Look Up Table you have Notice Users instead of Novice Users

Reply from Richard Rost:

Haha. You're right. That's what I get for recording videos at 4am. Thanks. :)

Carolyn Cwik on 6/19/2013: I am working on a database for a real estate company. Can this be used to categorize customers as Sellers, Buyers Buyer-Client, CoBroke Buyer, Past Seller, Past Buyer etc and also include the type of properties they are selling or looking for such as Residential, Waterfront, Acreage, Recreational, and so forth. Would it be best to have two groups? As an aside, this is also why I am still working on how to get my FirstName, Last Name question figured out. Thanks for your advice.

Reply from Richard Rost:

Yep. You can create groups for whatever you want. You can even create multiple types of groups! Have one set of groups for your seller types, and another type of groups for what they're looking for. The sky's the limit!

James Gray on 6/19/2013: Richard,

I verified that the Refresh command is a subcommand of Run Command in Access 2007

Reply from Richard Rost:

Thanks, James. I should keep an old laptop around here with the different versions of Access on it for the odd times when these questions come up. I only have 2000, 2003, and 2013 on my machines.

D. Forman on 7/19/2013: You said not to use lookups in tables. How about using them in Forms?

Also, are there times when using a lookup in a table is okay (assuming SQL Server is not in the database's future)? For instance, when the value is something very simple such as a 1, 2 or 3 in a rating system. Do you really want to create a new table just for this?

Reply from Richard Rost:

It's a general rule of thumb, but like I said in the video, there are exceptions. If you're sure you're never going to upgrade to SQL Server, and you're pretty sure you're not going to add options in the future, then go ahead. It's my STRONG recommendation, but not a rule.

Maurice C on 9/17/2013: Richard,

I just want to say I'm really enjoying your 2010/2013 Access lessons. I did the 2003 series a couple of years ago and they were great but you seem to be going into a lot more depth in the new series and have added a few new topics which is fantastic. So I definitely recommend these lessons to anyone who did previous course and think these new lessons are just a repeat of the old lessons.

Reply from Richard Rost:

Thanks, Maurice. My goal has been to EXPAND on the topics covered in the previous series where needed, and to ADD new material that I skipped (or glossed over) the first time. They're definitely NOT just the same old lessons re-recorded.

Bonnie on 10/1/2013: Richard, you mentioned that if enough of us asked for it, you would expand what you taught in Expert 7, Lesson 04 -- Letters by Group -- to show the more complex way of marking what groups, when their are multiple users. I would appreciate that whenever you are able. Thanks for another great class. Bonnie
Janet G on 11/27/2013: 4:36 - Combo box sort - How can I sort the people that are already listed in the combo box table - I understand how to sort the drop down list to pick from but they themselves do not sort once added to the list - I'm replicating this in my db whereby I have several "customers" attached to our "Client" - when searching if a customer belongs to our Client it would be helpful to see the Client's Customers sorted.
Alex Hedley on 11/30/2013: Hi Janet,

How are you adding the item to the combo?
Is the combo based on an underlying Query that has a Sort on it? You could just refresh, requery the datasource and then it will be sorted accordingly.


Christine White on 12/16/2013: Is it possible to have 3 values in a junction table? We stock some more generic products (CAT6 cable), the same product can have different manufacturers and vendors:
manf1, Vendor1,2,3
manf2, Vendor1,2,3 etc.
I was thinking of using the Barcode as the static item and displaying a Junction Box with the different variations above. Thanks

Kim Jensen on 3/16/2014: Hi mr. Rost

I have made my spare part form and want to make a vendors to product like you did, however i have a problem, some of the parts we buy from machine suppliers also, and not just plain suppliers. can i drag thoose details in and mix them with the plain suppliers in a quary so i get one list with both in the same colum?
If let say that one Company Closes, eather one, then i want to delete them from both tables at the same time?

michelle maughan on 5/17/2014: Please help, I Have set up an EmployeeT, EducationT and an AttendanceT, how do I get a report that shows all individual employee education, and all education of all employees. I know I am missing something BASIC, but please help!
avrom strasser on 7/14/2014: please help me with this matter im at the second lesson of expert 7 & i got a junction table everything exactly like explained in the lesson but when i bring in the sub form i any data i put in one contact is being show in all contacts i cant put like Hardware to this contact & a different group for another on
avrom s on 7/20/2014: Hi Richerd!
Thanks for all your teaching! I m putting in a lot hoers in your lessons & thanks god I m getting great Knowledge in access.
I sent you a question, & i got the answer between the rows of your lesson.
I m up to expert classes, at the 2nd lesson, i did everything exactly as explained in the lesson, but when i brought in the sub form in the big form it didn't work, i checked the properties & i didn't got the master & child link field & the data i put by one contact was being show in all other contacts.
I tried it several times, was listening to the lesson over & over but I wasn't able to find out what s wrong?
Today i got the answer: i brought in the both foreign keys in the sub form & now it s working very well
(P.s. I got the answer between the lines, cause you said that you are waiting to see if its working without bringing in both keys)

Thanks again
Avrom Strasser

you can post it, it might be a help for other students.

A Silva on 9/19/2014: Going back to the group example.
I would want to force the user to only insert one customer in a certain group once.
The way it was explained in the lesson, Rick, for example, can be putted to times in Harware. Is there a way to eliminate from the options the ones that are already selected for that particular user?


Reply from Richard Rost:

You would have to perform a DLOOKUP to see if he's already in that group and then disallow it in the BeforeUpdate event.

Michael Hrynewich on 11/1/2014: Great Class!! Yes I am interested in multi user interface design.
Michael Hrynewich on 11/1/2014: Can you do a many to many relationship where you've set up vendors to products can then do customers to vendors?? or do they need to be separate??

Reply from Alex Hedley:

Hi Michael,
Yes you can do Customers to Vendors.
A Customer may use many Vendors and Vendors can have many Customers.

Ian Garriques on 12/3/2014: Morning Rick,
Have been enjoying the courses. A question comes up frequently but don't know where it fits into the course program, so I'll just try here. We have a small network at home. I use all windows machines but the only other person on the network uses only Apple devices. We'd like to share the database app I'm slowly working on but her Mac won't run MS Access. How do I get around this problem? Thanks, Ian

Reply from Alex Hedley:

Hi Ian,
Unfortunately MS hasn't produced a Mac version of Access, it's just not got the demand.
I'm a Mac user myself and run a virtualisation program like Parallels or VMFusion and run Windows on that.
One option is SharePoint although Access Web Apps aren't great.
Another option is a custom ASP site that connects to the Access database.
Depending on the complexity are you needing Forms/Reports etc or is it just Table access.
There are 3rd Party apps for opening Access dbs but I've not found a great one yet.

Patrick Hoffmann on 12/11/2014: Hi Richard,
I am working on Access Expert 7 Lesson 3 and have made the GroupCustomerSubF. In the row source for the combo box it shows "ORDERED BY [FullName]; at the end, but instead of getting the customers full name I'm getting customerID number. Not sure what I'm doing wrong and where else to look for the fix. When I run the query on it's own it works fine

Reply from Alex Hedley:

Is the first Field in your Query the ID Field?
How many columns do you have showing and what are their column widths from the Property Sheet?

Patrick H on 12/14/2014: Thanks Alex, That's all it was column width was wrong, nice mini lesson:)))
Patrick Hoffmann on 12/16/2014: Hi Richard
I am having a hard time getting my Product form to recognize the ProductVendorSubF. My ProductF work well and my ProductVendorSubF is also working, but when I drop the sub-form onto the ProductF it doesn't change when I scroll through my products on the ProductF, it just shows all the items in the VendorXProductT. What have I missed/done incorrectly

Pat Hoffmann

Reply from Alex Hedley:

Pat if you are in Design View click on the subform then go to the Property Sheet.
You are looking for the Link Parent/Child Fields.
Check these are filled in and make sure they are the correct ones.

Patrick H on 12/16/2014: OK, so I forgot about check the parent to child relationship, I had misspelt the word productID (added an extra t) in one of the tables and that kept it from automatically forming the relationship.
Thanks again for the mini-lesson Alex

Reply from Alex Hedley:

Great stuff :)

Simon Randell on 12/19/2014: Is their away to get the same look as the lookup (tick box drop down Box) rather then using a sub form

Reply from Alex Hedley:

Simon I'm not sure what you mean, can you give an example?

Felisa on 1/23/2015: I have a field on my form that's multi-valued, I wanted to know if there is a way on the onclick event if the field already has an item or items selected it would go to that item within the list

Reply from Alex Hedley:

Are you wanting to open another Form?

Felisa on 2/9/2015: No, the list is long, and there is a chance that through out different conferences there may be a change, and to have to scroll through a list to deselect then possibly go back up the the list to select the changed item can be cumbersome especially when you have over 500 possibilites

Reply from Alex Hedley:

Can you explain further.
What is it you want to do on the Form, step by step?

Felisa on 2/9/2015: First, when the dropdown box had the focus and the listing is display, I want the item that is selected to have focus. This keeps the user from having to scroll to the selected item, then having to go possibly numerous items either way to select the update item.

Reply from Alex Hedley:

You could bind the control to a field in record source then the selected item would show

I'm still not fully aware of what you're trying to achieve maybe if you explain what you have and what you want with an example we can help

Richard Wilson on 4/12/2015: I have a large number of people who have multiple capacities in multiple years. For example: Donor, Host, Driver, etc. For a Donor, I need to keep up with the Year and the Amount of the donation for that year. Similar problems with other groups. What would be the best way to accomplish this? Make groups such as "Donor 2015", "Donor 2014" etc? Or is there a better way just to create a group "Donor" with something like sub-groups for each year attached to the amount? If this is covered somewhere, please point me in that direction. I can sometimes make the shift from order tracking to people management, but not always!!

Reply from Alex Hedley:

I'd add another Field of Year and record that too, it would then give you the option to query on both criteria.

Richard Wilson on 4/12/2015: I have a PeopleT and I created a GroupT. Then I created a PeopleXGroupT following the steps you outlined with three fields: ID, PeopleID and GroupID. Then the subform to show groups, which did not link automatically so I put in PeopleID and made it invisible. It still did not link so when a box opened, I put in PeopleID as Master and Child forms as well as GroupID on the second line. I slid the form into the PeopleF and when I selected a group, it entered the numbers on the junction table but the information does NOT show up on the Form. Is this enough information for you to tell me what I am likely doing wrong? Oh, I tried adding some data to the junction form and it "took" but not in the PeopleF.

Reply from Alex Hedley:

Have you create a combo box for both People and Groups on the JunctionF.
Have you set a Default value for the People Combo so when it's on the PeopleF it defaults to the current person you're on?
The Link Master/Child Fields should take care of the filtering.

Richard W on 4/13/2015: Field of "Year" with the Group Combo Box as -- for lack of a better term -- "sub field" under it?

Reply from Alex Hedley:

Best not call it Year, it's a reserved word, my bad, call it YearX where X is relating to the thing you're saving.

Richard W on 4/13/2015: I do not know what a Junction Form is. What I have done is follow as carefully as I can the instructions in Access 2013 Expert 7, Section 2, to set up a Group Subform inserted into a PeopleF. The subform does enter the correct information into the Junction Table, BUT that information does NOT show up in the form once I move on to another person and then return to the original person. However the Junction Table has the correct information in it FOR EACH TIME I TRY TO INSERT IT!!! What am I missing about a Junction Form? What class do I need to review to get it? Thanks for your help.

Reply from Alex Hedley:

I'd take this lesson again.
You can get a copy of the Student Database and work from the previous lesson.
You can then get the finished one and compare it to yours to see if there are any differences.

Yolande Villeneuve on 4/22/2015: I'm trying to place a control button in my database so I may open another table. I have done it before with Access 2010 but now with 2013 it will not work. The message is "The expression On Click you entered as the event property setting produced the following error: Ambiguous name detected: Forms_in_client_folder_click

Hope you can help me as it would really help in my database.

I thank you for your interest.

Reply from Alex Hedley:

Are you using the wizard?
Are you using a Macro or VBA?

Cheryl White on 4/30/2015: Just listened to why not to use lookup in tables which was very good explanation. As well as the allowing multiple values. I had notice that update queries could not deal with the multiple values when I was manipulating my database. Also, I have been wondering why you did not use lookup in you tables. I was always using them because it made form design easier. You might want to mention the reason or include the section in earlier videos to help people not have to redo their design. Thanks have learned so much.

Reply from Alex Hedley:

We could add a comment in the early forums.
I think the reason they aren't mentioned early on is because relationships aren't discussed.

Betti Baldan on 5/25/2015: Access 2013 Expert 7 Lesson 3 (Time Index 4:09)
Is there a way to have the customers display in alphabetical order? It's fine if there are only a few, but if there are 50 names, it's not useful.

Reply from Alex Hedley:

Have you tried adding an ORDER BY clause in SQL or using a SORT in Design View?

Betti B on 5/26/2015: The customers show up with a combo box by CustomerID. The combo box DOES have an ORDER BY instruction but that only affects the combo dropdown box.

As customers are added, they are not in alpha order. Is there a way to do that?

Reply from Alex Hedley:

You could require the form data when you add a new customer and if that has a order by that will take effect

Betti Baldan on 5/30/2015: Access 2013 Expert 7 Lesson 3 (Time Index 4:09) - I'm using Access for a property management business for Homeowner Associations. I have an Association table and a Vendors table. They have a many-to-many relationship.

I followed the example in the lesson to set this up and it works just like the example in the lesson with Customers and Groups. On my Association form, I have a Vendors subform which has a combo box of vendors sorted by vendor name. The list of vendors when you click on the combo dropdown box IS in alphabetical order. But the vendors in the subform are not. I see that the customers in the lesson also are not in alpha order. As I add vendors, they seem to go on the subform in ID# order.

Is there a way to get them listed alphabetically by vendor name on the subform? It's difficult to locate a specific name if there's a long list.

Reply from Alex Hedley:

You could add a SORT or ORDER BY to the Query that is the RecordSource of your SubForm.

Joe Beniacar on 11/19/2015: Hi Richard, I'm not sure if we covered this detail of the OrderF yet but:

(For a Many-to-Many relationship:) If you have a Form-Subform, where the Subform s Record Source is a Query that links the JunctionT-the other ManyT (besides the one who s data is on the Form itself), and then add a few fields from that ManyT to the Query (to be able to add them to the Subform) When you add a NEW record to the Subform for a NEW ManyT record (say, with a List Items Edit Form on it s ID Combo Box), wouldn t you only be able to enter info for the ManyT fields that you added to the Query but not values for the REST of the ManyT fields that you didn t add to the Query resulting in new the other ManyT records that are only partially complete?

If we haven't covered this yet, could you perhaps tell me which Level this issue is covered in?

Thank you very much!


Reply from Alex Hedley:

You could have the Form contain as many or as few fields as you wish to fill in.

David Leifer on 1/5/2016: Hi Rick, I have a lot of product substitutions which i replace item b for item a. How can i talk to my product list when i choose item b that this is to replace item a?

Reply from Alex Hedley:

You could run an UPDATE query to swap these values.

Joe Beniacar on 1/23/2016: At 10:35, you say that SQL statements should be stored in Queries or Forms do you mean Forms or Reports (to Query from Tables)?
Joe Beniacar on 1/23/2016: Is it still possible to upgrade a database to Share Point without using Lookup Wizards (even if you want that functionality, by doing it the better way instead)?

Reply from Alex Hedley:

What is the better way?

Joe Beniacar on 1/23/2016: Hi Alex, thanks for your response. My question is: If the Subform doesn't have all of the Table's fields, then when you enter the Subform's record, won't that transfer back to the Table as incomplete records? Is there a way around this issue?
Thank you very much for your help.

Reply from Alex Hedley:

Yes, only the Fields on the Form will be added as a Record in your Table.
You would need to put ALL the Fields from the Table on your Form, you can set DEFAULT VALUES in these Fields and make then Hidden if necessary.

Robert S on 1/30/2016: Richard, I noticed that the last news item from you on the website was for September 2016, have you published and further news items since then? if so I am not getting them.

Reply from Alex Hedley:

None have been published since then but there should be one soon

Joe Beniacar on 1/31/2016: Hi Alex,

Thank you very much for replying to my questions.

Is it possible to have 3 ID fields in a junction table?

Reply from Alex Hedley:

You can create as many joins as you'd like, say you have an OrderID with the CustomerID and a DiscountID


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


Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
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