Criteria required for excluding multiple records in a query

[ollie1]

I have a table with an address field. There are multiple records under this field eg Smith Street, Brown Street, Paul Street, etc. I want to create a query that will exclude multiple records under the address field. Eg The query will exclude all the entries that have Smith Street and Brown Street under the address field in the result.


Ollie

 

[Transcend]

Are you asking what the query should look like?

Select * from table1 where address not in ('Smith Street', 'Brown Street')

Transcend

 

[ollie1]

Sorry I'll try and explain a little clearer, using Microsoft Access is a pretty new deal for me.
I know that if I want to exclude a single record from a query I can put a criteria in that says Not "Paul Street".

I don't know what to put in the criteria to exclude multiple records.

Hope this explains a bit better.

Cheers
Ollie

 

[BoffoSteve]

Hmm, it's not clear to me what you're after. Perhaps look up "DISTINCT", "DISTINCTROW", and "GROUP BY" in Help's Answer Wizard for some ideas?
Cheers!
Steve

 

[Transcend]

To put in multiple records arrow down after typing in Not "Pauls Street". (I assume you're talking about design view ? )

Just arrow down and type in the next criteria

Not "Paul Street"
Not "This Street"
Not "That Street" etc

Transcend

 

[ollie1]

Transcend
Thanks for the reply. Yeah I am in Design View. I had tried what you suggested , but it was unsuccessful. It would only exclude the first NOT record.

Steve

Thank you for your reply but I need a simple solution. I did what you said to lookup help but to be truthful I didn't understand it.

I just need a simple expression in the criteria section(in design view) to exclude a couple of the records from the field.

Thank you for your patience.

Ollie

 

[dkillilea]

It would be great to get an answer on this. I've tried to exclude mutiple items in a single query and it never works. Can someone please help us out in this area?

Thanks,
-Doug

 

[PHV]

The answer is in the first reply:
Not In ('value1','value2',....,'valueN')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dkillilea]

Yes! This does work!!! Thank you so much.

-Doug