Two Database sets merge

[xaysana]

Dear there,

I have every recently finished most of the crucial parts. At the present, I just realize that one thing would bring me in major trouble.

In the PK field, it was defined as DataType: Number, Format 00000, I also utilized the DMAX code for autoincrementing values. However, it seams to be functional. Problem is that this database has now been used in two locations at the same time.

Form an Office locationed in Lao PDR. the PK was defined using 00000 as a DataType However the values would look likely to this (e.g 00001, 00002...) while another set in Vietnam was defined as 30000 (e.g 30000, 30001, 30002...) This will be going to be desperate disaster, once they are combined in one set.

As I run a quick test, when I imported few records from Vietnam set (example 30001, 30002, 30003, 30004 ). In table, they visually appeared duplicated to the existing records as 00001, 00002, 00003 and 00004.

What could be the solution to this?

Really appreciate for any kindly assistance, thank you in advance


With best regards,

 

[The_Doc_Man]

As long as neither is a "true" autoincrement field (i.e. you are incrementing through code), you can edit the records.

This will be a pain in the patootie, but you COULD do it via a multi-step operation.

1. BACKUP YOUR DATABASE - in case this screws up and you have to restart.

2. Import the second DB's tables to temporary tables in your first DB. Do not assert relational integrity among the temporaries even if it had been set for your first DB's corresponding tables. You do not need to import tables that are true duplicates from the second DB. (An example of the latter: If you use an abbreviation table for lookups of, say, standard abbreviations of state names... you only need one of those and the two tables should be the same already, no changes needed so no import needed.)

3. Establish a mapping formula that defines an offset for the key field. I.e. you determine that the highest number in the first table is 453 counts lower than the lowest number in the secondary table. So your formula might be new index = old index - 453. Now write individual queries that apply the exact same update formula to the key field for each temporary table holding that key.

4. Now append the (updated) records from the parent temporary table to the parent permanent table. Then append the (updated) child table records to their corresponding child tables on table at a time. Importing in the order parent first, child next assures proper observation of relational integrity if you were using it.

5. Verify by inspection that you got the desired result. If not, restore the backup and start over again.

6. Once success is confirmed, open the floodgates to your users.

 

[Simon_MT]

I would think about creating and establishing the Country and then:

DMax("[Invoice]", "[Sales]", "[Sale_Country] = " & [Forms]![Sales Entry]![Sale_Country]) + 1

Simon

 

[xaysana]

Hi The Doc Man,

Thank you very for your help, Im very much appriciated for that.

Unfortunately, Im really new to Access. I don't have any clue putting those into the action, could it be possible sending some short sample please?.

Thanky you very much in advance

With best regards,

 

[xaysana]

Please any advice

Hi The Doc Man,

Thank you very for your help, Im very much appriciated for that.

Unfortunately, Im really new to Access. I don't have any clue putting those into the action, could it be possible sending some short sample please?.

Thanky you very much in advance

With best regards,

What is the best solution to my situation, What should I define the field(s) in the table to admit those two data sets?

Thank you