Left join in query

[Tara]

I have a query set up with a one-to-many left join on two tables in order to
pull all records from one table (the "one" side) and the corresponding
records from another table (the "many" side), if they exist. It works well,
but only in THAT query. In other words, if I run that query, the records
show up as I want them to. But, if I then use that query in yet another
query to further manipulate the data, the only records that show up are the
records in the many table. Why and how can I fix it? I can post the query
if needed.

 

[Daryl S]

Tara -

I suspect you may have some criteria in the second query that doesn't take
into account the null values for the cases where there is data for the 'one'
table and not for the 'many'. We can only help if you post the SQL for both
queries...

 

[Tara]

Thanks Daryl...

Here is the SQL for the query that works:

SELECT tblContact.ContactID, tblContact.CaseID, tblContact.Date,
IIf(tblAmendedDates.F2F Is Null,tblContact.F2F,tblAmendedDates.F2F) AS
F2FAmended, IIf(tblAmendedDates.TeamMtg Is
Null,tblContact.[TeamMtg],tblAmendedDates.TeamMtg) AS TeamMtgAmended,
IIf(tblAmendedDates.CPC Is Null,tblContact.CPC,tblAmendedDates.CPC) AS
CPCAmended, IIf(tblAmendedDates.CC Is Null,tblContact.CC,tblAmendedDates.CC)
AS CCAmended, IIf(tblAmendedDates.OC Is
Null,tblContact.OC,tblAmendedDates.OC) AS OCAmended,
IIf(tblAmendedDates.Travel Is Null,tblContact.Travel,tblAmendedDates.Travel)
AS TravelAmended, IIf(tblAmendedDates.NS Is
Null,tblContact.NS,tblAmendedDates.NS) AS NSAmended,
IIf(tblAmendedDates.Court Is Null,tblContact.Court,tblAmendedDates.Court) AS
CourtAmended, IIf(tblAmendedDates.Training Is
Null,tblContact.Training,tblAmendedDates.Training) AS TrainingAmended,
IIf(tblAmendedDates.StaffMtg Is
Null,tblContact.StaffMtg,tblAmendedDates.StaffMtg) AS StaffMtgAmended,
IIf(tblAmendedDates.Admin Is Null,tblContact.Admin,tblAmendedDates.Admin) AS
AdminAmended, IIf(tblAmendedDates.DSOther Is
Null,tblContact.DSOther,tblAmendedDates.DSOther) AS DSAmended,
tblContact.PostedDate, tblContact.DSTypeID, tblContact.OCTypeID,
tblContact.numID, IIf(tblAmendedDates.Notes Is
Null,tblContact.Notes,tblAmendedDates.Notes) AS NotesAmended
FROM tblContact LEFT JOIN tblAmendedDates ON tblContact.ContactID =
tblAmendedDates.ContactID;


Here is the query (that doesn't work) that I'm trying to use the above query
in:

SELECT QryAmendedContacts.ContactID, QryAmendedContacts.CaseID,
QryAmendedContacts.PostedDate AS [Date], QryAmendedContacts.F2FAmended,
QryAmendedContacts.TeamMtgAmended, QryAmendedContacts.CPCAmended,
QryAmendedContacts.CCAmended, QryAmendedContacts.OCAmended,
QryAmendedContacts.TravelAmended, QryAmendedContacts.NSAmended,
QryAmendedContacts.CourtAmended, QryAmendedContacts.TrainingAmended,
QryAmendedContacts.StaffMtgAmended, QryAmendedContacts.AdminAmended,
QryAmendedContacts.DSAmended, QryAmendedContacts.DSTypeID,
QryAmendedContacts.OCTypeID, QryAmendedContacts.NotesAmended,
QryAmendedContacts.numID
FROM QryAmendedContacts, tblDates
WHERE (((QryAmendedContacts.PostedDate) Between [tblDates]![StartDate] And
[tblDates]![EndDate]));

 

[Tara]

That's essentially what Daryl said to look for too, but I can't find it. The
only criteria is on PostedDate which originally comes from tblContacts, the
"one" side.

 

[Daryl S]

Tara -

Do you have an example of a record from the first query plus a record from
tblDate that you think should be in the results of the second query, but
isn't there?

--
Daryl S

Thanks Daryl...

Here is the SQL for the query that works:

SELECT tblContact.ContactID, tblContact.CaseID, tblContact.Date,
IIf(tblAmendedDates.F2F Is Null,tblContact.F2F,tblAmendedDates.F2F) AS
F2FAmended, IIf(tblAmendedDates.TeamMtg Is
Null,tblContact.[TeamMtg],tblAmendedDates.TeamMtg) AS TeamMtgAmended,
IIf(tblAmendedDates.CPC Is Null,tblContact.CPC,tblAmendedDates.CPC) AS
CPCAmended, IIf(tblAmendedDates.CC Is Null,tblContact.CC,tblAmendedDates.CC)
AS CCAmended, IIf(tblAmendedDates.OC Is
Null,tblContact.OC,tblAmendedDates.OC) AS OCAmended,
IIf(tblAmendedDates.Travel Is Null,tblContact.Travel,tblAmendedDates.Travel)
AS TravelAmended, IIf(tblAmendedDates.NS Is
Null,tblContact.NS,tblAmendedDates.NS) AS NSAmended,
IIf(tblAmendedDates.Court Is Null,tblContact.Court,tblAmendedDates.Court) AS
CourtAmended, IIf(tblAmendedDates.Training Is
Null,tblContact.Training,tblAmendedDates.Training) AS TrainingAmended,
IIf(tblAmendedDates.StaffMtg Is
Null,tblContact.StaffMtg,tblAmendedDates.StaffMtg) AS StaffMtgAmended,
IIf(tblAmendedDates.Admin Is Null,tblContact.Admin,tblAmendedDates.Admin) AS
AdminAmended, IIf(tblAmendedDates.DSOther Is
Null,tblContact.DSOther,tblAmendedDates.DSOther) AS DSAmended,
tblContact.PostedDate, tblContact.DSTypeID, tblContact.OCTypeID,
tblContact.numID, IIf(tblAmendedDates.Notes Is
Null,tblContact.Notes,tblAmendedDates.Notes) AS NotesAmended
FROM tblContact LEFT JOIN tblAmendedDates ON tblContact.ContactID =
tblAmendedDates.ContactID;


Here is the query (that doesn't work) that I'm trying to use the above query
in:

SELECT QryAmendedContacts.ContactID, QryAmendedContacts.CaseID,
QryAmendedContacts.PostedDate AS [Date], QryAmendedContacts.F2FAmended,
QryAmendedContacts.TeamMtgAmended, QryAmendedContacts.CPCAmended,
QryAmendedContacts.CCAmended, QryAmendedContacts.OCAmended,
QryAmendedContacts.TravelAmended, QryAmendedContacts.NSAmended,
QryAmendedContacts.CourtAmended, QryAmendedContacts.TrainingAmended,
QryAmendedContacts.StaffMtgAmended, QryAmendedContacts.AdminAmended,
QryAmendedContacts.DSAmended, QryAmendedContacts.DSTypeID,
QryAmendedContacts.OCTypeID, QryAmendedContacts.NotesAmended,
QryAmendedContacts.numID
FROM QryAmendedContacts, tblDates
WHERE (((QryAmendedContacts.PostedDate) Between [tblDates]![StartDate] And
[tblDates]![EndDate]));

Tara -

I suspect you may have some criteria in the second query that doesn't take
into account the null values for the cases where there is data for the 'one'
table and not for the 'many'. We can only help if you post the SQL for both
queries...

 

[Tara]

tblDates is just a one-record table that is used to set date parameters for
various queries in the database. Do you just mean a record from the first
query that should show up in the second as well, based on that date
parameter? If so, then yes - but I'm afraid I'm not sure how you want me to
represent it here...

Are you just asking for some general information about the record?

If so, then for example - I have a record from the "one" table (tblContact)
with a ContactID of 18792 that falls within the required date parameters that
are set in the second query. It does not have a related record in
tblAmendedDates (the "many" table) but it does show up in my initial query
thanks to the left join.

Tara -

Do you have an example of a record from the first query plus a record from
tblDate that you think should be in the results of the second query, but
isn't there?

--
Daryl S

Thanks Daryl...

Here is the SQL for the query that works:

SELECT tblContact.ContactID, tblContact.CaseID, tblContact.Date,
IIf(tblAmendedDates.F2F Is Null,tblContact.F2F,tblAmendedDates.F2F) AS
F2FAmended, IIf(tblAmendedDates.TeamMtg Is
Null,tblContact.[TeamMtg],tblAmendedDates.TeamMtg) AS TeamMtgAmended,
IIf(tblAmendedDates.CPC Is Null,tblContact.CPC,tblAmendedDates.CPC) AS
CPCAmended, IIf(tblAmendedDates.CC Is Null,tblContact.CC,tblAmendedDates.CC)
AS CCAmended, IIf(tblAmendedDates.OC Is
Null,tblContact.OC,tblAmendedDates.OC) AS OCAmended,
IIf(tblAmendedDates.Travel Is Null,tblContact.Travel,tblAmendedDates.Travel)
AS TravelAmended, IIf(tblAmendedDates.NS Is
Null,tblContact.NS,tblAmendedDates.NS) AS NSAmended,
IIf(tblAmendedDates.Court Is Null,tblContact.Court,tblAmendedDates.Court) AS
CourtAmended, IIf(tblAmendedDates.Training Is
Null,tblContact.Training,tblAmendedDates.Training) AS TrainingAmended,
IIf(tblAmendedDates.StaffMtg Is
Null,tblContact.StaffMtg,tblAmendedDates.StaffMtg) AS StaffMtgAmended,
IIf(tblAmendedDates.Admin Is Null,tblContact.Admin,tblAmendedDates.Admin) AS
AdminAmended, IIf(tblAmendedDates.DSOther Is
Null,tblContact.DSOther,tblAmendedDates.DSOther) AS DSAmended,
tblContact.PostedDate, tblContact.DSTypeID, tblContact.OCTypeID,
tblContact.numID, IIf(tblAmendedDates.Notes Is
Null,tblContact.Notes,tblAmendedDates.Notes) AS NotesAmended
FROM tblContact LEFT JOIN tblAmendedDates ON tblContact.ContactID =
tblAmendedDates.ContactID;


Here is the query (that doesn't work) that I'm trying to use the above query
in:

SELECT QryAmendedContacts.ContactID, QryAmendedContacts.CaseID,
QryAmendedContacts.PostedDate AS [Date], QryAmendedContacts.F2FAmended,
QryAmendedContacts.TeamMtgAmended, QryAmendedContacts.CPCAmended,
QryAmendedContacts.CCAmended, QryAmendedContacts.OCAmended,
QryAmendedContacts.TravelAmended, QryAmendedContacts.NSAmended,
QryAmendedContacts.CourtAmended, QryAmendedContacts.TrainingAmended,
QryAmendedContacts.StaffMtgAmended, QryAmendedContacts.AdminAmended,
QryAmendedContacts.DSAmended, QryAmendedContacts.DSTypeID,
QryAmendedContacts.OCTypeID, QryAmendedContacts.NotesAmended,
QryAmendedContacts.numID
FROM QryAmendedContacts, tblDates
WHERE (((QryAmendedContacts.PostedDate) Between [tblDates]![StartDate] And
[tblDates]![EndDate]));

Tara -

I suspect you may have some criteria in the second query that doesn't take
into account the null values for the cases where there is data for the 'one'
table and not for the 'many'. We can only help if you post the SQL for both
queries...

--
Daryl S


:

I have a query set up with a one-to-many left join on two tables in order to
pull all records from one table (the "one" side) and the corresponding
records from another table (the "many" side), if they exist. It works well,
but only in THAT query. In other words, if I run that query, the records
show up as I want them to. But, if I then use that query in yet another
query to further manipulate the data, the only records that show up are the
records in the many table. Why and how can I fix it? I can post the query
if needed.

 

[John W. Vinson]

Here is the query (that doesn't work) that I'm trying to use the above query
in:

SELECT QryAmendedContacts.ContactID, QryAmendedContacts.CaseID,
QryAmendedContacts.PostedDate AS [Date], QryAmendedContacts.F2FAmended,
QryAmendedContacts.TeamMtgAmended, QryAmendedContacts.CPCAmended,
QryAmendedContacts.CCAmended, QryAmendedContacts.OCAmended,
QryAmendedContacts.TravelAmended, QryAmendedContacts.NSAmended,
QryAmendedContacts.CourtAmended, QryAmendedContacts.TrainingAmended,
QryAmendedContacts.StaffMtgAmended, QryAmendedContacts.AdminAmended,
QryAmendedContacts.DSAmended, QryAmendedContacts.DSTypeID,
QryAmendedContacts.OCTypeID, QryAmendedContacts.NotesAmended,
QryAmendedContacts.numID
FROM QryAmendedContacts, tblDates
WHERE (((QryAmendedContacts.PostedDate) Between [tblDates]![StartDate] And
[tblDates]![EndDate]));

As Daryl and Marshall said, you're using a criterion on the query field
PostedDate. For those records where no query record exists, that field will be
NULL - and NULL is in fact *not* Between those two dates.

Try changing the WHERE clause to

WHERE QryAmendedContacts.PostedDate Between [tblDates]![StartDate] And
[tblDates]![EndDate] OR QryAmendedContacts.PosteDate IS NULL

 

[Tara]

I have to leave for the day soon and I won't be able to get back to this
until Monday morning. Thanks so much for all of your help and I hope I can
touch base with both of you again on Monday to pick your brains some more
about this issue.

 

[Daryl S]

Tara -

What is the [PostedDate] for contactID 18792, and what are the StartDate and
EndDate values in tblDates? I assume all three of these are true date field
(that is, not text fields).

Make sure there is only one record in tblDates - if there is more than one
record, then this could cause a problem.

--
Daryl S

tblDates is just a one-record table that is used to set date parameters for
various queries in the database. Do you just mean a record from the first
query that should show up in the second as well, based on that date
parameter? If so, then yes - but I'm afraid I'm not sure how you want me to
represent it here...

Are you just asking for some general information about the record?

If so, then for example - I have a record from the "one" table (tblContact)
with a ContactID of 18792 that falls within the required date parameters that
are set in the second query. It does not have a related record in
tblAmendedDates (the "many" table) but it does show up in my initial query
thanks to the left join.

Tara -

Do you have an example of a record from the first query plus a record from
tblDate that you think should be in the results of the second query, but
isn't there?

--
Daryl S

Thanks Daryl...

Here is the SQL for the query that works:

SELECT tblContact.ContactID, tblContact.CaseID, tblContact.Date,
IIf(tblAmendedDates.F2F Is Null,tblContact.F2F,tblAmendedDates.F2F) AS
F2FAmended, IIf(tblAmendedDates.TeamMtg Is
Null,tblContact.[TeamMtg],tblAmendedDates.TeamMtg) AS TeamMtgAmended,
IIf(tblAmendedDates.CPC Is Null,tblContact.CPC,tblAmendedDates.CPC) AS
CPCAmended, IIf(tblAmendedDates.CC Is Null,tblContact.CC,tblAmendedDates.CC)
AS CCAmended, IIf(tblAmendedDates.OC Is
Null,tblContact.OC,tblAmendedDates.OC) AS OCAmended,
IIf(tblAmendedDates.Travel Is Null,tblContact.Travel,tblAmendedDates.Travel)
AS TravelAmended, IIf(tblAmendedDates.NS Is
Null,tblContact.NS,tblAmendedDates.NS) AS NSAmended,
IIf(tblAmendedDates.Court Is Null,tblContact.Court,tblAmendedDates.Court) AS
CourtAmended, IIf(tblAmendedDates.Training Is
Null,tblContact.Training,tblAmendedDates.Training) AS TrainingAmended,
IIf(tblAmendedDates.StaffMtg Is
Null,tblContact.StaffMtg,tblAmendedDates.StaffMtg) AS StaffMtgAmended,
IIf(tblAmendedDates.Admin Is Null,tblContact.Admin,tblAmendedDates.Admin) AS
AdminAmended, IIf(tblAmendedDates.DSOther Is
Null,tblContact.DSOther,tblAmendedDates.DSOther) AS DSAmended,
tblContact.PostedDate, tblContact.DSTypeID, tblContact.OCTypeID,
tblContact.numID, IIf(tblAmendedDates.Notes Is
Null,tblContact.Notes,tblAmendedDates.Notes) AS NotesAmended
FROM tblContact LEFT JOIN tblAmendedDates ON tblContact.ContactID =
tblAmendedDates.ContactID;


Here is the query (that doesn't work) that I'm trying to use the above query
in:

SELECT QryAmendedContacts.ContactID, QryAmendedContacts.CaseID,
QryAmendedContacts.PostedDate AS [Date], QryAmendedContacts.F2FAmended,
QryAmendedContacts.TeamMtgAmended, QryAmendedContacts.CPCAmended,
QryAmendedContacts.CCAmended, QryAmendedContacts.OCAmended,
QryAmendedContacts.TravelAmended, QryAmendedContacts.NSAmended,
QryAmendedContacts.CourtAmended, QryAmendedContacts.TrainingAmended,
QryAmendedContacts.StaffMtgAmended, QryAmendedContacts.AdminAmended,
QryAmendedContacts.DSAmended, QryAmendedContacts.DSTypeID,
QryAmendedContacts.OCTypeID, QryAmendedContacts.NotesAmended,
QryAmendedContacts.numID
FROM QryAmendedContacts, tblDates
WHERE (((QryAmendedContacts.PostedDate) Between [tblDates]![StartDate] And
[tblDates]![EndDate]));


:

Tara -

I suspect you may have some criteria in the second query that doesn't take
into account the null values for the cases where there is data for the 'one'
table and not for the 'many'. We can only help if you post the SQL for both
queries...

--
Daryl S


:

I have a query set up with a one-to-many left join on two tables in order to
pull all records from one table (the "one" side) and the corresponding
records from another table (the "many" side), if they exist. It works well,
but only in THAT query. In other words, if I run that query, the records
show up as I want them to. But, if I then use that query in yet another
query to further manipulate the data, the only records that show up are the
records in the many table. Why and how can I fix it? I can post the query
if needed.

 

[Tara]

I can't believe it took three people to make me see this! I guess I'd just
been at it too long because when I came back in this morning and read the
comments over again, I realized immediately you were all right. I guess my
brain was just too overworked. It really needed the weekend off.

Thank you all so very much!

John Vinson also said the same thing so be sure to read his
reply. Maybe he explained it in a way that is more
understandable.
--
Marsh
MVP [MS Access]

I have to leave for the day soon and I won't be able to get back to this
until Monday morning. Thanks so much for all of your help and I hope I can
touch base with both of you again on Monday to pick your brains some more
about this issue.

.

 

[Bob Barrows]

I'm glad you've got your answer (actually, I'm a little puzzled about
the answer), but I just needed to throw this in:

SELECT tblContact.ContactID, tblContact.CaseID, tblContact.Date

You should stop using "Date" as a field name. It's a reserved keyword
(being the name of a VBA function) and its use as a field name will
cause you problems down the road. If it is not too late in the design
process, you should change that fieldname to something more meaningful
(ContactDate?) so as to avoid the reserved keyword issues.

 

[Bob Barrows]

Here is the query (that doesn't work) that I'm trying to use the
above query in:
FROM QryAmendedContacts, tblDates
WHERE (((QryAmendedContacts.PostedDate) Between
[tblDates]![StartDate] And [tblDates]![EndDate]));

As Daryl and Marshall said, you're using a criterion on the query
field PostedDate. For those records where no query record exists,
that field will be NULL - and NULL is in fact *not* Between those two
dates.

Try changing the WHERE clause to

WHERE QryAmendedContacts.PostedDate Between [tblDates]![StartDate] And
[tblDates]![EndDate] OR QryAmendedContacts.PosteDate IS NULL

I don't understand. PostedDate comes from tblContact, the left side of
the query:

..., tblContact.PostedDate, ...
FROM tblContact LEFT JOIN tblAmendedDates ON tblContact.ContactID =
tblAmendedDates.ContactID;

If it's not Null in the original table, why would it be Null in the
output of QryAmendedContacts?

 

[Bob Barrows]

FROM QryAmendedContacts, tblDates
WHERE (((QryAmendedContacts.PostedDate) Between
[tblDates]![StartDate] And [tblDates]![EndDate]));

As Daryl and Marshall said, you're using a criterion on the query
field PostedDate. For those records where no query record exists,
that field will be NULL - and NULL is in fact *not* Between those two
dates.

Try changing the WHERE clause to

WHERE QryAmendedContacts.PostedDate Between [tblDates]![StartDate] And
[tblDates]![EndDate] OR QryAmendedContacts.PosteDate IS NULL

Repro:
tblContact:
ContactID PostedDate
1 5/1/2010
2 5/10/2010
3 5/16/2010
4 5/17/2010
5 5/20/2010
6 5/19/2010


tblAmendedDates:
ContactID AmendedDate AmendmentText
1 5/7/2010 ttt
1 5/8/2010 t2
3 5/20/2010 t1
3 5/21/2010 t2
6 5/20/2010 t1


QryAmendedContacts,
SELECT tblContact.ContactID, tblContact.PostedDate,
tblAmendedDates.AmendedDate, tblAmendedDates.AmendmentText
FROM tblContact LEFT JOIN tblAmendedDates ON tblContact.ContactID =
tblAmendedDates.ContactID;
returns:
ContactID PostedDate AmendedDate AmendmentText
1 5/1/2010 5/7/2010 ttt
1 5/1/2010 5/8/2010 t2
2 5/10/2010
3 5/16/2010 5/20/2010 t1
3 5/16/2010 5/21/2010 t2
4 5/17/2010
5 5/20/2010
6 5/19/2010 5/20/2010 t1

and this query
SELECT QryAmendedContacts.ContactID, QryAmendedContacts.PostedDate,
QryAmendedContacts.AmendedDate, QryAmendedContacts.AmendmentText
FROM QryAmendedContacts
WHERE (((QryAmendedContacts.PostedDate) Between #5/15/2010# And
#5/20/2010#));

returns the expected result:
ContactID PostedDate AmendedDate AmendmentText
3 5/16/2010 5/20/2010 t1
3 5/16/2010 5/21/2010 t2
4 5/17/2010
5 5/20/2010
6 5/19/2010 5/20/2010 t1


What am I missing?