Delete All Instances of Duplicates

[Deema]

Read many of the threads but seem to be missing what I need to do . . .
probably based on my limited access knowledge.

I have a query that is based off one table. I have 39 fields but only need
to set criteria for three of them.
1) field called response code - only need those marked as 1 (in critiera
field type 1)
2) field called invoice number - need to remove all duplicates, not just the
second or third, etc. instance but all instances . . . I'm stuck here!
3) field called action code - don't include credit or void (in criteria
field Not "credit" or "void")

It's #2 above that I can't seem to get working correctly. I need to run #1
first, then #2, then #3 in order to receive the output I need.

I'm currently run this in Excel but it is taking me too long and thought
access would do the trick but I am really stuck on #2 above.

Any help, suggestions????

 

[Allen Browne]

Create a query that pulls up the duplicated invoice numbers for you.
(There's a wiz. to help.) Make sure it displays only the invoice number
field, and de-duplicates. Then change it into a Make Table query (Make Table
on Query menu.) This will generate a table with just one field containing
all the duplicated invoice numbers.

Open the new table in design view, and make the invoice number the primary
key.

You can then join the 2 tables (the new one and the original) in a query,
and delete from the original.

 

[Lord Kelvan]

select *
from [thetable]
where [response code] = 1
and ([action code] <> "credit"
or [action code] <> "void")
and [invoice number] not in (
select [invoice number]
from [thetable]
having count(]invoice number]) > 1)

that query should do it

to use this query
create a new query
cancel the popup box
click view in the menu bar
click sql view
paste the above replacing what is there
run the query

remember to replace thetable with whatever your table name is

the query isnt tested but it should work

any questions please ask

Regards
Kelvan

 

[Deema]

Thank you to both of you for the quick response. As soon as I get to work
torrow I am going to try both directions. The more I know, the better.

I do have one question for "Lord Kelvan" . . . it may be answered when I
actually input the code but have been thinking about it so I figured I would
ask.

Am I still able to view the results for the remaining 36 fields which do not
require any specific criteria. I wasn't sure if I would be able to add these
in or if I will only be able to view the results for the 3 specific fields.

Thank you again, boy you both have been enormously helpful Thank you!!!

 

[Lord Kelvan]

the select * peice of my code means select all fields so yes

Regards
Kelvan

 

[Deema]

I placed the following in the SQL view but received a syntax error as noted
below. Any suggestions?

select *
from [Credit Card Table Test]
where [Response Code] = 1
and ([Action Code] <> "CREDIT"
or [Action Code] <> "VOID")
and [Invoice Number] not in (
select [Invoice Number]
from [Credit Card Table Test]
having count(]Invoice Number]) > 1)

Error received:
Syntax error. in query expression '[Response Code] = 1
and ([Action Code] <> "CREDIT"
or [Action Code] <> "VOID")
and [Invoice Number] not in (
select [Invoice Number]
from [Credit Card Table Test]
having count(]Invoice Number]) > 1)'.

 

[John Spencer]

You are missing a GROUP BY clause in the subquery.

select *
from [Credit Card Table Test]
where [Response Code] = 1
and ([Action Code] <> "CREDIT"
or [Action Code] <> "VOID")
and [Invoice Number] not in (
select [Invoice Number]
from [Credit Card Table Test]
GROUP BY [Invoice Number]
having count(]Invoice Number]) > 1)

I would be careful using this to delete records. You may get results
other than what you want. SO BACKUP your data before you do the delete.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I placed the following in the SQL view but received a syntax error as noted
below. Any suggestions?

select *
from [Credit Card Table Test]
where [Response Code] = 1
and ([Action Code] <> "CREDIT"
or [Action Code] <> "VOID")
and [Invoice Number] not in (
select [Invoice Number]
from [Credit Card Table Test]
having count(]Invoice Number]) > 1)

Error received:
Syntax error. in query expression '[Response Code] = 1
and ([Action Code] <> "CREDIT"
or [Action Code] <> "VOID")
and [Invoice Number] not in (
select [Invoice Number]
from [Credit Card Table Test]
having count(]Invoice Number]) > 1)'.

the select * peice of my code means select all fields so yes

Regards
Kelvan

 

[Lord Kelvan]

you dont need group by cause i am not doing any caluclations in the
sum of that query

the problem was

count(]Invoice Number])

the ] is the wrong way round sorry it was a typo

this is the fixed version

select *
from [Credit Card Table Test]
where [Response Code] = 1
and ([Action Code] <> "CREDIT"
or [Action Code] <> "VOID")
and [Invoice Number] not in (
select [Invoice Number]
from [Credit Card Table Test]
having count([Invoice Number]) > 1)

hope this helps

Regards
Kelvan