Change ID of imported table to Autonumber

[Kysteratwork]

Hi All,

I have a very important and sensitive question:

I have many tables linked together in all kind of ways and changing one information in one table will influence many other tables. Now my question:

If I reset the autonumber in one table (that is linked to numerous other ones) will the relationships and hence the related data be endangered?

The reason I ask: I had to export a table to excel in order to add a lot of new data and I imported it again. BUT now the ID appears as NUMBER. When I try to change it to AUTONUMBER, the error message says no can do, since already data in table.

It is very important I don't screw this up. Can you help me?

Kysteratwork

 

[Kysteratwork]

Actually, that is not going to work...

The situation is as follows:

I have a tblInvestorNames which I imported longer time ago. However, I forgot to import the respective COUNTRIES to the tbl. And now I am stuck, since the ID (autonumber) in tblInvestorNames are linked to other tables.

How can I import the respective countries and match them to the existing InvestorNames? I have over 2000 InvstorNames, so entering them manually would take me ages...

Maybe this clarifies my problem. An ideas?

Kysteratwork

 

[PHV]

Can't you play with an update query joining tblInvestorNames with the excel sheet on name+address ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ZmrAbdulla]

If you want to import respective countries to the tblInvestorNames as new field then you can use an update query, if you can find a matching field in both the tables( tblInvestorName & tblCountries).
Matching fields tblInvestorName[InvestorName],tblCountry[InvestorName]
HTH


Zameer Abdulla
[sub]Jack of Visual Basic Programming, Master in Dining & Sleeping[/sub]
Visit Me

 

[Kysteratwork]

I will try that - but can I add data to the TABLE tblInvestorNames like this? Or would the InvestorNames and Country only be together in the QUERY?

 

[PHV]

You already have added the country field in tblInvestorNames ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Kysteratwork]

Yes and they are all blank

 

[ZmrAbdulla]

Create a NewTable with the data from the excel sheet then use the SQL below to update the OldTable (with necessary changes in table & field names in the SQL)

UPDATE NewTable INNER JOIN OldTable ON NewTable.InvestorName = OldTable.InvestorName SET OldTable.InvestorCountry = NewTable!InvestorCountry
WHERE (((OldTable.InvestorName)=[NewTable]![InvestorName]));

hope this helps

Zameer Abdulla
[sub]Jack of Visual Basic Programming, Master in Dining & Sleeping[/sub]
Visit Me

 

[willir]

Kysteratwork

First, as suggested by PHV, you do not want to rename or re-import new records into tblInvestorNames because you may end up with having two investors for the one client.

You do want to UPDATE the Country field in tblInvestorNames with the correct country code.

There are several things that come to mind.

Option A) Use a key field / fields on the existing tblInvestorNames to decide the country.

For example the area code / country code for the phone number would be a possible field to use.

Then you can use a temp table, say tblTEMPCountryUpdate to be used as a look up table.

tblTEMPCountryUpdate
CountryCode
AreaCodeString
Country

primary key = Country + Area codes
[tt]
tblTEMPCountryUpdate
CountryCode Country AreaCode
1 USA 205 334 602 714 530 904 773 913 505 718...
1 Canada 204 250 316 403 416 514 519 905
61 Austrailia
32 Belgium
44 UK
33 France
49 Germany
39 Italy
91 India
52 Mexico
353 Ireland
...etc
[/tt]

You can obtain country and area codes here...
INTERNATIONAL CALLING CODES

Of course, you dont want to enter all the codes but by entering major locations, you should be able to "hit" most of your customers.

The real tough mix up would be separating the US and Canada since they both have the same "country code". For this specific reason, I added AreaCodes -- only way I can see to separate the regions.

With the lookup table created, you can now use the phone number prefix to determin the country. Sample code which would have to be taylored to your field names and needs might be someting like...

Dim rst as DAO.Recordset
Dim strCountry as String, strPhonePrefix as String, strQ as String

strQ = Chr$(34)

Set rst = CurrentDB.OpenRecordset("tblInvestorNames")

With rst
   .MoveFirst
   Do While Note .EOF
     If Not Len(Nz(!Country) Then

        If !CountryCode = "1" Then
           strPhonePreFix = "1 AND AreaCode = " & strQ & AreaCode & strQ
        Else
           strPhonePreFix = !CountryCode
        End If

        strPhonePreFix = "CountryCode = " & strPhonePreFix

        strCountry = DLookup("CountryCode", "tblTEMPCountryUpdate", _
        strPhonePreFix)

        If Nz(strCountry, "") Then
            .Edit
                !CountryCode = strCountry
            .Update
        End If

     End If
     .MoveNext
   Loop
End With

rst.Close
Set rst = Nothing

Assumptions for the above...
Field names on tblInvestorNames
- Include CountryCode and AreaCode, and both fields are text strings
- Country is the name of the target update field in tblInvestorNames

Option B) Re-import your data but directed it to a new table. The new table will include the country information. Use the new table to update tblInvestorNames country field.

 

[Kysteratwork]

ZmrAbdulla,

THAT WORKED!! Wow - I am sweating like a dog, but it works perfectly.

Thank you all for your help!!

Kysteratwork