Updating table data using Excel

[AnilBagga]

I have a database of over a 1000 customers. It has the usual address information, I now also need to add shipping information - Sea Port used for shipping

The City and country information unfortunately is text. Now to add the shipping information I created a table with all cities and ports with unique ID's.

How do I update my table of Customer Master with City ID and Port ID's efficiently? Manually means too much time and possibility of an error

 

[theDBguy]

If the names are exact mathes (or close enough), you might be able to join the tables.

 

[AnilBagga]

Unfortunately not. I tried. Got errors

 

[Uncle Gizmo]

I tried. Got errors

Isn't a very good description of the problem...

 

[AnilBagga]

Isn't a very good description of the problem...

The customer table 'tblCustMaster' has 2 fields - 'InlandCity' and 'DestPort' which are text fields. In the form I used cbo to select these names from the tblCityMaster. These were my initial foray in Access and did not realise future problems in this approach.

Since the text fields are creating problems in table relationships, I would like to replace the data in 'tblCustMaster' with the ID associated with InlandCity' and 'DestPort'

How can this be done efficiently?

 

[arnelgp]

make a copy of tblCustMaster table.
add those two (long integer) fields to your table.
use Update query to retrieve their values from tblCityMaster:

update tblCustMaster set [City ID] = Dlookup("ID", "tblCityMaster","InlandCity='" & [InlandCity] & "'"),
set [Port ID] = Dlookup("ID", "tblCityMaster","DestPort='" & [DestPort] & "'")

review your table. if satisfied with result, remove the InlandCity and DestPort columns.

 

[AnilBagga]

make a copy of tblCustMaster table.
add those two (long integer) fields to your table.
use Update query to retrieve their values from tblCityMaster:

update tblCustMaster set [City ID] = Dlookup("ID", "tblCityMaster","InlandCity='" & [InlandCity] & "'"),
set [Port ID] = Dlookup("ID", "tblCityMaster","DestPort='" & [DestPort] & "'")

review your table. if satisfied with result, remove the InlandCity and DestPort columns.

Thanks Arnel

Where are these Dlookup commands added? I have never used this Update query before

Will this be in the rowsource of these 2 new fields

 

[arnelgp]

you create a an Update query.
it is not the rowsource, it is to Update the 2 new fields with correct id.

 

[AnilBagga]

you create a an Update query.
it is not the rowsource, it is to Update the 2 new fields with correct id.

I never got around to do this and ultimately had to do it manually. Fortunately the update was needed in about 200 of the 1000+ records which i narrowed down by filtering the data

I have a much more complicated issue at hand now.

I have a 'ItemMastertbl' where the PK is an autonumber. There are 3 fields in the table which are relevant for the discussion. The no of data rows are over 4000

1. SerialNo (This is not the PK. The PK is called ID)
2. ErpFGCode
3. InternalCode

The InternalCode has changed in many cases due to change in the manufacturing process. This has been done manually in Excel after exporting the ItemMastertbl data.

Now we need to update the table InternalCode using ErpFGCode as the key reference field in the Excel file. How can build this UpdateQuery?

I tried looking it up online but the solutions I found involved VBA which I cannot follow