SQL Help Needed - Revisited

[carl]

I posted yesterday but did a poor job of explaining my task. I'm trying
again. My apologies. Thank you all who tried to help.

I need a query that will do the following.

Return only the records that satisfies these 2 criteria.

1. UnderlyingName not in this list
('A','AA','AAPL','AIG','AMD','AMGN','AMZN','BAC','BMY','BSC','C','CAT','CFC')
And TradeType not equal to Pip

2. UnderlyingName not in the list above And Account not equal to Public.

Thank you again for your help.

 

[John Spencer]

Your explanation still confuses me.

Do you want records that are not in the list and TradeType not "PIP"
PLUS
the records that are not in the List and Account is Not Public


The problem that I see is that if that is what you want you could get some
records that are not in the list but that are "PIP", but have an Account that
is not Public and records that are not "PIP", but have an Account that is Public.

You might try the following

WHERE NOT(UnderLyingName In
('A','AA','AAPL','AIG','AMD','AMGN','AMZN','BAC','BMY','BSC','C','CAT','CFC')
AND (Account = 'Public'
Or TradeType = 'PIP'))

That shoule return recods that are not in the list, Not TradeType="PIP" and
not Account equal to Public.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[KARL DEWEY]

I posted the below yesterday but no response --
With this set of data is the Return column correct?
UnderlyingSymbol TradeType Return
A Pip Yes
AA Pipe No
AAPL Pip Yes
BIG Pip No
AMD Pip Yes
AMGN Pip Yes
AMZN Pip Yes
Or is backwards?

 

[carl]

Thank you all again.

My criteria has changed a bit. I tried to put togther an SQL to reflect. Is
there a way to use multiple Where statements inthe same SQL. In this case, I
need an OR operator between the two Where.


SELECT BTA_Trades_April.*
FROM BTA_Trades_April


WHERE (((BTA_Trades_April.UnderlyingSymbol) In
('A','AA','AAPL','AIG','AMD','AMGN','AMZN','BAC','BMY','BSC','C','CAT','CFC','COP','CSCO','DELL','DIA','DNDN','EBAY','EEM','EMC','F','FCX','FLEX','GE','GM','GS','HAL','HD','INTC','IWM','JAVA','JPM','LEH','MER','MNX','MO','MOT','MSFT','NEM','NYX','OIH','PFE','QCOM','QQQQ','RIMM','RIO','SBUX','SMH','SNDK','SPY','T','TGT','TXN','VLO','VZ','WFMI','WM','WMT','XLE','XLF','XOM','YHOO'))
AND ((BTA_Trades_April.TradeType)<>'Pip'))


Where Not ((BTA_Trades_April.UnderlyingSymbol) In
('A','AA','AAPL','AIG','AMD','AMGN','AMZN','BAC','BMY','BSC','C','CAT','CFC','COP','CSCO','DELL','DIA','DNDN','EBAY','EEM','EMC','F','FCX','FLEX','GE','GM','GS','HAL','HD','INTC','IWM','JAVA','JPM','LEH','MER','MNX','MO','MOT','MSFT','NEM','NYX','OIH','PFE','QCOM','QQQQ','RIMM','RIO','SBUX','SMH','SNDK','SPY','T','TGT','TXN','VLO','VZ','WFMI','WM','WMT','XLE','XLF','XOM','YHOO'))
AND ((BTA_Trades_April.AccountType)<>'PublicCustomer'));

 

[John Spencer]

SELECT BTA_Trades_April.*
FROM BTA_Trades_April


WHERE ((((BTA_Trades_April.UnderlyingSymbol) In
('A','AA','AAPL','AIG','AMD','AMGN','AMZN','BAC','BMY','BSC','C','CAT','CFC','COP','CSCO','DELL','DIA','DNDN','EBAY','EEM','EMC','F','FCX','FLEX','GE','GM','GS','HAL','HD','INTC','IWM','JAVA','JPM','LEH','MER','MNX','MO','MOT','MSFT','NEM','NYX','OIH','PFE','QCOM','QQQQ','RIMM','RIO','SBUX','SMH','SNDK','SPY','T','TGT','TXN','VLO','VZ','WFMI','WM','WMT','XLE','XLF','XOM','YHOO'))

AND ((BTA_Trades_April.TradeType)<>'Pip')))


OR (Not ((BTA_Trades_April.UnderlyingSymbol) In
('A','AA','AAPL','AIG','AMD','AMGN','AMZN','BAC','BMY','BSC','C','CAT','CFC','COP','CSCO','DELL','DIA','DNDN','EBAY','EEM','EMC','F','FCX','FLEX','GE','GM','GS','HAL','HD','INTC','IWM','JAVA','JPM','LEH','MER','MNX','MO','MOT','MSFT','NEM','NYX','OIH','PFE','QCOM','QQQQ','RIMM','RIO','SBUX','SMH','SNDK','SPY','T','TGT','TXN','VLO','VZ','WFMI','WM','WMT','XLE','XLF','XOM','YHOO'))

AND ((BTA_Trades_April.AccountType)<>'PublicCustomer')));

Changed second where to OR and then added parentheses around each set of criteria.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County