Collate data from two identical tables...

[Dave Ramage]

Hi...

I have separate but identical databases operating in three
different locations. Once per week I get emailed the
complete main table from each location in the form of
a .csv or .xls file (created on client PC using
DoCmd.TransferText).

I have a master table on my PC which I update with any new
records in the data I am sent. What is the best way to
automate this update/collation process- either a query or
in DAO/ADO code?

The table consists of a primary key (ID_HotlineRef) and
approx 50 other fields. The data I receive will consist of
the following:
-Complete new records (need to add these)
-Existing records that have been changed since the last
data file was sent (need to update these)
-Existing records that have not changed (ignore these)

Thanks,
Dave

 

[John Nurick]

HI Dave,

Use two queries that join your existing table and the new data on the
primary key.

One update query to update records that already exist; one append query
to add the new records.

 

[Dave Ramage]

Thanks John...this makes sense. I'd been struggling to get
one query to do it all..I'll give this a try.

Cheers,
Dave

-----Original Message-----
HI Dave,

Use two queries that join your existing table and the new data on the
primary key.

One update query to update records that already exist; one append query
to add the new records.

Hi...

I have separate but identical databases operating in three
different locations. Once per week I get emailed the
complete main table from each location in the form of
a .csv or .xls file (created on client PC using
DoCmd.TransferText).

I have a master table on my PC which I update with any new
records in the data I am sent. What is the best way to
automate this update/collation process- either a query or
in DAO/ADO code?

The table consists of a primary key (ID_HotlineRef) and
approx 50 other fields. The data I receive will consist of
the following:
-Complete new records (need to add these)
-Existing records that have been changed since the last
data file was sent (need to update these)
-Existing records that have not changed (ignore these)

Thanks,
Dave

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.