Checking for duplicate entries in large table

[Jermin]

Hi,

I have a database composed of a single table composed of 2 columns, an
auto-numbered ID column and a column which contains 30 million random
numbers. All I want to Access to do is check the numbers and let me
know if there are any duplicates. Ideally I'd like Access to flag the
duplicates if they exist. The easy approach that I thought would work
is to go to Design view and select Indexed table, No Duplicates. This
doesn't work however, as Access won't save the table after I change
the Indexed setting from No duplicates to Duplicates allowed, or from
Duplicates allowed to No Duplicates. Each time I try to save the table
I got a message saying invalid argument.

Any suggestions on how I can accomplish what I want with minimal
effort?

Thanks.

--Brian

[Jeremy Wallace]

SELECT Count(AutoNumberColumn) AS HowMany, OtherColumn
FROM tblWhatever
GROUP BY OtherColumn
HAVING Count(AutoNumberColumn)>1;
--
Jeremy Wallace
AlphaBet City Dataworks
http://www.ABCDataworks.com
"Jermin" <ju*********@hotmail.com> wrote in message
news:2b*************************@posting.google.co m...

Hi,

I have a database composed of a single table composed of 2 columns, an
auto-numbered ID column and a column which contains 30 million random
numbers. All I want to Access to do is check the numbers and let me
know if there are any duplicates. Ideally I'd like Access to flag the
duplicates if they exist. The easy approach that I thought would work
is to go to Design view and select Indexed table, No Duplicates. This
doesn't work however, as Access won't save the table after I change
the Indexed setting from No duplicates to Duplicates allowed, or from
Duplicates allowed to No Duplicates. Each time I try to save the table
I got a message saying invalid argument.

Any suggestions on how I can accomplish what I want with minimal
effort?

Thanks.

--Brian

[PaulT]

Minimal effort:

1. go to the Queries group...
2. click New...
3. One of the options listed is what you want to do.

-Paul

ju*********@hotmail.com (Jermin) wrote in message news:<2b*************************@posting.google.c om>...

Hi,

I have a database composed of a single table composed of 2 columns, an
auto-numbered ID column and a column which contains 30 million random
numbers. All I want to Access to do is check the numbers and let me
know if there are any duplicates. Ideally I'd like Access to flag the
duplicates if they exist. The easy approach that I thought would work
is to go to Design view and select Indexed table, No Duplicates. This
doesn't work however, as Access won't save the table after I change
the Indexed setting from No duplicates to Duplicates allowed, or from
Duplicates allowed to No Duplicates. Each time I try to save the table
I got a message saying invalid argument.

Any suggestions on how I can accomplish what I want with minimal
effort?

Thanks.

--Brian

[Jermin]

Beautiful - exactly what i was looking for. Fast, easy & powerful. Thank you!

Minimal effort:

1. go to the Queries group...
2. click New...
3. One of the options listed is what you want to do.

-Paul

[Jermin]

Encountered a new problem when running the query. I get a message
stating that there is not enough space on the temporary disk. This of
course is nonsense as I have gigs of free space on the drive and a
completely empty temp folder. Doing a newsgroup search others have had
the same problem as well, with far fewer records than I. I'm guessing
the message is implying that Access can't handle running the query on
so many records (30 million). Any suggestions for how I can run this
query succesfully, or do i need to go to a SQL box?

THanks!
pt******@spsu.edu (PaulT) wrote in message news:<81*************************@posting.google.c om>...

Minimal effort:

1. go to the Queries group...
2. click New...
3. One of the options listed is what you want to do.

-Paul

[PaulT]

(I may not offer the easiest choice, so check back for other
responses...)

If you have access to an SQL box, and can link to that table, then
that would probably be easiest; otherwise, you'd be stuck dividing the
table into smaller "chunks" and have to do quite a bit of
cross-checking depending on how many 'chunks' you have.

Or you could possibly try to run Jeremy's example and see if you can
get any count quantities.

Also, I don't know what version of access you have, but if it's 97,
check this too:
http://support.microsoft.com/default...29&Product=acc

-Paul

ju*********@hotmail.com (Jermin) wrote in message news:<2b*************************@posting.google.c om>...

Encountered a new problem when running the query. I get a message
stating that there is not enough space on the temporary disk. This of
course is nonsense as I have gigs of free space on the drive and a
completely empty temp folder. Doing a newsgroup search others have had
the same problem as well, with far fewer records than I. I'm guessing
the message is implying that Access can't handle running the query on
so many records (30 million). Any suggestions for how I can run this
query succesfully, or do i need to go to a SQL box?

THanks!
pt******@spsu.edu (PaulT) wrote in message news:<81*************************@posting.google.c om>...

Minimal effort:

1. go to the Queries group...
2. click New...
3. One of the options listed is what you want to do.

-Paul