Help with SQL statement. Not all records displaying

[Montez659]

The following is my SQL:

SELECT qryAllAudits.auditID, qryAllAudits.auditorName, qryAllAudits.auditDate, qryAllAudits.auditProvName, qryAllAudits.auditProvID, qryAllAudits.dateAuditRecd, qryAllAudits.dateRepCompleted, qryAllAudits.dateRepRecd, qryAllAudits.auditType, qryAllAudits.repAudited, qryAllAudits.delEntity, qryAllAudits.newHireAudit, qryAllAudits.markAsNoErrors, qryAllAudits.extraNotes, qryAllAudits.contactID, qryAllAudits.hideMe, qryAllDings.mainCatID, qryAllDings.mainCatSubCatID, qryAllDings.subCatID, qryAllDings.auditDingsID, qryAllDings.noLongerValid
FROM qryAllAudits LEFT JOIN qryAllDings ON qryAllAudits.auditID = qryAllDings.auditID
GROUP BY qryAllAudits.auditID, qryAllAudits.auditorName, qryAllAudits.auditDate, qryAllAudits.auditProvName, qryAllAudits.auditProvID, qryAllAudits.dateAuditRecd, qryAllAudits.dateRepCompleted, qryAllAudits.dateRepRecd, qryAllAudits.auditType, qryAllAudits.repAudited, qryAllAudits.delEntity, qryAllAudits.newHireAudit, qryAllAudits.markAsNoErrors, qryAllAudits.extraNotes, qryAllAudits.contactID, qryAllAudits.hideMe, qryAllDings.mainCatID, qryAllDings.mainCatSubCatID, qryAllDings.subCatID, qryAllDings.auditDingsID, qryAllDings.noLongerValid
HAVING (((qryAllDings.noLongerValid)<>True));

Big and hairy? Yes, I know. But it's really not that bad. The problem that I seem to be having is that all records from qryAllAudits should display regardless of whether there is a record in qryAllDings, but this does not seem to be happening. What am I missing?

It is a Left Join, but it is acting like an Inner Join.

 

[Joe4]

I think it is you "HAVING" clause that is dropping them.
What happens if you get rid of that?

If there are records from your qryAllDings that you do not want, I would drop them before joining them with qryAllAudits (in a subquery).

 

[Montez659]

Joe, I think you got it!

I couldn't drop it completely, but I did put it in a subquery and it seems to like that better.

Thanks for the quick response!

 

[Joe4]

Glad it worked out.

Yes, the "HAVING" clause criteria would be applied to your total result set AFTER the Left Join, so it would drop records. That is why you would need to exclude the records from that data source before the Left Join (in a subquery) if you truly want all the records from the Left side of the join in your final result set.

 

[Montez659]

Yeah, I can see that now. In hindsight it makes perfect sense, but to be honest, I am just now getting used to looking at the actual SQL and in Access' query builder, I would have never seen it.

'Course, I guess I didn't see it in the SQL either!

 

[Joe4]

Once you get used to seeing the SQL code, believe it or not, sometimes it makes more sense, especially in determining the order of how things get processed when using things like Aggregate Queries or Sub-Queries. It can be hard to analyze more complex queries in the Query Builder.

I used to think it was all Greek to me (the SQL code) until we got a program which uses SQL. Then I learned how to write SQL directly, and it is amazing how quickly you adapt. The one thing I don't like about Access SQL View is that you cannot indent/format the layout of the code. So it can be very hard to read. I often will copy and paste it in Word or a Text file and indent it for readability when I am trying to analyze it.