Find duplicates from 2 tables and remove from 1 table.

[soldat452002]

Hello,

I have a report with 2 access tables (1 Master table and another a daily feed table)

The Master table keeps a log of all incoming records. (once append it to this table, should not show in future reporting)

The Daily feed information within the last 48 hours. (uploaded from an excel report into access temporary table)

When the daily feed table gets completed, I append the records and updated them into the Master to avoid duplication.

When I upload the daily feed table and I match it against the Master table
to find duplicates, how can I delete the duplicates from the Daily Feed table?

This is my code to find duplicates:

SELECT CMPreport.ID, CMPreport.MbrName, tblMaster.ID
FROM CMPreport LEFT JOIN tblMaster ON CMPreport.ID = tblMaster.ID
WHERE (((tblMaster.ID) Is Not Null));

 

[vbaInet]

Duplicates based on what field(s)?

Have you tried running the Duplicates Query Wizard to begin with?

 

[Old Man Devin]

Are you concerned about copying something from teh daily table into the master table if it is already in the master table? If so, you could set up a Unique Index on the master table that doesn't let you copy in duplicates. This is done via table design view, indexes button at the top. Start a new index then in the second column list all the columns. Then in the properties bit at the bottom of the index window, set unique to Yes.

Then hopefully when you copy the daily stuff into the master, it will tell you if you are making a duplicate and not copy in the record that would have done so.

 

[soldat452002]

Hello,

The Master table has all those records of daily feed reports we already completed. Once a record from the daily feed table is completed, I delete it from the daily report and append them to the Master Table. So the next time I import new records into the daily feed report I bumped it against the Master table to provide me with those records already showing "Completed" in the Master table. What I need to do is remove them and only keep NEW records on the Daily Feed Report.

 

[Old Man Devin]

Hello,

The Master table has all those records of daily feed reports we already completed. Once a record from the daily feed table is completed, I delete it from the daily report and append them to the Master Table. So the next time I import new records into the daily feed report I bumped it against the Master table to provide me with those records already showing "Completed" in the Master table. What I need to do is remove them and only keep NEW records on the Daily Feed Report.

Had a look at your tables. Looks like you have a completed boolean field there, so all you need to do is delete everything from the daily table that isn't completed, right? In which case, make a delete query using this SQL and execute it:

DELETE tblQiz.*, tblQiz.Completed
FROM tblQiz
WHERE (((tblQiz.Completed)=True));

No annoying duplicate finding required, if it really is just the complete ones that get moved across!

 

[soldat452002]

Well I was thinking more of Bumping these two reports to find duplicates and then deleting the results. Problem is when I run DELETE QUERY I get this message "Could not delete from specified tables.

SELECT CMPreport.*, tblMASTER.SPS
FROM CMPreport INNER JOIN tblMASTER ON CMPreport.SPS = tblMASTER.SPS
WHERE (((tblMASTER.SPS) Is Not Null));

DELETE CMPreport.*, tblMASTER.SPS
FROM CMPreport INNER JOIN tblMASTER ON CMPreport.SPS = tblMASTER.SPS
WHERE (((tblMASTER.SPS) Is Not Null));

 

[Old Man Devin]

I'm not sure what you mean by 'bumping'?

It might be stopping you deleting due to relationships with referential integrity set between your tables. Easier to identify the specific records in each table separately then delete them with separate queries, might be easier to see what is happening that way.

 

[vbaInet]

I don't see the need of you deleting from and updating to two tables that contain the same fields. Normalisation springs to mind!

Queries are included in Access to allow you filter data based on certain criteria. If you want your future report to show only those records that are Not Completed, simply use a query and put the right criteria under the filed you wish to filter by.

 

[soldat452002]

The reason is the report is pulled manually from a website on a daily basis. If the data changes daily we have to at least create a temporary table and a history table.

I don't see the need of you deleting from and updating to two tables that contain the same fields. Normalisation springs to mind!

Queries are included in Access to allow you filter data based on certain criteria. If you want your future report to show only those records that are Not Completed, simply use a query and put the right criteria under the filed you wish to filter by.

 

[vbaInet]

Ok, but I would still like to know what fields you're matching against? Are you checking whether an entire row (i.e. all fields in a row) in one table matches an entire row in another table for all records?