Updating table data using Excel (1 Viewer)

AnilBagga

Member
Local time
Today, 04:49
Joined
Apr 9, 2020
Messages
92
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

I’m here to help
Staff member
Local time
Yesterday, 16:19
Joined
Oct 29, 2018
Messages
11,869
If the names are exact mathes (or close enough), you might be able to join the tables.
 

AnilBagga

Member
Local time
Today, 04:49
Joined
Apr 9, 2020
Messages
92
Unfortunately not. I tried. Got errors
 

AnilBagga

Member
Local time
Today, 04:49
Joined
Apr 9, 2020
Messages
92
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

error reading drive A:
Local time
Today, 07:19
Joined
May 7, 2009
Messages
10,253
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

Member
Local time
Today, 04:49
Joined
Apr 9, 2020
Messages
92
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

error reading drive A:
Local time
Today, 07:19
Joined
May 7, 2009
Messages
10,253
you create a an Update query.
it is not the rowsource, it is to Update the 2 new fields with correct id.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom