Solved. Changed the Not In to
(SELECT DISTINCT [AcctID] FROM [tblUnisysBiller] WHERE AcctID Is Not Null AND [Status] = 'Active' )
"A NOT IN query will not return any rows if any NULLs exists in the list of NOT IN values."
---In MS_Access_Professionals@yahoogroups.com, <runuphillracing@...> wrote :
The following SQL isn't working. The second part of the UNION is returning 0 records when it should return 5,724.
SELECT DISTINCT AcctID, Status
WHERE AcctID Is Not Null AND Status='Active'
UNION SELECT DISTINCT AcctID, Status
WHERE AcctID Not In (SELECT DISTINCT [AcctID] FROM [tblUnisysBiller] WHERE [Status] = 'Active' )
ORDER BY tblUnisysBiller.AcctID
I am trying to return list of all accounts with their status. In tblUnisysBiller, the key fields are AcctID and Device. An AcctID can be listed several times. If an AcctID has a Status of 'Active' at least once, I want it to return as 'Active'. If it does not have any 'Active' Status, it should show as 'Inactive'. Status is a text field with either 'Active' or 'Inactive' (that's how it comes in from an external source).
Example from Table:
This should result in
Posted by: email@example.com
|Reply via web post||•||Reply to sender||•||Reply to group||•||Start a New Topic||•||Messages in this topic (2)|