How to synchronize table data records

You may come up with a new problem - it table A has 1 record for customer and table B has 2 records for the same customer - for example it the customer has 2 phone numbers (different).

So think which tables you can join on "NewBigTable" and use "INSERT INTO ... SELECT" statement as geobul suggested.

Yep, that's true. My proposal would work only if your tables contain one record per customer and there is a record for every customer. (I hope it will work, actually, because the SQL statement is very large and complicated and BDE might refuse its execution. I don't know).

If that's not true then I'd make a program which will open first table, loop through its records, check if that custno already exists in the BigTable and if not - add a new record filling some fields (custno and one with data, name for instance). And the same for all other tables which contain one record per customer - if custno doesn't exist then add a new record, otherwise fill a field of the existing record with data (addres from Table2, phone number from Table3, etc.). Tables which contain more than one record per customer should not be added but remain as separate ones.

Regards, Geo

You will also need to deal with conflicting information when table  A has one phone number for customer X and Table  b has a different phone number for customrer X, You don't want to lose the good number.  So in creating the one big table you are going to have decide on either a hierarchy under which fields from table A are more valid than table B which is more valid than table C etc.  If there is no overlap and no conflict it is very straightforward and the answers above will give you what you need.  But in that case a massive Join query will be enough to create a master table and then restructrure it so that the customer number is the key field.

A second way is to use two steps create the master table with duplicates if they exist, and then create a second table from the first which using a unique select on the customer number field.

None of us know what is in the ten tables.  If it is a normalized relational database than converting it to a flat database is probably not a good idea anyway.  It would make more sense to create a Flat View, using a query or form, in that case.

A relational database designed correctly will lead to fewer problems and quicker and more efficient updates than a flat database.

hi geo, Ivanov, LSORRELLS,

I've tried your suggestions but I think I'm to stupid (brainwashed again). My last attempt was the following, but without a result.

INSERT INTO "AllAdr.DB" (NR,KDM,UID,STNR,KDART,NAME,ADRESSE1,ADRESSE2,ADRESSE3,ADRESSE4,PLZ,ORT,BLZ,KTONR,BANK)

SELECT A.NR,A.KDM,A.UID,A.STNR,A.KDART,A.NAME,A.ADRESSE1,A.ADRESSE2,A.ADRESSE3,A.ADRESSE4,A.PLZ,A.ORT,A.BLZ,A.KTONR,A.BANK,B.NR,B.KDM,B.UID,B.STNR,B.KDART,B.NAME,B.ADRESSE1,B.ADRESSE2,B.ADRESSE3,B.ADRESSE4,B.PLZ,B.ORT,B.BLZ,B.KTONR,B.BANK,C.NR,C.KDM,C.UID,C.STNR,C.KDART,C.NAME,C.ADRESSE1,C.ADRESSE2,C.ADRESSE3,C.ADRESSE4,C.PLZ,C.ORT,C.BLZ,C.KTONR,C.BANK,D.NR,D.KDM,D.UID,D.STNR,D.KDART,D.NAME,D.ADRESSE1,D.ADRESSE2,D.ADRESSE3,D.ADRESSE4,D.PLZ,D.ORT,D.BLZ,D.KTONR,D.BANK,E.NR,E.KDM,E.UID,E.STNR,E.KDART,E.NAME,E.ADRESSE1,E.ADRESSE2,E.ADRESSE3,E.ADRESSE4,E.PLZ,E.ORT,E.BLZ,E.KTONR,E.BANK,F.NR,F.KDM,F.UID,F.STNR,F.KDART,F.NAME,F.ADRESSE1,F.ADRESSE2,F.ADRESSE3,F.ADRESSE4,F.PLZ,F.ORT,F.BLZ,F.KTONR,F.BANK,G.NR,G.KDM,G.UID,G.STNR,G.KDART,G.NAME,G.ADRESSE1,G.ADRESSE2,G.ADRESSE3,G.ADRESSE4,G.PLZ,G.ORT,G.BLZ,G.KTONR,G.BANK,H.NR,H.KDM,H.UID,H.STNR,H.KDART,H.NAME,H.ADRESSE1,H.ADRESSE2,H.ADRESSE3,H.ADRESSE4,H.PLZ,H.ORT,H.BLZ,H.KTONR,H.BANK,I.NR,I.KDM,I.UID,I.STNR,I.KDART,I.NAME,I.ADRESSE1,I.ADRESSE2,I.ADRESSE3,I.ADRESSE4,I.PLZ,I.ORT,I.BLZ,I.KTONR,I.BANK,J.NR,J.KDM,J.UID,J.STNR,J.KDART,J.NAME,J.ADRESSE1,J.ADRESSE2,J.ADRESSE3,J.ADRESSE4,J.PLZ,J.ORT,J.BLZ,J.KTONR,J.BANK,K.NR,K.KDM,K.UID,K.STNR,K.KDART,K.NAME,K.ADRESSE1,K.ADRESSE2,K.ADRESSE3,K.ADRESSE4,K.PLZ,K.ORT,K.BLZ,K.KTONR,K.BANK,L.NR,L.KDM,L.UID,L.STNR,L.KDART,L.NAME,L.ADRESSE1,L.ADRESSE2,L.ADRESSE3,L.ADRESSE4,L.PLZ,L.ORT,L.BLZ,L.KTONR,L.BANK,M.NR,M.KDM,M.UID,M.STNR,M.KDART,M.NAME,M.ADRESSE1,M.ADRESSE2,M.ADRESSE3,M.ADRESSE4,M.PLZ,M.ORT,M.BLZ,M.KTONR,M.BANK,N.NR,N.KDM,N.UID,N.STNR,N.KDART,N.NAME,N.ADRESSE1,N.ADRESSE2,N.ADRESSE3,N.ADRESSE4,N.PLZ,N.ORT,N.BLZ,N.KTONR,N.BANK,O.NR,O.KDM,O.UID,O.STNR,O.KDART,O.NAME,O.ADRESSE1,O.ADRESSE2,O.ADRESSE3,O.ADRESSE4,O.PLZ,O.ORT,O.BLZ,O.KTONR,O.BANK

FROM "A1.DB"A, "A1.DB"B, "A1.DB"C, "A1.DB"D, "A1.DB"E, "A1.DB"F, "A1.DB"G, "A1.DB"H,  "A1.DB"I, "A1.DB"J, "A1.DB"K, "A1.DB"L,  "A1.DB"M, "A1.DB"N, "A1.DB"O, "A2.DB"A, "A2.DB"B, "A2.DB"C, "A2.DB"D, "A2.DB"E, "A2.DB"F, "A2.DB"G, "A2.DB"H,  "A2.DB"I, "A2.DB"J, "A2.DB"K, "A2.DB"L,  "A2.DB"M, "A2.DB"N, "A2.DB"O, "A3.DB"A, "A3.DB"B, "A3.DB"C, "A3.DB"D, "A3.DB"E, "A3.DB"F, "A3.DB"G, "A3.DB"H,  "A3.DB"I, "A3.DB"J, "A3.DB"K, "A3.DB"L,  "A3.DB"M, "A3.DB"N, "A3.DB"O, "A4.DB"A, "A4.DB"B, "A4.DB"C, "A4.DB"D, "A4.DB"E, "A4.DB"F, "A4.DB"G, "A4.DB"H,  "A4.DB"I, "A4.DB"J, "A4.DB"K, "A4.DB"L,  "A4.DB"M, "A4.DB"N, "A4.DB"O, "A5.DB"A, "A5.DB"B, "A5.DB"C, "A5.DB"D, "A5.DB"E, "A5.DB"F, "A5.DB"G, "A5.DB"H,  "A5.DB"I, "A5.DB"J, "A5.DB"K, "A5.DB"L,  "A5.DB"M, "A5.DB"N, "A5.DB"O

WHERE  A.NR = B.Nr
AND A.Nr = C.NR
AND A.Nr = D.NR
AND A.Nr = E.NR
AND A.Nr = F.NR
AND A.Nr = G.NR
AND A.Nr = H.NR
AND A.Nr = I.NR
AND A.Nr = J.NR
AND A.Nr = K.NR
AND A.Nr = L.NR
AND A.Nr = M.NR
AND A.Nr = N.NR
AND A.Nr = O.NR

The tables are all containting the fields NR,KDM,UID,STNR,KDART,NAME,ADRESSE1,ADRESSE2,ADRESSE3,ADRESSE4,PLZ, ORT,BLZ,KTONR,BANK with only one record in each field. There is the possibiltiy, that customers have more than one CustomerNumber but this  doesn't matter - I can filter it later. Also I cannot say, which table is the 'completest' so it's necessary to get one 'AllAdr.DB' with all the completed fields of the others. The up-to-dateness is only secondary. Perhaps there's an other way to get a solution... I've no problem to merge all DBs to an access.mdb - do you think there is a better chance

Hi Lonnie,

I've tried your suggestion already in former times - however the result was not which I search.
I need a solution, which examines identical customer numbers and which SUPPLEMENTS associated empty fields with possibly existing contents of the other Tables(fields) - like a
synchronization betweet mobile-phone and outlook-dates,contacts....
New customer numbers are to be added with the associated entries. Double cust.numbers have to appear for manual treatment one behind the other. Any of this
address.db is nearly 30.000 !! records and I must find a solution with as few differences as possible.  

Well once you have the big table you can use an sql query and groupby the customer number and in all the other fields use a "maximum"  summary operator .

You will end up with a single record for each customer number and all empty records will have been replaced.


Lonnie

Hi,

I'm sorry but your big SQL is wrong. There are two main possibilities:

1.
Is it true to say that Table1 contains all valid phone numbers of your customers and Table4 contains all valid names?
Is it true that any table contains all valid data for one (or more) field(s)?
If the above is true, please tell us the source table name for each field of the 15 fields in the big table. Like:
KDM is in Table2
NAME is in Table4
etc.

And I'll write the SQL.

2.
Or alternatively one customer may have its BANK (for instance) in Table5 and another one's BANK to be in Table6 and NOT in Table5.

In that case I'd make a program which will:
- for each table from Table1 to Table10:
  - open one table (TableX)
  - loop through its records and for every record:
    - check if that NR already exists in the BigTable and if not - add a new record else position at the record that exists
    - for every field in TableX check if its value is not null and not empty (i.e. contains a value) and
      store that value in the corresponding field for that NR in the big table
  - end records loop
- end tables loop

Please decide which one best fits your needs and tell us.

Regards, Geo

Hi Geo, hi Lonnie,

First I need one big table - ok  - but with batAppend I've a little problem. CustNr (named NR) is an indexed and
autoincrement value and so I get only for the first table correct informations. (i.e.: each table contains 30.000
customers - with the result, that  in 'BigTable' last customer of table10 gets CustNr 300.000).  

Next I think, Geo's solution No. 2 is the best fitting one for CustNr. 1 to 29.000 because all adresses are identical
with CustNr and only the completeness is varying. Here I need your help for a program which loops through all
records and makes any necessary additions.

Last I have to list CustNr. 29.001 to 30.000. Here is the problem, that different custumers have the same CustNr.
The necessary adjustments for these 999 Records will be made manually.

Finally the updated and most complete 30.000 records will be served on LAN.

Regards Tom

I haven't tested the code above, so some errors are possible.
Forgot to say that there are two tables on the form: one for the big table and one for all others.

For the records above 29000 I can't help with a program. As you said: manual processing ;-)

First things first.   Do not use autoincrement for customer number field in Big table (You actually want the duplicates to exist as duplicates so set it to be an integer, long integer or even number.)  This will prevent it from issuing new customer numbers as data is appended.  It will merely store the existing number.
Once you consolidate the data as one master table without duplicates you can change the field back to an autoincrement field.


Once that is done the previous instructions should work without manual processing.  This is assuming there is no way of knowing whether data in one table is more valid than data in another.   The only assumption my method makes is that some data is better than no data and not that some data is better than other different data which is invariably true.  but requires more information than I have.