S
srm
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
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