Query Existing Data, Query New Data, Append New Data
Posted on 2007-04-02
Scenario: The scenario is our employees can attend some training at the vendors facility. The training/test results are sent to us in an access mdb file. We need to update our SQL database with the test results. Any employee (userID) can take more than one course so there could be one employee with multiple titleID (course title). There will not be duplicate userID/titleID pair. testID is simple the date the test was taken and is paired with titleID.
Need: We receive an access mdb file from a vendor (A) that contains records (userID, titleID, and testDate). I need help with a query that will query our current database (B) and then query the new data (A) and update the current database (B) with any new values.
Field Names: userID, titleID, and testDate
We are using Coldfusion and SQL2000. We have the .mdb file setup as a datasource so we can directly query the mdb file.