Getting/merging results from 2 tables

[Styler001]

I've got some tables I pulled off GeoNames.org that I need to extract data from. One table has everything I need except the counties listed in it. Instead of that, it gives a county code. The other table has the actual county names associated with those codes.

In the main table, country and state are listed similar to "US" and "WA", representing U.S.A. and Washington state, with the city name ("Seattle") also displayed in a separate field. A county may be displayed as a county code of 003 or 101, or any such 3-digit number. The thing is, multiple counties can have the same county code, since each state's counties start their numbering over at 001, 003, 005, etc.

In the county table, one column has the country, state, and county code in one field similar to US.WA.003, and the county name ("King County") in another field.

What I need to do with this info is create a new table that will have only the country, state, county, and city names (omitting the text "County", if possible). So, my final result would be: US, WA, King, Seattle

I just don't know how to pull the data from each table while merging the county information.

The reason I haven't included the examples of the tables I'm using is because I'm hoping to be able to put together the query myself, with the help you all can provide me with. Alot of times, there are well-intentioned people who are kind enough to layout everything exactly as you need it to come up with finished results, and I'd just like to get the opportunity to take a shot at it myself first. If I can't get it from there, believe me, I'll be back for more help.

Thanks in advance.

 

[boblarson]

Actually, you really shouldn't put that information in the table, if you already have it in another table, but with the id numbers. You can use a query to pull it all together for whatever purpose (you don't need to move it all into one table), all without modifying the structure of the database and, in the process, keep from moving into a denormalized mess.

 

[Styler001]

Well, the reason I was thinking of creating a new table (without all of the extra info that the other two tables have) is because I wanted to use that new table for other purposes as well, one of which is for importing to a MySQL database. I just think it'd be easier for me (personally) to work with a cleaned-up table.

 

[boblarson]

Well, it goes against normalization principles, and I wouldn't go there, but since it is data that isn't likely to need to be corrected as far as spelling or something (where you would have to go touch all records to fix it) then you can do it.

1. You will want to split out the data that is in one field by separating by using the Instr function along with the Left, Mid, and Right functions, respectively and creating a separate column for each.

For example for the Country you would use
Country:Left([YourOriginalFieldName],Instr(1,[YourOriginalFieldName],"."))


2. Once you have a query that separates those outJust add the fields necessary to the table you want it in and use an update query with linked together by the common field(s).

 

[Styler001]

Well, I'm having a heck of a time getting the county code field stripped down.

The field from the original file contains entries for all counties in all countries. It's not a field that is consistently in the form of xx.xx.xxx like I had originally thought. The form is: country, before the first period; state, between the first and second periods; county, after the second period. It's got entries like:

ES.29.M
BE.07.91
AT.01.101
CH.BL.1301
BG.38.BLG01
GR.51.LIMNOU
BR.01.1200013
GR.51.PLOMARIOU
GB.ENG.A1

and extremely long ones for Russia. The first two "codes" still take the form of xx.xx but the last part can be 20 or 30 long (also containing spaces).

The only thing that is consistent is the first part (country) before the first period. All entries for that portion are just 2 characters.

So, to pull out the country, I was able to use:

Country: Left([geoname ID],2)

I tried using variations of your suggestion with InStr with the Mid and Right functions. Instead of InStr for the Right function, I tried many forms using InStrRev. I couldn't get anything that resembled what I was looking for for the state and county portions, due to the variations in length.

I guess I'm at the point now where I need more help with this part, if you could, please.

 

[boblarson]

Can you post a db with the applicable parts?

 

[Styler001]

Here you go. Hope this helps.

 

[boblarson]

Here's your db revised with a function and a query that splits up the admin code to 3 fields. It will be up to you to make sense of it all, including the 3rd field which you said can be more than just county. But this will get you started.

 

[Styler001]

Thank you so much, Bob. You've been a great help. I guess this was a bit more involved than my very limited knowledge of Access could handle. I hope I can manage from here.

 

[Styler001]

Well, I got everything extracted (with your much-needed help). The only thing is, the data in the main GeoNames table is not as accurate as they might like to think it is. I get erroneous results because of their loose definition of cities. Turns out, the county I live in has about 90 towns/cities (according to GeoNames), rather than the actual 15-20.

But I really do appreciate your help. I couldn't have made it through without it.

 

[boblarson]

Glad we could at least help a little.