Importing data to Access database with lookup tables

[adibakale]

I am fairly new to Access but starting to get the grasp of it.

I am creating an Access database and I need clarification on using lookup tables.

I import an excel file once a week to a temporary table. From there, I append the data to the main tables to seperate the information accordingly. I want to create additional tables - a CityTable and a StateTable. There are hundreds of records that get imported once a week and many Customers have the same City and State, so I would like to avoid repeating this duplicate information for every record.

My question is, When I append the data from the temporary table to the main tables, Would I append the City data to the main table, or to the City Lookup table?

Here is an example of my Customer table:

Primary Key
Account Number
Customer Name
Street
City
State
Zip Code

I would like to create these lookup tables:

tbl City
tbl State

So, when I append my temporary table to my Customer Table, would I append the City information to the Customer Table? Or too the City table? Same question for the State table.

Any help with this would be greatly appreciated.

 

[Kyle123]

You'd check if the state/city exist in their respective tables, if not add them.

You'd then add the primary key of the state/city to your customer table.

Fwiw though, I probably wouldn't normalising the cities unless I expected huge amounts in a small amount of cities. If that were the case you may add the state as a foreign key in the city table then simply join the city to the customer to get both

 

[adibakale]

"You'd check if the state/city exist in their respective tables, if not add them"

I created a StateTable and added all the state abbreviations and state names to it before importing the excel file.

For the StateTable, I used the State Abbreviation field for the Primary Key and connected it to the CustomerTable State field.

After importing my excel file, I appended the Customer State to the CustomerTable.

Is this the correct way to do it?

How do I append it in a way that it would check the StateTable and use the StateTable for the source of data?