Selasa, 26 April 2016

[MS_AccessPros] Not In (SELECT

 

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:

AcctIDDeviceStatus
A00001ABC1Active
A00001ABC2Inactive
A00002ABC3Active
A00003ABC4Inactive
A00003ABC5Inactive


This should result in

AcctIDStatus
A00001Active
A00002Active
A00003Inactive


Adam


__._,_.___

Posted by: runuphillracing@yahoo.com
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)

Upgrade your account with the latest Yahoo Mail app
Get organized with the fast and easy-to-use Yahoo Mail app. Upgrade today!


.

__,_._,___

Tidak ada komentar:

Poskan Komentar