G
Guest
I have a linked table in an Access Database 2003. The linked table is updated
via a 3rd party application that is an Access database of all printouts done
on a printer. While 3rd party db doesn't have a primary key, I can use Print
ID, User ID, Time and Date as a combined 'key.' When the application captures
a printout, it adds a record to the database, and the ID is the job number
for that day for that printer (ex. 35). After a set amount of time (about 20
minutes) the record is finalized and the ID is changed to add the letter 'O'
as a prefix to the ID (ex. O35).
Given that, I append new records to another table that I set up (this is so
we can track out own data and not have to change a lot if the 3rd party app
changes db design). The problem is, eventually each record appears twice in
my table, as the append will see the unfinalized record, and then the
finalized record 20 minutes later, but add the finalized, not overwrite. I
changed my append to only append finalized records, but that creates a lag of
20 minutes, which is too long for what I am doing.
So I thought to write two SQL statements, one that moves only finalize
records, and the other that gets a set of unfinalized records and somehow
scans to see if there is a finalized counterpart before appending to my
table. The questions are, is it possible for an SQL statement to do the
compare and append, if so how, and if not, how can I loop through both record
sets when the find method only lets me compare 1 field in a multi-field
'key'.
Thanks, and sorry for the dissertation.
--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003
User of MS Office 2003
via a 3rd party application that is an Access database of all printouts done
on a printer. While 3rd party db doesn't have a primary key, I can use Print
ID, User ID, Time and Date as a combined 'key.' When the application captures
a printout, it adds a record to the database, and the ID is the job number
for that day for that printer (ex. 35). After a set amount of time (about 20
minutes) the record is finalized and the ID is changed to add the letter 'O'
as a prefix to the ID (ex. O35).
Given that, I append new records to another table that I set up (this is so
we can track out own data and not have to change a lot if the 3rd party app
changes db design). The problem is, eventually each record appears twice in
my table, as the append will see the unfinalized record, and then the
finalized record 20 minutes later, but add the finalized, not overwrite. I
changed my append to only append finalized records, but that creates a lag of
20 minutes, which is too long for what I am doing.
So I thought to write two SQL statements, one that moves only finalize
records, and the other that gets a set of unfinalized records and somehow
scans to see if there is a finalized counterpart before appending to my
table. The questions are, is it possible for an SQL statement to do the
compare and append, if so how, and if not, how can I loop through both record
sets when the find method only lets me compare 1 field in a multi-field
'key'.
Thanks, and sorry for the dissertation.
--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003
User of MS Office 2003