Multiple LEFT Joins w/ COUNT... getting really wierd results

[Dalt]

I have 3 tables that i'm trying to form a query on. 1 table has most of the
information, but I need to also query how many Issues are in an Issues table
and how many Assignments there are in an Assignments table.

SELECT AllRecords.SID,
AllRecords.RecordNum AS [Number],
AllRecords.ApprovalStatus AS [Status],
AllRecords.LOB, AllRecords.InstallStart AS [Install Begin],
AllRecords.InstallEnd AS [Install End],
Iif(Count([Assignments].RecordNumber) = 0, '',
Count([Assignments].RecordNumber)) AS [Assignees],
Iif(Count([IssueRecords].IssueRecord) = 0, '',
Count([IssueRecords].IssueRecord)) AS [Issues]

FROM ([AllRecords]

LEFT JOIN [Assignments] ON [AllRecords].RecordNum= [Assignments].RecordNum)
LEFT JOIN [IssueRecords] ON [AllRecords].RecordNum=
[IssueRecords].ParentRecord
GROUP BY AllRecords.SID, AllRecords.RecordNum, AllRecords.ApprovalStatus,
AllRecords.LOB, AllRecords.InstallStart, AllRecords.InstallEnd
ORDER BY Count([Assignments].RecordNum) DESC

The above query DOES execute, but I get wierd results.

1) If there are NO assignments in the Assignments table, I get the correct
number of Issues.

So lets say a record in table A has 5 assignments and 1 issue, the above
query results in 5 assignments and 5 issues.

2) If there are ANY Assignments for a record, then both the Issues and
Assignments count are equal to the number of Assignments.

If a record however has 0 assignments and 3 issues, it will correctly
display nothing for assignments and a 3 for issues.

Anyone help?

 

[Allen Browne]

Dalt, I've experienced similar things.

AFAICT, Access actually ignores the brackets in the FROM clause, and
conseaquently does not perform these multiple outer-join operations
reliably.

Try breaking it into 2 queries, and see if that gives the right results.

(I'm rushing out, so forgive me if I missed some other factor in your q.)