Fred,

I only had to read this a couple times, but I actually got the concept and
think I have a shot a getting this. Thank you so much

Fred wrote:
County names are often repeated between states. And so you have inherent
ambiguity in your one table if it lists multiple states and multiple counties
as it does not say which state each county is in. It probably relies on
human interpretation based on other factors. This really isn't a database
table structure. For example, if it lists IL and MN, and Llake county is
smongst the counties, is that Lake COunty IL, or Lake County MN?

Also, to clarify, a "match" in a county meas a match of both the county name
and it's state's name, not just the county name.

If it feasible, you should turn that table into a normalized DB table
structure. If not, of if you just wanted a narrower fix, you could just
execute a query which looks for the state being within the string of states
listed in the field, and the county name being within the string of county
names in the field. This process would be limited by the inherent ambiguity
described above.

To put it into a real DB table structure, we would need to know whether
those 240 records include repetions of the programs. For now let's assume
not, that your table is a listing of 240 programs plus the ambiguous listing
of the states/counties where they occur. In this case (change/shorten names
as desired) make a program tabl with 240 records, same as your current table
except no state or county info, and add a ProgramIDNumber Autonumber PK
field.

Now make a table which has a record for each instance of the program
applying in a state. Include a FK ProgramIDNumber, The state abbreviation,
and a PK StateInstanceID. Now make a table for each instance of a program
applying in a county. FK StateInstanceID, and CountyName. (add an
optional PK if you want). Link the FK's to theri namesake primary keys.

Make a query that has all of the fields from the above tables plus a colum
which concentates the County&State. Make a query from your company table
which concentates it's county & state. Linke these concantated colums
together. The result will show all applicable programs for every company.

I'm good at unscrambling situaitons and structure, but much weaker than
other responders on the other Access developer stuff. They may know a
better way to handle the latter part of my post.

--
Message posted via http://www.accessmonster.com