Data Correction

[Vu_Ho]

By mistake, I have split a table into two different ones, whereas the Customers' City fell into Customers' State Table (it should be in Customers' Details Table). Please tell me is there any way to correct this mistake (I don't want to retype all the Cities)? Thanks a bunch

 

[RainLover]

You can use an append query to fix this.

Fairly simple if you use the query grid. On the menu change from Select to Append.

 

[Pat Hartman]

Probably an update query rather than append. First you need to add the city column to the table you want to move the data to. Then create a query that joins the table you want to update to the table where the data exists currently. Turn it into an update query. Once you have populated all the city columns, make another backup (you did make one be for you ran this update query I hope). Then delete the city column from the state table. This will probably require you to delete lots of rows from the state table now that city is gone.

The above is a pretty simplistic description. The actual procedure may be more complicated if the PK of the state table is stored in your main table because after you have removed all the cities, you'll have a lot of duplicate state records and you'll need to reduce those to one state. Personally, unless I am creating a huge database, my state table uses the state abbreviation as its PK rather than an ID which always has to be translated. I don't think in our entire history a state has actually changed its name so that isn't much of an issue. I think we'll see Texas as a separate country first

 

[Vu_Ho]

Problem is I already created a Lookup field that already included the city in the field. That also included the customers' zip code

 

[Pat Hartman]

You may as well get rid of it sooner rather than later. The lookup will cause nothing but trouble. Cut your loses now.

 

[RainLover]

Probably an update query rather than append.

My mistake.

Of course an update would be proper, not append as I wrote.

Could I get away with saying that this was a typo.

 

[Pat Hartman]

Of course you can