Flat database into multiple tables

[Tinkerers]

Hi, I have an Access 97 database with 1 large table. In this table, there is a field called "State". Of course, there are lots of the same states throughout all the records. In an effort to reduce the size of the database, I'd like to put all the states into a separate table, and create a lookup field in my original table, that will look to the 2nd table to get the state value.

What would the query look like that will accomplish this ? There are over 50,000 records, so I can't do it by hand.
Let's call the main table "Assets" and the state table "State".

Any help would be appreciated !!
Thanks,
Paul

 

[GoinDeep]

Are the States Abbreviated ie CA, AZ, NY right now, or whole names?

 

[Tinkerers]

They are whole names.

 

[GoinDeep]

I have a table of States with the Abbreviation as the Primary that I could send you. Then you could make a Query using your table and mine with the state Abbreviations added, and then do a "Make Table Query"...or something like that...let me know an email address and I will send it to you.

 

[Tinkerers]

My email is broadband100@hotmail.com

I'm curious what the Make Table query would look like. My problem is with the lookup part.

Thanks,
Paul