Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > TechHelp > Directory > Access > Outer Joins < Progress Bar 2 | 15-Min Increments >
Back to Outer Joins    Comments List
Upload Images   Link   Email  
Ambiguous outer joins
Thomas Gonder 
       
11 months ago
My situation has five tables, one is duplicated.
I'll add two images after this is approved.

In a nut shell, I log what users are doing in various jobs (forms, reports, etc.)
All this is stored in the Log table.
The Log table has several related tables. One of them is User.
If someone tries to login more than six times, I make a log record and close the Login form.

The problem is, I don't know who the user is, since they haven't logged in. So, I put a "0" in the field for user in the Log table. The SQL statement that creates the Log record won't allow me to put null in the User field, and I kinda like seeing the zero-it stands out as an error (that I use in other places too). There is no user "0". Thus, I don't get that Log record to show in the query. I did as the Tech Help shows, but now I get an "ambiguous outer joins" error.

This is confusing as I only see one join with an arrow tip, so what in this query is ambiguous? How can we fix it without resorting to multiple queries?
Thomas Gonder OP 
       
11 months ago

Thomas Gonder OP 
       
11 months ago

Thomas Gonder OP 
       
11 months ago
Okay, I think I solved this problem after a break with a good restaurant burger and beer. It could be a trap for others, so I'll try to explain in simple terms.

When I created the relationships between the tables (like Log and User), I followed some advice I heard somewhere to start with the KEY for the primary table (User) and drag to the KEY which is the foreign table (Log). I think I have that right as one User record ID can be used in many different Log records, but not vice versa (one Log!UpdtUserID isn't found in many User records).

The one relationship link shown in my first image just happened to have the correct Left table, probably because I was testing it in different ways, but the other links were wrong (like Job was the Left table in both links).

As a result, most of the tables, other than Log, were the Left table. I deleted the relationship links and did them again, this time dragging from the Log table to the others and then the second choice for the "Include ALL records from..." option.

And it works as shown in the image below.
Thomas Gonder OP 
       
11 months ago

Thomas Gonder OP 
       
11 months ago

Thomas Gonder OP 
       
11 months ago
For the eagle eyes that are figuring out the second set of images:
1) I hid some of the fields in the query.
2) There is a bug that's creating a Menu entry even though the person didn't log in.
3) You can see the date and how many login failures there were in the Short note column.
4) See in records 8227 & 8228 the record was created by the Login form while record 8229 was created by the Menu form. Can you guess why this is so?

These samples were taken from the Access Development Shell (ADS), a description of which can be found in the Access Template Marketplace here in Richard's site.
Kevin Yip 
     
11 months ago
Outer joins should either point in the same direction as all other outer joins, or point away from other outer and inner joins.  For instance:

Table1 --> Table2 --> Table3     OK: all outer joins point in the same direction
Table1 <-- Table2 --> Table3     OK: all outer joins point away from other outer joins
Table1 <-- Table2 --- Table3     OK: outer join points away from inner join

Table1 --> Table2 --- Table3     Not OK: outer join points towards inner join
Table1 --> Table2 <-- Table3    Not OK: outer joins point towards other outer joins


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

 
 
 

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 2025 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 3/23/2025 4:14:35 PM. PLT: 1s