sql question?

Question

My sql right now is

SELECT ariInfo.[Part Qualifier], ariInfo.[Part Number], ariInfo.Description, ariInfo.[List Price], ariInfo.Stock, ariInfo.[Supercession or Alternative Part Number], ariInfo.[Part Weight], ariInfo.[Price 1], [Price 1]*0.95 AS [Price 2], [Price 1]*0.9 AS [Price 3], [Price 1]*0.85 AS [Price 4], [Price 1]*0.8 AS [Price 5], ariInfo.[Price 6], ariInfo.[Price 7], ariInfo.[Price 8], ariInfo.[Price 9], ariInfo.[Price 10], ariInfo.[Box Height], ariInfo.[Box Width], ariInfo.[Box Length], ariInfo.[Handling Type], ariInfo.[Handling Value]
FROM ariInfo
WHERE (((ariInfo.[Part Qualifier]) In ('SIL','TH')));

I need to ad something like
AND (Not (ariInfo.[Part Number])=('1') so that this query excludes certain part numbers. If i try to add more than 1 number (ex ('1','2') I get an error. I have about 160 part numbers I need to exclude. How can I do this?

Answer 1

how about
AND ariInfo.[Part Number] Not in ('1','2','3','4')

Answer 2

Since you have 160 part numbers to exclude, there must be something special about them, right? It might not technically be the most efficient, but I'd create a table to store those part numbers (or add a field somewhere to signify those parts you want to exclude) and change the query to have the following condition:

AND NOT EXISTS (SELECT * FROM table_with_excluded_parts WHERE partNo = arilInfo.[Part Number] [AND whatever else you need to find those part numbers, if you didn't make a new table]);

I wasn't aware that one could use brackets like that in SQL. You should definitely be able to get by without it. Anyway, this method is better than typing all those part numbers into the query. Good luck!

Answer 3

anonymous is right, you have too many items to exclude so make a new table and check against them, besides exists you can do a comparison with ariInfo.

[Part Number])!=ALL( -- with your new table query to get all the part numbers you dont want --)

Answer 4

Instead of: "AND (Not (ariInfo.[Part Number])=('1', '2','3')" use "AND (Not (ariInfo.[Part Number])=('1' OR '2' OR '3')"