Importing and deleting duplicate entries

[Guest]

Hi,

I've got a problem in Access that I need help on. I think it would be easier
to explain the situation in full.

Each day i get a standardized report in excel format telling me about the
journal entries posted in the past week. So for day 1, i would get this
report detailing all the entries that have been posted on our general ledger.
The issue is that the report is cumulative, so for day2 i would get day1's
report plus any additional entries made between the time when the first
report was issued to when the second report is issued. As you can see, there
is a lot of duplication. The problem is once i receive and import the day1
report, i add new fields and my analysis to each entry. What I want to do is
when I import the new excel file for day2, whatever entries that are carrying
over from day1 (which at this point i've added data to), i want to replace
that entry for day2 in my new table because essentially, the day2 report is
simply restating what already happened in day1, simply without my added
analysis. To give you a bit more information, each entry has a unique id and
so entries that are listed in day1's report and repeated in day2 have the
same id.

This is a big issue to tackle and I hope someone can help me out.

Much Thanks,

 

[John Nurick]

Hi Chris,

Create a new "no duplicates" index on the ID field in your Access table.
That way, only the new records in each day's Excel data will be
imported.

 

[Guest]

Not impossible, but will take a different approach than you are using now, I
think.
What I would suggest is that rather than import the Excel table, you link to
it.

Create a table in Access that is just like the table you put the data in.
We will call it Temp for now.

Write a Query that will copy rows from the linked XL table into your temp
table, but exclude prior day entries. I hope there is some column in the XL
file that tells you what day the row or some other way the query can exclude
them.

Write another Query that will append the data in the Temp table to the GL
table.

So, the sequence is:

Link the XL file
Run the query that copys the data into the Temp table
Open the Temp table
Make your additions in the temp table
Run the query that copys the new data from the Temp table to the GL table.