Delete query based on data from another table (1 Viewer)

[IannWard]

Hi All

I am trying to delete dups from a table whilst leaving one entry. I have created a query to find the dups and then copy them to a temp table. This shows me one of each dup in the table. I am now trying to create a delete query using wizard (not sql) to delete the dups in the main table using the data in the temp table. I want to base the delete on all fields in the temp table to ensure I only delete the dups that share the same data, some asset numbers are listed with earlier dates, I want to keep these.

What do I drag into the delete query and what criteria do I add?

I will then run an append query to put the single entry in the temp table back in to the main table, if that makes sense.

Thanks in advance

 

[isladogs]

By definition, duplicate records have exactly the same data.
If two otherwise identical records exist with different dates then
a) if dates are excluded from the fields checked, these will be shown as dupes
b) if dates are included they won't be included

In your case, I suggest you exclude the dates so these records are identified as dupes.
It is useful to add a Boolean field Tag and mark all those dupes as true.
Then run a separate query on those records to identify the lowest date for each using DMin.
Reset the Tag value for those as False as you want to keep those

Next delete all records where Tag = True after of course checking the records.

There are other methods that may be better
E.g. Don't delete records at all but mark them using a Boolean field as Active=False

 

[IannWard]

Hi

Thanks for your response. The table was created to monitor specific faults of returned devices and keep a total of each fault. Lets say a device comes in in Jan with a fault, that fault and date is recorded in the table against that device (asset No). Now, 3 months later the same device comes in with a different fault and is recorded the same way. The table allows duplicates on the primary key (Asset No) to allow multiple faults for the same device.

Is what happens, is when an employee is entering the fault data in a form, they often press submit and then later add another field and press submit again, this then creates a double entry for the same fault which then gives incorrect stats on my reports.

All I want to do is prevent dups on the same date but allow dups from earlier dates.

Any ideas?

Many Thanks

 

[isladogs]

Create a composite index using both the AssetNo and your date field so that you cannot get duplicate records for same AssetNo and Date.

If your PK file is just AssetNo, it wouldn't allow duplicates for that field. Perhaps you have a different PK or a composite PK

 

[IannWard]

Hi Again

Not sure what you mean by composite (still learning, the hard way).

I did create an index in the table and it worked fine, it blocked the dups as expected, the problem is that every time the team over click the submit button, thus trying to create a dup, I get a popup error message informing me that cannot continue as would create a duplicate.

Perhaps I should add that the submit button first of all saves the form data to another table before the copying some of the fields to this table. Although I get the error message the data is first saved to the main data.

If I could just get rid of the popup message, all would be well.

 

[isladogs]

Not sure what you mean by composite (still learning, the hard way).

I did create an index in the table and it worked fine, it blocked the dups as expected, the problem is that every time the team over click the submit button, thus trying to create a dup, I get a popup error message informing me that cannot continue as would create a duplicate.

Perhaps I should add that the submit button first of all saves the form data to another table before the copying some of the fields to this table. Although I get the error message the data is first saved to the main data.

If I could just get rid of the popup message, all would be well.

Composite = more than one field in the same index

If you create a composite index (or composite PK) you won't be able to enter duplicates so the warning message appears for information.
You can get rid of the message by removing the index - then you can get duplicates
Choose whichever you want but you can't have both at the same time

 

[IannWard]

I did have more than one field selected in the index, so a composite it was.

Still not solved my problem though.

Back to the Delete query then. Help appreciated with delete query as mentioned in earlier post.

Regards

 

[isladogs]

See my suggested delete solution from my first reply

 

[IannWard]

Hi Again

I can't remove the date as this is needed to get stats based on month etc. Not quite sure what a Boolean is. Newby still, although looking for a good access book to read.

Regards

 

[isladogs]

I'm not telling you to delete the date field.
I'm telling you to exclude that field when you create a duplicates query.

Boolean fields = yes/no fields

What makes a good book depends on knowledge levels and personal preference. You might find the You Tube videos by Steve Bishop informative. Everything from beginners to advanced. Over 100 in total