Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Trouble with Left Join

Trouble with Left Join

Trouble with Left Join

UGH. As noted below, I found the answer almost immediately after I posted this. It's a long post, so I'm top-editing to save people the time it would take to read through this all.

Hi folks,

Way back in another lifetime I spent a ton of time here helping folks with their problems. Now I'm old, spacey, and far enough removed from working with Access that I'm here to ask for some help! :(

I've been struggling for a good while to get a Left Join to preserve all of the records from the table on the left while bringing in data from the right table and leaving blank cells when there is no matching record in the right table. I've tried this mutliple times with various queries, tables, and database, and I think I'm just forgetting something essential. I've also scoured the web for samples to compare to my work and not found anything that showed me what I was doing wrong.

First question: I'm not crazy, am I? That is what a Left Join should do, right?

Next question: Can anyone help me fix this?

I'm working in Access 2003 (please, don't ask). For this test, I've turned a query into a table (tbl999999) and I'm LEFT JOINing it to another table, tblContact.

But, as I mentioned, it's not working. tbl999999 has 896 rows, so presumably any Left Join based on that should return at least that many rows, right? Certainly, what I'm looking for is a result set of 896 rows, with data in every row for fields that come from tbl999999 and for fields that come from tblContact, there should be data only when there's a row in tblContact that matches the criteria applied there.

Here's the SQL that I think should do the trick:
SELECT tbl999999.ContactID, tblContact.TimeStamp, tblContact.ContactTypeID
FROM tbl999999 LEFT JOIN tblContact ON tbl999999.FamilyID = tblContact.FamilyID
WHERE (((tblContact.ContactTypeID)=16 Or (tblContact.ContactTypeID) Is Null))
ORDER BY tbl999999.ContactID;

The above query (Query44, in the images below) returns 770 rows. If I replace the LEFT with INNER (query 43), I get exactly the same results. If I remove the criteria from tblContact (Query42), I get 2662 rows, as expected. Shouldn't queries 43 and 44 return differnt results?

Attached are images of the three queries side-by-side in SQL, Design, and Datasheet view.

In that last image, I've highlighted two records that don't show up in the other two datasets. It makes sense to me that these do not show up in the middle dataset, as that is an inner join, which should require the presence of matching records in the two tables. But shouldn't those records show up in the dataset on the right, since that's using a LEFT JOIN?

Thanks for any help on this!! (And hello to any old pals who are still hanging out helping folks here!!!)


Jeremy Wallace
You can find us on the web, and my e-mail is fairly easy to guess.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close