Solved. Changed the Not In to
(SELECT DISTINCT [AcctID] FROM [tblUnisysBiller] WHERE AcctID Is Not Null AND [Status] = 'Active' )
From http://stackoverflow.com/questions/5231712/sql-not-in-not-working
"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
FROM tblUnisysBiller
WHERE AcctID Is Not Null AND Status='Active'
UNION SELECT DISTINCT AcctID, Status
FROM tblUnisysBiller
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:
| AcctID | Device | Status |
| A00001 | ABC1 | Active |
| A00001 | ABC2 | Inactive |
| A00002 | ABC3 | Active |
| A00003 | ABC4 | Inactive |
| A00003 | ABC5 | Inactive |
This should result in
| AcctID | Status |
| A00001 | Active |
| A00002 | Active |
| A00003 | Inactive |
Adam
Posted by: runuphillracing@yahoo.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar