Lessons

Resources

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Subscribe to Access 309
Get notifications when this page is updated

Intro

In lesson 309 you will learn how to create many to many relationships in Microsoft Access using cross reference tables, such as managing vehicles and drivers with multiple associations. I will also show you how to convert dates that are stored as numbers or text into proper Access date formats using update queries and text functions like Left, Right, Mid, and CDate, including handling dropped leading zeros. This class follows Microsoft Access 308 and builds on concepts from previous lessons in the Developer series.

Transcript

Welcome to Microsoft Access 309, Access Developer Level 9, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

Today's class is going to focus on two main topics. The first is creating many to many relationships. We have learned about relationships in lots of our previous classes starting with Access 201, where we created a one to many relationship, where there was one customer related to many orders or many contacts.

In today's class, we are going to focus on a many to many relationship, where you have two sets of related items, but they might have multiple relationships between them. For example, think of vehicles and drivers. You have a list of vehicles. You have a list of drivers. Sometimes vehicle A might have driver one, two, or three. Sometimes driver one might be driving vehicle A, B, or C.

So we need to learn how to create cross reference tables to keep track of our many to many relationships.

Then we are going to do some work with converting dates. You might sometimes get databases that have dates given to you by other people that might not be stored in Access Date formats. You might get numbers. You might get text fields. You might have missing leading zeros. All kinds of different problems.

We are going to use some update queries. We are going to be converting number and text fields into dates. I will show you how to deal with dropped leading zeros. We will work with the left, right, and mid functions, and also the CDate function, which is a date conversion function, very handy.

This class follows Microsoft Access 308. I strongly recommend you watch that class before this one. We also learned about some of the stuff in 308 that we are going to cover today. For example, we covered the left function and some of the other text string manipulation functions in 308, so it would definitely be handy to watch that class first.

I am working with Access XP and Windows XP for this lesson. However, the version of Access that you have should not matter.

Of course, I cannot stress enough doing the examples for yourself. I know sometimes I move a little fast in class, but trust me, you will remember that material better if you stop and do the examples. Pause the video if you have to, but make sure that you understand what I am doing and why I am doing it.

Later on, when we get into more advanced material and I am using some of the techniques we learned in this class, you will thank yourself. Stop and do the examples.

The sample database file that I am using in today's class can be found on the website at accesslearningzone.com/students. That is forward slash students.

We have a new feature now. As of October 2007, all of our courses are now interactive, which means you can post comments and or questions on the website about this class. You can interact with me and the other students that are taking it.

If you are using the new Amicron video player, which is the desktop version, or if you are watching it with the Amicron theater, you will see a pane open up - a small web browser will open up if you are online - that will show the comments and or questions that other students have posted about this specific class and the lessons in the class.

If not, if you are watching our videos offline, I know a lot of people look at the laptops to watch in the car or on the plane, you can go to the website later at accesslearningzone.com?GOAC309. That is a special page just for this class. It is go, AC is in Access, 309. That will take you right to the pages for this specific class.

Again, I welcome you to post your questions, comments, and anything else you might want to say about this lesson and all the specific lessons in the class.

Quiz

Q1. What is the main focus of Access Developer Level 9 (Access 309)?
A. One to many relationships and text formatting
B. Creating many to many relationships and converting date formats
C. Importing Excel data into Access
D. Designing advanced forms

Q2. What is an example of a many to many relationship given in the lesson?
A. Customers and orders
B. Employees and departments
C. Vehicles and drivers
D. Students and grades

Q3. How are many to many relationships typically managed in Access?
A. By using lookup fields
B. By creating cross reference tables
C. By duplicating data in multiple tables
D. By using auto-number fields as primary keys

Q4. What type of data issues with dates does the lesson mention?
A. Incorrect sorting of dates
B. Dates stored as numbers or text without Access date format
C. Calendar synchronization problems
D. Unicode character conversions

Q5. Which function is recommended for converting text or numbers to dates in Access?
A. DateDiff
B. CDate
C. Now
D. DateAdd

Q6. Which text functions are specifically mentioned as being covered in this lesson?
A. UCase and LCase
B. Left, Right, and Mid
C. Trim and Replace
D. InStr and Len

Q7. The lesson recommends doing what while learning the material?
A. Skipping examples to save time
B. Pausing the video and doing the examples yourself
C. Memorizing all textbook content
D. Using a printed manual only

Q8. If a student wants to interact with the instructor or other students about the class, what should they do?
A. Send a letter by mail
B. Post comments or questions on the AccessLearningZone website
C. Call the support line immediately
D. Only use the desktop video player without internet access

Q9. The Amicron video player desktop version and Amicron theater both allow you to:
A. Edit the video content
B. View comments and questions from other students if online
C. Directly email the instructor
D. Access the course offline only

Q10. What should students do if they are watching the video offline and want to interact or ask questions?
A. Wait until the next in-person class
B. Visit accesslearningzone.com?GOAC309 later to access the relevant pages
C. Comment on any video regardless of the lesson
D. Join a separate online forum not associated with the course

Answers: 1-B; 2-C; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 9-B; 10-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

Today's video from Access Learning Zone is Microsoft Access 309, Access Developer Level 9. I am your instructor, Richard Rost.

In this class, I am focusing on two primary topics. First, I will demonstrate how to set up many to many relationships in your databases. In earlier courses, such as Access 201, you learned about one to many relationships, like linking a single customer to multiple orders or contacts. However, a many to many relationship involves two groups of items that can each be related to multiple records from the other group. For example, think about a list of vehicles and a list of drivers. Any given driver may use several different vehicles, and any vehicle can be driven by various drivers. To organize and track these connections effectively, I will show you how to create a cross reference table, which is essential for handling many to many relationships.

The second core topic is working with date conversions. In real-world scenarios, you might receive databases containing dates in formats that Access does not recognize, such as numeric or text fields, or with missing leading zeros. I will guide you through using update queries to convert numbers or text into actual dates. You will learn how to fix dropped leading zeros and use functions like Left, Right, and Mid to manipulate text, as well as the CDate function to convert text or numbers into date values. These tools are valuable when cleaning and standardizing your data.

If you have not yet watched Microsoft Access 308, I highly recommend doing so beforehand. Many of the techniques I will discuss, such as using the Left function and other text string manipulation methods, were first introduced in that class.

For today's lesson, I am using Access XP and Windows XP, but the steps I cover are generally applicable to any version of Access, so you should be able to follow along with whatever version you have.

Whenever possible, I encourage you to work through the examples on your own. It is easy to watch along passively, but you will retain the information much better if you pause and practice each step yourself. This hands-on approach will benefit you as the material grows more advanced in the future.

The sample database file for this session is available for download at the Access Learning Zone website under the students section.

As a new feature, all courses offered after October 2007 now include interactive components that let you post questions and comments about the class on the website. You can interact with me and other students directly as you work through each lesson.

If you are viewing the lessons using the new Amicron video player or the Amicron theater, an integrated browser window will display comments and questions from other students about this specific class and its lessons. If you are not connected to the internet or watching offline, you can still post your questions later by visiting the special class page at the Access Learning Zone website using the provided class link.

Please feel free to contribute your thoughts, questions, or feedback regarding any lesson in the class. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Creating many to many relationships in Access
Using cross reference tables for relationships
Converting number and text fields into date fields
Fixing dropped leading zeros in date data
Using update queries to convert data types
Working with the Left, Right, and Mid functions
Using the CDate function for date conversion