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  
 
 
 
Courses - Microsoft Access 309
Description: Advanced Access
Running Time: 65 minutes
Pre-Requisites: Access 308 very strongly recommended
Previous Lesson: Access 308
Next Lesson: Access 310
Main Topics: Many-to-Many Relationships, Junction Tables, CDATE Function, Date Converstions
Versions: This course is valid for Access 2000 through 2003. If you are using Access 2007 or 2010, you will still benefit from this course. You will find that the concepts are the same, but there are cosmetic differences between the versions.

    

Order before 4/22/2019 to get a FREE upgrade to our Access 2010 version!
Click here for details

 

Access 309 covers two main topics: many-to-many relationships, and date conversions.

So far, the only types of relationships we've learned about have been either one-to-one or one-to-many. One-to-one relationships are used where you have one record related to exactly one other record, like the extended customer information form we made. It shows one additional screen of information for one customer.

A one-to-many relationship is the "traditional" relationship you find in most databases. One customer to many orders. One customer to many contacts. One manufacturer to many products. Etc. Another example would be car owners to vehicles. Each driver might own one or more vehicles, but the vehicles aren't driven by different people. One car owner to many vehicles.



In this class, I'm going to teach you about many-to-many relationships.

 

For example, if you have a fleet of vehicles and different drivers who might drive any car, you might need to be able to assign drivers to vehicles on different dates. This is a many-to-many relationship. Many drivers for many vehicles.

 

This is what you'd use if you want to be able to put a many different customers in MULTIPLE groups... or put a different members in MULTIPLE classifications... or assign a several drivers to MULTIPLE vehicles... or assign a MULTIPLE supervisors to MULTIPLE employees. This type of relationship is extremely powerful.



 

Then, after we study relationships, we're going to work with dates. Dates are often misunderstood in Access. I'm going to teach you about the CDate() conversion function. I'm also going to teach you tricks for dealing with dates that you might import from someone else's database. Perhaps you got a date like "20070910" and you have to convert that to a regular Access date.

 


ACCESS 309 - Course Outline

0. Introduction

1. Relationships Review
One to Many
Many to Many
Cross Reference Tables
DriverXVehicleT

2. Cross Reference Table
Creating the Many-To-Many Relationship
Making a Cross Reference Table
Creating a Group Table for our Customers
CustomerXGroupT
CustomerXGroupQ

3. Group Form
Creating the Group Subform
Many To Many Form

4. Converting Numbers into Dates
Got a field with dates like 990105
Convert with an Update Query to 1/5/99
LEFT, RIGHT, MID functions
Update Query
String Concatenation
Calculated Fields

5. Addendum on Numbers to Dates
What if you have dates with 5 digits?
Using LEFT and LEN functions
CDate() to convert text to dates

 

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
Change Email
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