Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   TechHelp   Forums   Help   Contact   Merch   Join   Order   Logon  
 
Back to Access Forum    Comments List
Upload Images   @Reply   Bookmark    Link   Email   Next Unseen 
Creating Junction table form
David Cummins 
      
3 years ago
Backgroud: I build a quick database using lookup tables for a supervisor. I am now rebuilding as a relational database. I am creating a table where I assign two staff members to once client. I have ClientT and a StaffT. The problem is that I can only assign one staff person using the StaffID field to store in.This was not an issue on the previous DB using lookup's. I am trying to a junction table but I don't understand how to create a form for this type of table.

Kevin Robertson  @Reply  
           
3 years ago
David Cummins OP  @Reply  
      
3 years ago
Many-to-many is what I thought, but it dawned upon me that if I want to assign two staff people to a single client, would that not be a many to one relationship?
David Cummins OP  @Reply  
      
3 years ago
I see the problem. I need to select a family to be audited by two staff members. I have a FamiliesT, a StaffT and I need a third table that selects which family will be audited and then to select two staff members to be the auditors. I built another table, FamilyAuditT. The problem is, after several attempts and using different combinations of relating the FamilyAuditT, I can only select one staff member because I am bound to the StaffID. I have two choices. 1) use lookup fields or 2) duplicate the StaffT , call it Staff2T and then use a Combo Box to select a staff member from StaffT and Staff2T. The problem is that I have to update both Staff tables with staff changes, which obviously is a not acceptable. I am sure the right VBA code will solve this problem but I don't know what that would be. Alternatively, is there a way to edit both StaffT and Staff2T simultaneously?
Sami Shamma  @Reply  
             
3 years ago
If your requirement will not change in the future, to assign more staff to a customer then the simplest solution is that you have staff ID1 and staff ID2 on the customers table. Then in your query, you would link a customer table 1 to 1 to the staff table. You will need to iterations of the staff table.  
This is not considered good design as you will be limited to only two staff assigned to a customer.
Sami Shamma  @Reply  
             
3 years ago
You do not need 2 staffT tables. You can link your customer table to the staffT twice.
To Show you this, create a new query add customerT once and add staffT twice.
You will get the second stuff table renamed staffT_1. Now link staff1ID from the customerT two staffT and link staff2ID from customerT table to staffT_1.

Add the following fields to the query; customer ID, customer name, staff ID and staff name from staffT, staff ID and staff name from staffT_1.
I think this will give you what you are looking for
Good luck

Kevin Robertson  @Reply  
           
3 years ago

David Cummins OP  @Reply  
      
3 years ago
Thanks, all, this jarred my memory.

I remembered I built an Access file awhile back with junction tables and forms that I can now use as templates. Access is not like riding a bicycle. Use or lose it. At least for me.
Scott Axton  @Reply  
        
3 years ago
"it dawned upon me that if I want to assign two staff people to a single client, would that not be a many to one relationship?"

Just to back up a bit and help you talk it through.  
Each Staff can have many Clients.  Each Client could be serviced by many Staff members. So the many-to-many
You can limit it to two but if in the future your requirements or needs change you don't have to change all your forms by setting it up like Kevin showed above.

If you do want to limit it to only two see the Event Enrollment video for an example of how to do that.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.
 

Next Unseen

 
New Feature: Comment Live View
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2026 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 5/6/2026 10:34:22 PM. PLT: 1s