Consider the following:
1) Create & save ImportQuery1 to identify "Old" records with DateTimeStamps
=> Min(DateTimeStamp) of TempTable
2) Create & save ImportQuery2 to delete the records identified in Query1
3) Create & save ImportQuery3 to identify duplicate records in whichever
table you want to delete them from (a little unclear on this)
4) Create & save ImportQuery4 to delete the records identified in query 3
5) Create & save ImportQuery5 to append TempTable to "old" data
6) Write code to run saved ImportQueries 2, 4 and 5
This assumes the name of TempTable is static and never changes. Once set up,
Step 6 is the only thing that needs "running".
7) Optional: Query/code to Delete all records in TempTable in preparation
for next import (Don't delete the table as a whole. The table structure
should always be left intact to make sure the stored queries contain "valid"
table and field references).
Advantage: breaking the process down into steps that can be done by a stored
query can make maintenance easier if/when things go wrong or if/when changes
are made to the data structure. Fixing/changing a query can be a lot easier
than doing the same thing within code, and might be able to be done by
someone who isn't at "developer" level, if necessary.
HTH,
--
George Nicholson
Remove 'Junk' from return address.
"jpb" <(E-Mail Removed)> wrote in message
news:15F32CB9-2824-42E7-8233-(E-Mail Removed)...
>I am looking for some advice/opinions on the best course of action for some
> code that I have been working on. I have programmed before, but I have
> been
> attempting to teach myself Access/VBA as I go along. After reading
> through
> some of these discussion threads, I am starting to think that there may be
> a
> better way than the brute force approach that I have been struggling with.
>
> I am importing data from a text file into a temporary table. This data
> however, may have a field that contains duplicate data. I need to remove
> the
> corresponding duplicate record(s) can be removed. After this
> data-simplification has occured, I then need to append the newly imported
> data from the temp table into the actual table that I am storing my valid
> data in. Another caveat - there is a date/time field that all data is
> sorted
> by; the imported data will always overlap and supersede this date/time
> value
> of the old data by some amount. The "old" records of the existing table
> must
> be deleted and replaced by the newer, more current imported data.
>
> Any suggestions on how to efficently do this? Right now I have a very
> ugly
> nested-loop churning through the records so that I can compare
> Current-to-Previous-to-Next records. It seems long-winded and system
> consuming. With all the bits and pieces that I am picking up through this
> discussion board and the help file, my gut is starting to tell me that
> this
> is getting to be a lot uglier than it needs to be.
>
> Thanks in advance, and please respond to this discussion thread - network
> security prevents access to this email at work.
|
Similar Threads
