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