Find matching rows (54 columns)

  • Thread starter Thread starter CoCoNutt
  • Start date Start date
C

CoCoNutt

I have a table that has 54 columns in it. I would like to know how to
see how many of the records have complete identical records in all 54
columns. Is there an easy way to do this? I was going to use the find
dups wizard but the size limitation prevents me from doing it. Any
help is appreciated
 
well, you could create a FindDuplicates query based on the table, using the
wizard, matching on the first 10 fields and including the rest of the
fields. then rename the query something short, like qryTen. then create
another FindDuplicates query, based this time on qryTen, matching the next
10 fields, and including the rest of the fields. rename the query as
qryTwenty. then create a third FindDuplicates query...

frankly, the "queries based on queries" may become too complex for Access to
run, but you can try it out - at least it will be fairly quick and easy to
test. you could employ a "temp" table to help you out, appending the
recordset of each Duplicates query into the table and running the next query
on those records.

hth
 
CoCoNutt said:
I have a table that has 54 columns in it. I would like to know how to
see how many of the records have complete identical records in all 54
columns. Is there an easy way to do this? I was going to use the find
dups wizard but the size limitation prevents me from doing it. Any
Click to expand...


How many is pretty easy. Just runs a unique values query:

SELECT DISTINCT * FRIM table

And subtract the number of records returned from the number
of records in the table.

Finding out which ones are duplicates is what's difficult
;-)

A find duplicates query won't work because a WHERE clause is
limited to 40 ANDs.

54 columns is a red flag that you probably have a
normalization problem.
 
Marshall Barton said:
How many is pretty easy. Just runs a unique values query:

SELECT DISTINCT * FRIM table

And subtract the number of records returned from the number
of records in the table.

Finding out which ones are duplicates is what's difficult
;-)

A find duplicates query won't work because a WHERE clause is
limited to 40 ANDs.

54 columns is a red flag that you probably have a
normalization problem.
Click to expand...

Marsh, do you know if it would be possible to create a unique index on 54
columns?

What I had in mind is creating a second table with an index across all
columns, then attempt an append query to identify the duplicates.

Randy Harris
 
Hey Marsh,

Cold enough for you?

I would be considering taking some text columns, padding them to maximum
length, and concatenating them into fewer columns using an intermediate
query. Even numeric columns can do this, padding to the left (integer
values only). If you get the total number of columns down, you should be
able to do this. Not that it overcomes any of the other problems inherent
with such a large number of columns.

Otherwise, find some columns not necessary to uniqueness.

Its rare to need more than 15 columns in a table. 20 tops.

Tom Ellison
 
Randy Harris said:
Marsh, do you know if it would be possible to create a unique index on 54
columns?
Click to expand...


No, it's not. You cannot have more than 10 fields in an index.
What I had in mind is creating a second table with an index across all
columns, then attempt an append query to identify the duplicates.
Click to expand...

I do agree with the others that 54 fields in a table doesn't sound right.

You should be able to create a query, though, that will identify the
duplicates:

SELECT Field1, Field2, Field3, ...
FROM MyTable
GROUP BY Field1, Field2, Field3, ...
HAVING Count(*) > 1
 
Randy said:
Marsh, do you know if it would be possible to create a unique index on 54
columns?

What I had in mind is creating a second table with an index across all
columns, then attempt an append query to identify the duplicates.
Click to expand...


I think an index can only(?) have ten fields so that's not a
viable approach.

How about this really dumb idea. Create a query that has a
few calculated fields that are the concatenation of half a
dozen fields from the table:

SELECT a "/" & b & "/" & c & "/" & . . . As f1,
p "/" & q & "/" & r & "/" & . . . As f2,
. . .
x "/" & y & "/" & z & "/" & . . . As fn
FROM table

Then run a Find Duplicates query against that query.
 
Yeah, Tom it is more than cold enough. Too cold to go out
and play with the dogs, which results in them driving me
nuts by trying to pile every toy they own on my lap ;-)

Hey, it took me longer, but I came up with the same idea
about concatenating fields in an intermediate query. Maybe
some of your query teachings are finally start to soak in
;-)
 
Dear Marsh,

I don't claim to have taught you anything! But you're sharp enough to learn
without me having to try.

It's so much colder here now, I'm sorry I mentioned it. Headed for
about -10F tonight. Vehicles already acting up. It's only early December.
Must be global warming. Brrrrrr!

Tom


Marshall Barton said:
Yeah, Tom it is more than cold enough. Too cold to go out
and play with the dogs, which results in them driving me
nuts by trying to pile every toy they own on my lap ;-)

Hey, it took me longer, but I came up with the same idea
about concatenating fields in an intermediate query. Maybe
some of your query teachings are finally start to soak in
;-)
--
Marsh
MVP [MS Access]


Tom said:
Hey Marsh,

Cold enough for you?

I would be considering taking some text columns, padding them to maximum
length, and concatenating them into fewer columns using an intermediate
query. Even numeric columns can do this, padding to the left (integer
values only). If you get the total number of columns down, you should be
able to do this. Not that it overcomes any of the other problems inherent
with such a large number of columns.

Otherwise, find some columns not necessary to uniqueness.

Its rare to need more than 15 columns in a table. 20 tops.

Tom Ellison


"Marshall Barton" wrote
Click to expand...
Click to expand...
 
Back
Top