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:
[pre]
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;
[/pre]
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
---
Jeremy Wallace
ABCDataworks
You can find us on the web, and my e-mail is fairly easy to guess.
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:
[pre]
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;
[/pre]
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
---
Jeremy Wallace
ABCDataworks
You can find us on the web, and my e-mail is fairly easy to guess.
