3 tables - combine to 1 - corresponding info

[taylornow]

Good morning -

I need assistance on combining of tables. Okay - here goes

1st table - City and corresponding County
2nd table - County with corresponding Division#
3rd table - Division# with corresponding Dept Code#

Need it to be all in one place, be it Access or exported to Excel, for look-up purposes. When looking up by City the County, Division, and Dept. Code will be there also.
Any help or advice would be much appreciated.

Thanks in advance
Taylor

 

[Trendsetter]

Make a query linking all three tables by the related fields and check that all of the records are there.
City table 1
County table 1
Division table 2
DeptCode table 3

There should be the same number of records in each table as well as the output from the query!

Once this is OK - change the query type to a "make table" query and run it again - name it - save it - all done!!!

 

[coco86]

link your tables like this

[table1].[county] <-> [table2].[county]
[table2].[Division] <-> [table3].[Division]

And then use the fields the way Trendsetter described.

I wouldn't convert the query to a maketable query though. If you want to use it as a lookup in Access, a query works fine and you don't have to worry about updating the combined table everytime the individual tables change. If you want to export to Excel, you can do that from the query as well as long as it has been saved.

 

[taylornow]

Have I said lately how great this site is????
The combining worked like a dream. I will put in in Excel for the staff using it and will keep in Access for me.

You guys are the best. Thanks so much. It has been printed and hole punched and put in the notebook for the next time I have a brain freeze.

Thanks again
Taylor