Need to filter a query by over 2000 different criteria in one field

[sazi22]

I have a table with about 65,000 records and need to filter out any records with specific zip codes - there are over 2000. What's the easiest way to do this?

 

[jsteph]

You should build a zipcode table with a Choose/unchoose (or similar) field, then check the choos/unchoos for all the ones not chosen, then join that table based on the zip code and the choose value
--jim

 

[hneal98]

put the zip codes in a table as a lookup table and then reference the table in your query. When you set up your query, you show both your main table and the look up table and link on the zip code as an inner join.

Let me know if you need more info.

 

[sazi22]

I do need more info on both solutions....

Thank you!

 

[jsteph]

...I hit submit too soon--I should add that the reason for the choose/unchoose field (btw, don't name it that) is my assumption that you'll be changing the criteria--if you have a single table with, say, all US zipcodes, it may be easier to just keep that full and simply check/uncheck the choose field.

Performance shouldn't really be an issue--the zip table can be indexed so that regardless of whether it has just the 2000 zips or 100,000 with just 2000 'chosen', the performance won't be significantly affected.
--Jim

 

[hneal98]

What part don't you understand? I am kind of shooting in the dark here because I don't know what you get or don't get.

I am assuming you know how to build a table and how to build a query. With that, you build your ZipCode table. It only needs one field, the ZipCode field.

When you build your query, put add the table with the 65,000 records, and the ZipCode table. Link the two tables by the Zip Code. (I am also assuming the big table contains a Zip Code field.)

Double click on the Link Line and a dialog will pop up that shows the field(s) you are linking as well as give you a choice to select 1, 2, 3. You would select 1.

That would take care of your criteria.

 

[hneal98]

jsteph's idea is best if, in fact, you do need to choose or unchoose each zipcode.

 

[sazi22]

As of now, the criteria won't be changing. Would that make it easier? He's some more detail.

Each month, I will get a file of about 65,000 property records. I need to exclude any records located in hurricane impacted areas - as defined by the 2000+ zipcodes.

 

[hneal98]

OK. If you need to exclude those items, you will need to select 2 and then in the criteria row of the query under the zipcode field in for the big table, you need to put NULL.

 

[lespaul]

create a table with the 2000 zipcodes that need to be excluded.

SELECT * FROM BigTable INNER JOIN ZipCodeTable on BigTable.ZipCode = ZipCodeTable.ZipCode

there you go



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

In fact:
SELECT B.*
FROM BigTable B LEFT JOIN ZipCodeTable Z On B.ZipCode = Z.ZipCode
WHERE Z.ZipCode Is Null

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

 

[hneal98]

lespaul, that would not work becuase in your query you are including them, not excluding them. You would need to do a left outer join and in the where clause put something like where BigTable.ZipCode is null. the code would like like this:

SELECT * FROM BigTable LEFT JOIN ZipCodeTable on BigTable.ZipCode = ZipCodeTable.ZipCode
WHERE isnull(ZipCodeTable.ZipCode) = True

 

[lespaul]

my bad...

 

[sazi22]

Thank you! It worked... but now there is another issue... in the BigTable, some records have zipcodes with the 4 digit extension and some don't. The zipcode file doesn't have any extensions. Is there anyway to use a wild card with this?

 

[PHV]

Something like this ?
SELECT B.*
FROM BigTable B LEFT JOIN ZipCodeTable Z On Left(B.ZipCode,5) = Z.ZipCode
WHERE Z.ZipCode Is Null

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