I'm using Access 2003 and new to it. I usually keep data in Excel, but
reached the limit of 65,000 rows. I have read through many articles on
removing duplicate records, but I'm still having trouble. Here is my
situation.

I have combined multiple excel files of 185,000 records in one database
with about 40 or 50 fields. I took a subset of my records (23) for my
testing.

What I'm filtering on is the "Company" and "MailingAddress1". It is
possible that for the same address there could be multiple company
names. For example, in my 23 test records, I have three address of
"4920 3rd Street". Each of those addresses have different companies,
so I cannot just filter on "MailingAddress1" as I would delete 2
records I require.

The only way I can figure out how to do this (without learning too much
of Access at this point) is...

1. Create a new query
2. Use the total field (Use Group By on the "Company" and
"MailingAddress1" fields and "First" or "Last" on the other 50 fields.
3. When I do this, I need to update the totals field value in each
field. With 50 some fields, this is kind of tedious.
4. I run the query (create a new table) and see the data I require, but
now each of the column headings is "First", then the old column
heading. I then need to change the headers on the new table for all
fifty fields.

I may need to do this many times so I was wondering if there was an
option that is easier. The Find Duplicates finds the duplicates, but
doesn't leave at least one of the records.

Again, I'm new to Access, so I'm just learning and hoping I can learn
from others experience.

Thank you

Shawn

I have had luck with the "Find Duplicates Query Wizard"

Choose "Queries" on the Object toolbar, then click the "New" button and
the "Find duplicates Query Wizard" should be one of your choices.

don


srm wrote:
> I'm using Access 2003 and new to it. I usually keep data in Excel, but
> reached the limit of 65,000 rows. I have read through many articles on
> removing duplicate records, but I'm still having trouble. Here is my
> situation.
>
> I have combined multiple excel files of 185,000 records in one database
> with about 40 or 50 fields. I took a subset of my records (23) for my
> testing.
>
> What I'm filtering on is the "Company" and "MailingAddress1". It is
> possible that for the same address there could be multiple company
> names. For example, in my 23 test records, I have three address of
> "4920 3rd Street". Each of those addresses have different companies,
> so I cannot just filter on "MailingAddress1" as I would delete 2
> records I require.
>
> The only way I can figure out how to do this (without learning too much
> of Access at this point) is...
>
> 1. Create a new query
> 2. Use the total field (Use Group By on the "Company" and
> "MailingAddress1" fields and "First" or "Last" on the other 50 fields.
> 3. When I do this, I need to update the totals field value in each
> field. With 50 some fields, this is kind of tedious.
> 4. I run the query (create a new table) and see the data I require, but
> now each of the column headings is "First", then the old column
> heading. I then need to change the headers on the new table for all
> fifty fields.
>
> I may need to do this many times so I was wondering if there was an
> option that is easier. The Find Duplicates finds the duplicates, but
> doesn't leave at least one of the records.
>
> Again, I'm new to Access, so I'm just learning and hoping I can learn
> from others experience.
>
> Thank you
>
> Shawn