Query multiple values in field - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Query multiple values in field

Paul Kiener

3 years ago

There is a third-party table from which I need to extract data.  The table is from the FCC, listing demographic info for all licensed Amateur Radio Operators (Hams).  The table has over 1.5 million records.  My objective is to extract only those individuals who live in Morris County, NJ; however, the table does not have a county field.  My work-around is to use zip codes.  Morris County, NJ has quite a number of zip codes which makes my query/SQL statement quite bulky; it works, but if I made a mistake in setting up the query with a typo of a zip code, or if I would like to add additional zip codes, editing takes some very sharp eyes.  It would be great if the desired zip codes could be placed in a separate table, which then could be accessed (pardon the pun) by the query, but I don't know how to do this, or if there are any other suggestions.  Below is a portion of the SQL of the query:

SELECT EN.Field2 AS ID, EN.Field5 AS CALL, EN.Field8 AS FULLNAME, EN.Field9 AS FIRSTNAME, EN.Field10 AS MIDDLE, EN.Field11 AS LASTNAME, IIf([ADDRESS1] Is Null,"PO Box " & [ADDRESS2],(IIf([ADDRESS1] Is Not Null And [ADDRESS2] Is Not Null,([ADDRESS1] & ";  PO Box " & [ADDRESS2]),[ADDRESS1]))) AS ADDRESS, EN.Field16 AS ADDRESS1, EN.Field20 AS ADDRESS2, EN.Field17 AS CITY, EN.Field18 AS STATE, IIf(Len([Field19]>5),Left$([Field19],4),[Field19]) AS ZIP, EN.Field23 AS FRN
FROM EN
WHERE (((EN.Field18)="NJ") AND ((IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7960 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7840 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7054 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7920 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7869 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7801 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7866 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7950 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7928 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7405 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7834 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7940 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7005 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7836 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7853)) OR (((EN.Field18)="NJ") AND ((IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7828 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7885 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7932 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7444 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7438 Or (IIf(Len([Field19]>5),Left$([Field19],4),[Field19]))=7035 Or ... snipped for brevity.

Any help and/or direction would be appreciated!  Thanks

	John Davy @Reply 3 years ago
Hi Paul If you are familiar with Recordsets, (Rick's Recordset in VBA or Access Developer 15) or several others you could create the table you need and then loop through and perform the Select or Make Table as needed.

	Paul Kiener OP @Reply 3 years ago
John, thank you very much for your quick reply. No, I'm still learning and just recently came across the topic of Recordsets and need to do more study on this. Thanks for narrowing down the topic(s) for me. Now I have something to go on.

Kevin Yip @Reply

3 years ago

Hi Paul, If you store all the zip codes in a table named ZipCodesT, like this:

County            State  Zip
Morris County     NJ     07960
Morris County     NJ     07840
Morris County     NJ     07054
Morris County     NJ     07920
Morris County     NJ     07869
Bergen County     NJ     07601
Bergen County     NJ     07666
Bergen County     NJ     07024
Bergen County     NJ     07410
Bergen County     NJ     07026
Etc.

Then you can achieve your goal with just one relatively simple, but nested, query:

     SELECT EN.Field2 AS ID, ...
     FROM EN
     WHERE Left(Field19, 5) IN
          ( SELECT Zip FROM ZipCodesT WHERE County = "Morris County")


The WHERE clause above checks if a zip code is among the ones returned by the second SELECT statement.  This is essentially the same as:

    WHERE Left(Field19, 5) IN ("07960", "07840", "07054", "07920", ...)

A few notes:
1.  You don't need to check for state (e.g. EN.Field18)="NJ") because each zip code is unique to each state.
2.  Zip codes should preferably be stored as text because they aren't used for calculations and they have leading zero(es).
3.  A zip code can be up to 9 digits long (e.g. 07960-1234).  So you need to check only the first 5 digits against your zip code table.  Hence, I use Left(Field19, 5) above.

Paul Kiener OP @Reply

3 years ago

Kevin, thank you so very much!  Your suggestion looks very promising.  I'll have to give it a try tomorrow, as it is getting late.  Just to be sure, let me clarify that my ZipCodesT will need at least 2 fields (both County and Zip); State would not be necessary.

In regards to your notes:
1.  As I was composing my initial question, I noticed that I had the state, "NJ", in my query.  I was thinking that I really didn't need that since the operative is ZIP.  Thanks for pointing that out and confirming my thoughts.
2 & 3.  I agree that ZIP codes should be stored as text.  However, keep in mind that the FCC is a government agency and their ZIP codes are stored as numbers!!  (Go figure!)  Hence, "07960" is stored "7960", and the ZIP+4 is stored as "79601440", for instance.  That's why I had to do the code the way I did.

Again, thank you so very much for your response.  It'll be fun to see if I can get it to work!

	Paul Kiener OP @Reply 3 years ago
Kevin, the nested query worked great! Thanks again for the help. John, thank you very much for your suggestion as well. I checked out Richard's Access 320 and found it to be extremely interesting. Recordsets certainly does open up huge possibilities. I will have to continue to do more study along those lines.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View