Managing duplicate records

[radshar]

Hello,

I've created a database where a user will upload a new excel datasheet daily. The user has to review the information and indicate within the record if they've dealt with the issue. once dealt with they change the status of the record.
This datasheet comes from an external system. If the user has not dealt with an item on the list it will reappear the next day with almost all the same information. However when they do the upload again they will now have a duplicate record.

What would be the best way to manage this. Ideally, I'd like for the duplicate to be identified on the table or for them to replace the previous entry. My challenge here is that if the user had input comments or updated the status to say 'in-progress' on the previous record, I do not wan the duplicate to remove that....however I would like for the created date' to appear (which comes with the datasheet) as this will help identify which report it came from.

Or if there could be a way to manage this even more efficiently that would be great!

all suggestions appreciated!

Cheers!!

 

[MarkK]

What harm do the duplicates do? Why not just allow duplicates, and then write a GROUP BY query that merges the "dupe" records, showing only the information you want? For instance, you could write a query that creates the fields . . .

..., Min(ItemDate) As StartDate, Max(ItemDate) As EndDate, ...

. . . so you can actually allow for and keep track of multi-day data. Similarly, if there is a status field (make it a number), you could do . . .

..., Max(Status) As FinalStatus, ...

Managing duplicates is hard. Writing aggregate queries that GROUP BY shared fields is easy.

 

[CJ_London]

think the only way you could manage it is if the excel file has a uniqueID column of some sort - or a combination of columns to create a uniqueID.

If that was the case, upload the file to a temporary table (or link to the file) then use an append query to append records that are in the excel file, but not in your main table based on the uniqueID and perhaps an update query where the uniqueID in the main table matches the uniqueID in the file and relevant fields have changed

 

[radshar]

What harm do the duplicates do? Why not just allow duplicates, and then write a GROUP BY query that merges the "dupe" records, showing only the information you want? For instance, you could write a query that creates the fields . . .

..., Min(ItemDate) As StartDate, Max(ItemDate) As EndDate, ...

. . . so you can actually allow for and keep track of multi-day data. Similarly, if there is a status field (make it a number), you could do . . .

..., Max(Status) As FinalStatus, ...

Managing duplicates is hard. Writing aggregate queries that GROUP BY shared fields is easy.

Well the duplicates will interfere with the statistical reports that I will produce, they will also make it seem like there is more work to do than there actually is. I mean the user has to review each et of data only once and update the status as they go along, but because it reappears the next day this makes it seem like two records.....

 

[radshar]

Hi Mark can I aggregate based on a unique ID?

What harm do the duplicates do? Why not just allow duplicates, and then write a GROUP BY query that merges the "dupe" records, showing only the information you want? For instance, you could write a query that creates the fields . . .

..., Min(ItemDate) As StartDate, Max(ItemDate) As EndDate, ...

. . . so you can actually allow for and keep track of multi-day data. Similarly, if there is a status field (make it a number), you could do . . .

..., Max(Status) As FinalStatus, ...

Managing duplicates is hard. Writing aggregate queries that GROUP BY shared fields is easy.