Left Join Not Working (1 Viewer)

[ErinL]

Hello - I have the following SQL in a query...

SELECT qryFlashHoursRP.WorkDate, qryFlashHoursRP.WeekEnding, qryFlashHoursRP.ReportOrder, qryFlashHoursRP.WorkCategorySubGrouping, qryFlashHoursRP.Hours, qryAdditionalGenOpsHours.AdditionalHours, Nz([Hours])+Nz([AdditionalHours]) AS RPHours
FROM qryFlashHoursRP LEFT JOIN qryAdditionalGenOpsHours ON (qryFlashHoursRP.WorkCategorySubGrouping = qryAdditionalGenOpsHours.WorkCategorySubGrouping) AND (qryFlashHoursRP.WorkDate = qryAdditionalGenOpsHours.WorkDate);

But the results are not correct. Even thought I have a left join on the two queries the results only show the records where the field "WorkCategorySubGrouping" match.

Can anyone shed some light as to what I have wrong in this query? I have another query with different queries but the same design and it works fine so I'm confused.

Thank you in advance!

 

[plog]

...the results only show the records where the field "WorkCategorySubGrouping" match.

...  (qryFlashHoursRP.WorkCategorySubGrouping = qryAdditionalGenOpsHours.WorkCategorySubGrouping)...

Well, yeah, that's how you set it up. The best way to communicate these issues is with data. Please provide 2 sets of data:

A. Starting data from your table(s). Include table and field names as well as enough data to cover all cases.

B. Expected results of A. Show what data you expect to end up with when you feed the data A into your query.

Again, 2 sets of data--starting and expected results.

 

[Isaac]

But the results are not correct. Even thought I have a left join on the two queries the results only show the records where the field "WorkCategorySubGrouping" match.

That does sound odd, if everything is just as you've described, and all else being equal.

1. Run qryFlashHoursRP by itself, no where clause. What's the total recordcount?
2. What's the recordcount of that SQL you posted when you run it?
3. Can you post a zipped copy of the database with just enough data in it to reproduce this scenario?

 

[CJ_London]

Suspect one or both of your date fields contains a time element hidden by formatting. To test set the format property in each query to include the time element

 

[The_Doc_Man]

But the results are not correct. Even thought I have a left join on the two queries the results only show the records where the field "WorkCategorySubGrouping" match.

Are you saying that you get a matched record where your qryFlashHoursRP.WorkDate <> qryAdditionalGenOpsHours.WorkDate? I would have thought that to not be possible. A two-field exact-match JOIN shouldn't return mismatches.

 

[Isaac]

I assumed OP to be emphasizing Left vs. Inner join. That he believes the query results are what he would have expected from an inner join, ("just the records that matched on the join fields) rather than what he expected from a left join (all records from the table on the left, regardless of match on join fields). Hopefully OP will come back soon and clarify.

 

[ErinL]

Hello all - Thank you all for the responses.

Isaac - when qryFlashHoursRP is run by itself there are 8 records returned. When the query I posted is run it only returns 1 record (the one where the WorkCategorySubGrouping field matches with GenOps).

Below are the 8 records qryFlashHoursRP returns:

WorkDate	WeekEnding	WorkCategoryGrouping	WorkCategorySubGrouping	Hours
6/18/2021	6/19/2021	Direct	Receiving	7.19
6/18/2021	6/19/2021	Direct	Putaway	22.35
6/18/2021	6/19/2021	Direct	Letdown	63.32
6/18/2021	6/19/2021	Direct	Selecting	344.9
6/18/2021	6/19/2021	Direct	Loading	107.3
6/18/2021	6/19/2021	Direct	Gen Ops	61.96
6/18/2021	6/19/2021	Direct	Non Production Direct	30.43
6/18/2021	6/19/2021	Indirect	Indirect	3.34

Only one of these records (GenOps) contains additional hours in qryGenOpsAdditionalHours on the same date of 6/18/21.

This is the result I get:

WorkDate	WeekEnding	WorkCategoryGrouping	WorkCategorySubGrouping	Hours	AdditionalHours	RPHours
6/18/2021	6/19/2021	Direct	Gen Ops	61.96	22.11	84.07

This is the result I expected and want:

WorkDate	WeekEnding	WorkCategoryGrouping	WorkCategorySubGrouping	Hours	AdditionalHours	RPHours
6/18/2021	6/19/2021	Direct	Receiving	7.19	0.00	7.19
6/18/2021	6/19/2021	Direct	Putaway	22.35	0.00​	22.35
6/18/2021	6/19/2021	Direct	Letdown	63.32	0.00​	63.32
6/18/2021	6/19/2021	Direct	Selecting	344.89	0.00​	344.89
6/18/2021	6/19/2021	Direct	Loading	107.33	0.00​	107.33
6/18/2021	6/19/2021	Direct	Gen Ops	61.96	22.11	84.07
6/18/2021	6/19/2021	Direct	Non Production Direct	30.43	0.00​	30.43
6/18/2021	6/19/2021	Indirect	Indirect	3.34	0.00​	3.34

 

[The_Doc_Man]

Your query includes a JOIN on a field that might be null. I think that in essence you have over-constrained the JOIN.

FROM qryFlashHoursRP LEFT JOIN qryAdditionalGenOpsHours ON (qryFlashHoursRP.WorkCategorySubGrouping = qryAdditionalGenOpsHours.WorkCategorySubGrouping) AND (qryFlashHoursRP.WorkDate = qryAdditionalGenOpsHours.WorkDate);

Would there be a work date in qryAdditionalGenOpsHours if there was no additional work? If the answer is NO then you are seeing things exactly as you asked for. (This might be a case where Access does EXACTLY as you told it to do rather than what you WANTED it to do.)

 

[Isaac]

Your query includes a JOIN on a field that might be null. I think that in essence you have over-constrained the JOIN.

FROM qryFlashHoursRP LEFT JOIN qryAdditionalGenOpsHours ON (qryFlashHoursRP.WorkCategorySubGrouping = qryAdditionalGenOpsHours.WorkCategorySubGrouping) AND (qryFlashHoursRP.WorkDate = qryAdditionalGenOpsHours.WorkDate);

Would there be a work date in qryAdditionalGenOpsHours if there was no additional work? If the answer is NO then you are seeing things exactly as you asked for. (This might be a case where Access does EXACTLY as you told it to do rather than what you WANTED it to do.)

That's a great point. Extra predicates on a left join, for example, can sometimes cause a left join to behave kind of like an inner join. At least in SQL
And if you're joining on a field that may be Null, which shouldn't be done, then maybe a sort of similar behavior/result is what's coming out of that.

I don't know how well Access handles creative joins, like (on table1.fieldname=nz(table2.fieldname,'99999') or something, if that's an option or not.

Because I've never tried to join on a field that may contain a null.

 

[The_Doc_Man]

Because I've never tried to join on a field that may contain a null.

Just as well that you didn't, because the ON x.y = w.z syntax doesn't join if either or both of the records contain nulls in those fields. Remember that a null is not EVER equal to anything - including another null.

 

[ErinL]

The null was the problem. I broke it into two separate queries and then create a union query to combine and see all the records.

Thank you both for your help!!

 

[The_Doc_Man]

Sounds like you have a workable solution. Good result and good luck with your project.