Query Does not return all matching results

[Guest]

I have problem with a query not returning all matching records.

**** Design of Db******
I have three tables and three forms (Audits, Findings and Notes). Each form
feeds the namesake table.

I made the Notes form a subform of the Findings form as there are many notes
related to one finding and I wanted to datestamp notes. The Findings form
(w/ notes) is a subform of the Audits form as there are many findings within
any particular audit.

*****Issue details****
I created a simple query returning every field from the above tables. I
have no parameters set on the query, so it should return all the audits and
list the findings and any notes that apply.

It works Excellent as long as there is at least one set of notes for a
finding. If there are no notes for the finding, it will not show it. If an
audit has multiple findings, it only shows findings that contain a note. If
all the findings within an audit lack notes, the query does not return the
audit at all.

Testing reveals that if I occupy the notes field with a character, the audit
and finding will appear in the query. If I go back and delete the character,
the audit and finding continue to appear eventhough the notes field is blank.

I need the query to return all audits regardless of what may or may not be
in the notes field. Any help on this would be appreciated.

 

[Guest]

The query is returning all matching results. It just isn't returning what you
want! ;-)

You need a left join. Had you provided your SQL, someone could have tweaked
it for you. It would look something like this:

SELECT Audits.Audit_ID, [Notes]![Audit_ID_FK]
FROM Audits LEFT JOIN Notes ON Audits.Audit_ID = Notes.Audit_ID_FK;

 

[Guest]

I just learned how to view the SQL code - I didn't know it was available.

Here is what I have in the query that is not working as I would like.

SELECT Audits.AuditName, Audits.AuditDesc, Audits.AuditDate,
Audits.AuditOrg, Audits.AuditOrgContact, Audits.AuditStatus,
Findings.FindingNumber, Findings.FindingDesc, Findings.RespDir,
Findings.RespDep, Findings.RespDiv, Findings.RespPers,
Findings.InitialDeterm, Findings.[Due Date], Findings.FindingStatus,
Notes.Note, Notes.NoteDate
FROM (Audits INNER JOIN Findings ON Audits.SysGenAuditNumber =
Findings.AuditNumber) INNER JOIN Notes ON Findings.SysGenFindingNumber =
Notes.FindingNumber
ORDER BY Audits.AuditName;

The query is returning all matching results. It just isn't returning what you
want! ;-)

You need a left join. Had you provided your SQL, someone could have tweaked
it for you. It would look something like this:

SELECT Audits.Audit_ID, [Notes]![Audit_ID_FK]
FROM Audits LEFT JOIN Notes ON Audits.Audit_ID = Notes.Audit_ID_FK;
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have problem with a query not returning all matching records.

**** Design of Db******
I have three tables and three forms (Audits, Findings and Notes). Each form
feeds the namesake table.

I made the Notes form a subform of the Findings form as there are many notes
related to one finding and I wanted to datestamp notes. The Findings form
(w/ notes) is a subform of the Audits form as there are many findings within
any particular audit.

*****Issue details****
I created a simple query returning every field from the above tables. I
have no parameters set on the query, so it should return all the audits and
list the findings and any notes that apply.

It works Excellent as long as there is at least one set of notes for a
finding. If there are no notes for the finding, it will not show it. If an
audit has multiple findings, it only shows findings that contain a note. If
all the findings within an audit lack notes, the query does not return the
audit at all.

Testing reveals that if I occupy the notes field with a character, the audit
and finding will appear in the query. If I go back and delete the character,
the audit and finding continue to appear eventhough the notes field is blank.

I need the query to return all audits regardless of what may or may not be
in the notes field. Any help on this would be appreciated.

 

[Douglas J. Steele]

As Jerry said, change the INNER JOINs to LEFT JOINs.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I just learned how to view the SQL code - I didn't know it was available.

Here is what I have in the query that is not working as I would like.

SELECT Audits.AuditName, Audits.AuditDesc, Audits.AuditDate,
Audits.AuditOrg, Audits.AuditOrgContact, Audits.AuditStatus,
Findings.FindingNumber, Findings.FindingDesc, Findings.RespDir,
Findings.RespDep, Findings.RespDiv, Findings.RespPers,
Findings.InitialDeterm, Findings.[Due Date], Findings.FindingStatus,
Notes.Note, Notes.NoteDate
FROM (Audits INNER JOIN Findings ON Audits.SysGenAuditNumber =
Findings.AuditNumber) INNER JOIN Notes ON Findings.SysGenFindingNumber =
Notes.FindingNumber
ORDER BY Audits.AuditName;

The query is returning all matching results. It just isn't returning what
you
want! ;-)

You need a left join. Had you provided your SQL, someone could have
tweaked
it for you. It would look something like this:

SELECT Audits.Audit_ID, [Notes]![Audit_ID_FK]
FROM Audits LEFT JOIN Notes ON Audits.Audit_ID = Notes.Audit_ID_FK;
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have problem with a query not returning all matching records.

**** Design of Db******
I have three tables and three forms (Audits, Findings and Notes). Each
form
feeds the namesake table.

I made the Notes form a subform of the Findings form as there are many
notes
related to one finding and I wanted to datestamp notes. The Findings
form
(w/ notes) is a subform of the Audits form as there are many findings
within
any particular audit.

*****Issue details****
I created a simple query returning every field from the above tables.
I
have no parameters set on the query, so it should return all the audits
and
list the findings and any notes that apply.

It works Excellent as long as there is at least one set of notes for a
finding. If there are no notes for the finding, it will not show it.
If an
audit has multiple findings, it only shows findings that contain a
note. If
all the findings within an audit lack notes, the query does not return
the
audit at all.

Testing reveals that if I occupy the notes field with a character, the
audit
and finding will appear in the query. If I go back and delete the
character,
the audit and finding continue to appear eventhough the notes field is
blank.

I need the query to return all audits regardless of what may or may not
be
in the notes field. Any help on this would be appreciated.