Query to combine data from 2 tables

[Knildon]

Hello Again,
I must be brain dead since I have a simple problem that I cannot get to work.
I have 2 tables. One has names, addresses, city, state, zipcode and other info. The second table has state, zipcode and county. I want to add the counties from table 2 into table 1. I've been playing with the different types of queries (simple and update) and get the counties to show up in table 1 except the number of records increases dramatically. 746,000 to over 1,000,000. I obviously don't know what I am doing and need some help. An example of the code would be great. Thanks in advance for any guidance.
Knildon

 

[Alansidman]

Try this:

SELECT Table8.tnames, Table8.taddress, Table8.tcity, Table8.tstate, Table8.tzip, Table9.ccounty
FROM Table8 INNER JOIN Table9 ON (Table8.tzip = Table9.czip) AND (Table8.tstate = Table9.cstate);

 

[Knildon]

Alan,
Thanks for the quick answer. I don't quite understand where I should use that code. I understand what it is doing and I'll probably have to add the rest of my info to get my table 1 filled in properly. In my mind, I thought the use of an access query would do the trick. It seems so simple - If table 2 zip = table 1 zip then table 1 county =table 2 county!!! I guess not. It's getting a little late on my end so I think I'll sleep on this one and start with a clear head in the morning.

Thanks again,
Don

 

[Alansidman]

This is the SQL code that sits behind the QBE (design grid). With your query open in design view, click in the upper left hand corner where you change your query view and there is a drop down, select SQL and you will see what you have for your SQL statement. Compare them and make the appropriate changes.

 

[Knildon]

Alan,
Thanks for that info. I knew there had to be some code back there somewhere but without any formal training I didn't know where to look.
It turns out your code and my code were very close but even after I corrected my code I was still getting over a million records. About 260,000 more than I started with. I did a lot of checking into the tables today and it turns out that the Counties table had a lot of blank spaces in the County column because of military zipcodes that don't have counties related to them. I filled in those blanks with the word military so the field has a name attached to the zipcode. I then ran the query again and now I get 65,000 less records than I started with.
After some more checking I found that my table 1 has zipcodes that don't appear in the Counties table. I used one of those Match/Don't Match queries. I don't know what to do, if anything, about that problem. Maybe some code will cure that.
I think I'm getting closer to my goal if I can figure out how to tell the query I need all of my records from table 1 even though they don't have a county in table 2.
If you have any thoughts on this, please pass them on. Meanwhile I'm going to keep pounding the keyboard to see if I can come up with something.
Thanks again,
Don

 

[Knildon]

Hello Again,
Just so no one wastes any more time on this problem, I found a cure for it. The query was trying to find matching zipcodes in 2 tables to obtain the name of a county from table 2 for table 1. Well, table 2 did not have a complete list of zipcodes so the query could not fully complete its job and gave erroneous information. Since table 2 should be a non changing data table, I decided to populate it with the missing zipcodes. I didn't have a county name to associate with the added zipcodes so I just used "UNK" for now. Eventually it will get updated.
With that said, the query now works as it was intended to and as I look back the real problem was with the table and not the query.
Thanks for the help.
Don

 

[Alansidman]

Glad that you figured it out.