D
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?
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?