ACCESS FEED: Re: [MS_AccessPros] Not In (SELECT

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

http://www.viescas.com/

(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: