Compare Data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have my main table in my database with the following fields:

Name; Status; ID etc etc

I produce an Excel Download from Business Objects to get this data and from
this i have 2 scenarios in which i was hoping could be automated?

1. I could have new entries ie a new Name, Status, ID or

2. The Status of current records in the table could change from eg. Testing
to Operation

Is there anyway i could import the new data and compare it with the current
contents of my Main Table to add new records and also update the change in
Status?

Thanks
Dave
 
Hi Dava,

1) Use File|Get External Data either to link to the Excel worksheet or
to import it to a temporary table. Then

2) Create an update query that joins the temporary table and your main
table on the primary key (is this ID?) and updates Status in the main
table from Status in the temporary table;

3) Create an append query that appends records from the temporary table
(it will only be able to append the records that don't already exist).

If your table doesn't have a primary key, (2) won't work and (3) will
create duplicate records.
 
Hi John

Thnaks for this, i seem to have mastered the update side of things, however
i seem to be struggling with the append query.

How does the query only know to append new records? Do you put something in
the criteria?

Thanks
Dave
 
Hi Dava,

The index on the main table's primary key won't allow the append query
to create duplicate records. (If it doesn't have a primary key, the
update query won't know which record in the main table goes with which
record in the linked table.)

1) Are you getting an error message about key violations when you run
the append query?

2) in your next message, please include the SQL of each of the two
queries (open the query in design view, switch to SQL view, and copy and
paste the SQL statement).
 
Back
Top