ERROR #3704: Operation is not allowed when the object is closed. Microsoft Access 309: Many-to-Many Relationships, Junction Tables, CDATE Function, Date Converstions
 

4/24/2017: You may see an "operation not allowed" error on the site. We're working on fixing the problem. Nothing seems to be affected, it's just annoying. Carry on. :)   [dismiss]
 
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  
 
NEW Release: Access Advanced Level 4 and Level 5   dismiss
 
 
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/27/2017 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

 

 

Student Interaction: Microsoft Access 309

Richard on 10/9/2007:  Access 309 covers two main topics: creating MANY-TO-MANY relationships, and converting numbers and text fields to proper Access dates. You can find out more about this class on the Access 309 page. This is going to be the first class that I'm promoting student interaction with. The Amicron Video Player and the 599CD Theater will both have links to this blog post so that students who are taking the course can discuss it. They can post any questions they might have here (as comments) and we can work together interactively as students & teacher. I'm excited! Now, please, post your questions here!
Terry on 10/9/2007: Are many to many relationships the kind that you would use to track members and groups? I have a membership database i'm building with your lessons. I need to be able to put members in multiple groups.
Richard on 10/9/2007: Yes, that is exactly what you would use this for. Many people in multiple groups. Many drivers with multiple cars. Etc.
Twila Mitchell on 11/8/2007: As the database grows, I find myself coming back into old forms (or using them to copy). When in design view, I find it helpful to readily see text boxes that are not visible in form view. I immediately know what's invisible because I made their background color Hot Pink.
Richard Rost on 11/8/2007: Yes, this is an excellent idea. I do this myself from time to time - althought I have to say I don't use hot pink. :)
Margaret Cattarin on 3/14/2009: Access 309 at 05:03
I am following the video step by step to build a survey response form. Everything is great until I change the form properties to the CustomerXGroup Query. Then I lose the star and can't add new records. I change the properties back to the CustomerxGroup table, and the star is back. How to I fix this?
Thanks,
Maggie

Richard Rost on 3/15/2009: Margaret, it sounds like your query has properties that make it non-updateable. Can you open the query directly and add records to it? Make sure your query is exactly the same as mine (download the sample database file from the web site if you need to).
Doris  Taijeron on 10/1/2010: In lesson 3 -Group Form 3:33, following your example to set query criteria in the CustomerID Field to:=Forms!CustomerF!CustomerID, when I run the query, the 'Enter Parameter Value box pops up with the message Forms!CustomerF!CustomerID. I followed all your steps for this lesson. Is there something I'm missing?

Reply from Richard Rost:

That error message usually pops up when something is spelled wrong. Make sure you have it EXACTLY as I have it, and that nothing is spelled wrong. Also check for spaces and punctuation.

 Alyson on 6/12/2012: Ok, I am getting a little frusterated. I made an EmployeeXClient T&Q. I made the relationships. I have it set up to put employee information from the employee file that is opened. I want to be able to type in a client number and it pull the client information from that client's file and put it on the EmployeeXClientF. When I try to do this I get the "Error sound" that notifies me that I cannot enter data there. What am I doing wrong? Please help! :)

Reply from Richard Rost:

Depending on how you set up your relationships, you might have created a read-only query. What information are you trying to pull from your client table, and where are you trying to put it? Share some of your table structure (the relevant fields) with me.

Alyson on 6/13/2012: I need to pull the client #, name, county of residence, and phone numbers from the client table. There are multiple clients per employee.

Reply from Richard Rost:

Is there a reason you need to duplicate all of this information? If your relationship is set up properly, all you need is the EmployeeID and the ClientID. All of the rest of the data can just be pulled from the table. This is how I would set it up:

EmployeeT: EmployeeID, FirstName, LastName, etc.
ClientT: ClientID, CompanyName, FirstName, etc.
EmployeeXClientT: ID (key), EmployeeID, ClientID

That's all you need. Unless you need to track HISTORICAL data (like what the client's address was at the time he placed an order), then you don't need to duplicate the information in multiple tables.

Alyson on 6/13/2012: Well, it's an employee schedule. So one employee sees more than one client. I have it set up like you suggest, but it won't let me type in a client ID.

Reply from Richard Rost:

OK, now make an EMPLOYEE form with a SUBFORM. The subform is based on your junction table. The combo box on your subform shows a list of clients from the client table. If you want to DISPLAY the information from each client in the subform, use a query that includes just the junction table with a one-to-many join to the client table. That should make it an updateable dataset.

Alyson on 6/15/2012: Ok. Thanks. Is this the most practical way? We have around 220 employees and 500 clients, so it will be a long combo box...

Reply from Richard Rost:

Yeah, pretty much. You could narrow it down by department or category using two cascading combo boxes, but that's pretty much the best way to do it without a complex search form.

Alyson on 7/3/2012: Ok, I have done as you said and it works as far as bringing in the client information, but now when I update the "time in/ time out" fields, it updates it for every client that employee sees. Also, it creates a new Client record in the ClientT with the "lastname, firstname" format...

Reply from Richard Rost:

I don't understand. Can you please be a little more specific? It sounds like you don't have a form bound to the right table/query.

Emad on 2/17/2016: Hello, I have Access 2013 I got Parameter Value box pops, I have Check the Spelling for Forms Name and Contolrs names on the table but still this comes to me again. Kindly, help me to fix that problem becuse it is important for me.

Reply from Alex Hedley:

Can you change to SQL view and post your code

Emad on 2/17/2016: Kindly, can you explain more the last part [NewDOb], I did not get it?

Reply from Alex Hedley:

Since the DOB is a number Access strips any leading spaces, the IIF function checks for this and appends the leading 0 so it's always a 6 digit date, although it's actually a string now.

Richard Wilson on 5/31/2016: I am using Access 2016. I have a many to many situation involving Schools (colleges) and Teachers wherein a given professor can simultaneously be on several faculties and of course each school has many faculty members. However, each Teacher can have many phone numbers, emails, websites, addresses and some would be related to a particular School. Is there a class that would show me how to do this? I am getting multiple versions of the same data in the ways I have been trying to solve the problem.


Reply from Alex Hedley:

The Relationship Seminar covers this. Check out the outline.

 

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