Selasa, 26 April 2016

Re: [MS_AccessPros] Not In (SELECT

 

Adam-


Why are you using NOT IN?  First off, Access optimizes that very badly.  But it should have worked.

This should do it:

SELECT DISTINCT AcctID, Status

FROM tblUnisysBiller

WHERE AcctID Is Not Null AND Status='Active'

UNION SELECT DISTINCT AcctID, Status

FROM tblUnisysBiller

WHERE Status <> 'Active' 

ORDER BY tblUnisysBiller.AcctID

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Apr 26, 2016, at 6:14 PM, runuphillracing@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> 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:

AcctIDDeviceStatus
A00001ABC1Active
A00001ABC2Inactive
A00002ABC3Active
A00003ABC4Inactive
A00003ABC5Inactive


This should result in

AcctIDStatus
A00001Active
A00002Active
A00003Inactive


Adam





__._,_.___

Posted by: John Viescas <johnv@msn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (3)

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