Updating table data using Excel (1 Viewer)

AnilBagga

Member
Local time
Today, 16:18
Joined
Apr 9, 2020
Messages
223
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
Today, 03:48
Joined
Oct 29, 2018
Messages
21,358
If the names are exact mathes (or close enough), you might be able to join the tables.
 

AnilBagga

Member
Local time
Today, 16:18
Joined
Apr 9, 2020
Messages
223
Unfortunately not. I tried. Got errors
 

AnilBagga

Member
Local time
Today, 16:18
Joined
Apr 9, 2020
Messages
223
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

..forever waiting... waiting for jellybean!
Local time
Today, 18:48
Joined
May 7, 2009
Messages
19,175
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, 16:18
Joined
Apr 9, 2020
Messages
223
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

..forever waiting... waiting for jellybean!
Local time
Today, 18:48
Joined
May 7, 2009
Messages
19,175
you create a an Update query.
it is not the rowsource, it is to Update the 2 new fields with correct id.
 

AnilBagga

Member
Local time
Today, 16:18
Joined
Apr 9, 2020
Messages
223
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:48
Joined
Feb 19, 2002
Messages
42,981
Joining on text fields is not a problem.
You were never clear on what problem you were experiencing. "problem" covers a lot of ground. So, please remember that you need to tell us what error message you are getting.

For this new problem, it would have been better to start a new thread. Don't just reuse old threads.

To solve this problem, you will need a unique ID or combination of unique fields in each table on which you can join. So, to update InternalCode, you would join the two tables on SerialNo AND ErpFGCode. As long as the combination of the two values gets you to a unique row, this update query will be your solution.

If you don't have any unique data on which to join the two tables, how would you ever match the tables manually? If you can describe your matching logic in sufficient detail, we might be able to suggest a VBA solution but you can only use an update query if you have unique matches.
 

Users who are viewing this thread

Top Bottom