remove duplicate records in Access 2007

  • Thread starter Thread starter Erik
  • Start date Start date
E

Erik

I have a table that is created by an append query from 4 other tables. When
I use the append query, it creates duplicate records. For a record to be
considered a true duplicate, 4 fields have to match exactly. How do I create
a query that will delete the duplicates while retaining 1 record? (example: 4
records are duplicates, and I need to keep one of them while deleting the
other 3) The four fields that match to create a duplicate are "DATE", "OP",
"HOURS", and "ASSET". Any help is appreciated.

Thanks.

Erik
 
Hi Erik,
Create a copy of your table as a new table using the structure only option.
On the copy, create a unique index on those 4 fields.
Now run an append query to move the data from the existing table to the new
table. You will find that duplicates will be excluded from the table.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
If there is no difference between the duplicates, there's no way to tell
Access which one to keep. Your options are:

a) Create a query that groups on your 4 fields (so it only shows one record
where there are duplicates.) Turn it into a Make Table query, to make a new
table. Then throw the old table away.
or
b) Add an AutoNumber field and make it the primary key. You can now
deduplicate with a subquery like this:
http://allenbrowne.com/subquery-01.html#DeDuplicate
 
I have 9 total fields in each record. It's just that if these 4 match
between records, then it is a duplicate record
 
If a record is a duplicate based on the four fields, what values do you want
to keep in the other fields. If it makes no difference, use a total/aggregate
query as the source of the append query.

SELECT [Date], OP, Hours, Assets
, First([Tablename].[FieldName5]) as FieldName5
, First([Tablename].[FieldName6]) as FieldName6
, First([Tablename].[FieldName7]) as FieldName7
, First([Tablename].[FieldName8]) as FieldName8
, First([Tablename].[FieldName9]) as FieldName9
FROM [TableName]
GROUP BY [Date], OP, Hours, Assets

IF you need help creating a query like this in the SQL view and must use the
query design view, then post back for tedious, step-by-step instructions.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
John,

Thanks I will try this, but I can tell you, I am not that versed on SQL.
So, if you want to put directions in a document and email them to me I would
be up for that.
(e-mail address removed)

Thanks

Erik

John Spencer said:
If a record is a duplicate based on the four fields, what values do you want
to keep in the other fields. If it makes no difference, use a total/aggregate
query as the source of the append query.

SELECT [Date], OP, Hours, Assets
, First([Tablename].[FieldName5]) as FieldName5
, First([Tablename].[FieldName6]) as FieldName6
, First([Tablename].[FieldName7]) as FieldName7
, First([Tablename].[FieldName8]) as FieldName8
, First([Tablename].[FieldName9]) as FieldName9
FROM [TableName]
GROUP BY [Date], OP, Hours, Assets

IF you need help creating a query like this in the SQL view and must use the
query design view, then post back for tedious, step-by-step instructions.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have 9 total fields in each record. It's just that if these 4 match
between records, then it is a duplicate record
Click to expand...
Click to expand...
 
In query design view
==Open a new query
==Add your table
==Select your fields
==Select View: Totals from the menu
==Leave GROUP BY under Date, OP, Hours, and Assets fields
==Change GROUP BY to FIRST under the other fields

By the way it is NOT a good idea to post your actual email address in these
groups. You are opening yourself to getting a lot of spam, since the
<expletive deleted> folks that generate that stuff have software that crawls
the internet looking for such things. Since you only posted it in the body of
the message you might be ok.

You can do things like the following to post your email when needed.
mustangREMOVETHISsailor at Live.xxx replace xxx with com

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John,

Thanks I will try this, but I can tell you, I am not that versed on SQL.
So, if you want to put directions in a document and email them to me I would
be up for that.
(e-mail address removed)

Thanks

Erik

John Spencer said:
If a record is a duplicate based on the four fields, what values do you want
to keep in the other fields. If it makes no difference, use a total/aggregate
query as the source of the append query.

SELECT [Date], OP, Hours, Assets
, First([Tablename].[FieldName5]) as FieldName5
, First([Tablename].[FieldName6]) as FieldName6
, First([Tablename].[FieldName7]) as FieldName7
, First([Tablename].[FieldName8]) as FieldName8
, First([Tablename].[FieldName9]) as FieldName9
FROM [TableName]
GROUP BY [Date], OP, Hours, Assets

IF you need help creating a query like this in the SQL view and must use the
query design view, then post back for tedious, step-by-step instructions.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have 9 total fields in each record. It's just that if these 4 match
between records, then it is a duplicate record

:

If there is no difference between the duplicates, there's no way to tell
Access which one to keep. Your options are:

a) Create a query that groups on your 4 fields (so it only shows one record
where there are duplicates.) Turn it into a Make Table query, to make a new
table. Then throw the old table away.
or
b) Add an AutoNumber field and make it the primary key. You can now
deduplicate with a subquery like this:
http://allenbrowne.com/subquery-01.html#DeDuplicate

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I have a table that is created by an append query from 4 other tables.
When
I use the append query, it creates duplicate records. For a record to be
considered a true duplicate, 4 fields have to match exactly. How do I
create
a query that will delete the duplicates while retaining 1 record?
(example: 4
records are duplicates, and I need to keep one of them while deleting the
other 3) The four fields that match to create a duplicate are "DATE",
"OP",
"HOURS", and "ASSET". Any help is appreciated.

Thanks.

Erik
Click to expand...
Click to expand...
Click to expand...
 
Thanks, I will try this and see if I can get it to work.

John Spencer said:
In query design view
==Open a new query
==Add your table
==Select your fields
==Select View: Totals from the menu
==Leave GROUP BY under Date, OP, Hours, and Assets fields
==Change GROUP BY to FIRST under the other fields

By the way it is NOT a good idea to post your actual email address in these
groups. You are opening yourself to getting a lot of spam, since the
<expletive deleted> folks that generate that stuff have software that crawls
the internet looking for such things. Since you only posted it in the body of
the message you might be ok.

You can do things like the following to post your email when needed.
mustangREMOVETHISsailor at Live.xxx replace xxx with com

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John,

Thanks I will try this, but I can tell you, I am not that versed on SQL.
So, if you want to put directions in a document and email them to me I would
be up for that.
(e-mail address removed)

Thanks

Erik

John Spencer said:
If a record is a duplicate based on the four fields, what values do you want
to keep in the other fields. If it makes no difference, use a total/aggregate
query as the source of the append query.

SELECT [Date], OP, Hours, Assets
, First([Tablename].[FieldName5]) as FieldName5
, First([Tablename].[FieldName6]) as FieldName6
, First([Tablename].[FieldName7]) as FieldName7
, First([Tablename].[FieldName8]) as FieldName8
, First([Tablename].[FieldName9]) as FieldName9
FROM [TableName]
GROUP BY [Date], OP, Hours, Assets

IF you need help creating a query like this in the SQL view and must use the
query design view, then post back for tedious, step-by-step instructions.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Erik wrote:
I have 9 total fields in each record. It's just that if these 4 match
between records, then it is a duplicate record

:

If there is no difference between the duplicates, there's no way to tell
Access which one to keep. Your options are:

a) Create a query that groups on your 4 fields (so it only shows one record
where there are duplicates.) Turn it into a Make Table query, to make a new
table. Then throw the old table away.
or
b) Add an AutoNumber field and make it the primary key. You can now
deduplicate with a subquery like this:
http://allenbrowne.com/subquery-01.html#DeDuplicate

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I have a table that is created by an append query from 4 other tables.
When
I use the append query, it creates duplicate records. For a record to be
considered a true duplicate, 4 fields have to match exactly. How do I
create
a query that will delete the duplicates while retaining 1 record?
(example: 4
records are duplicates, and I need to keep one of them while deleting the
other 3) The four fields that match to create a duplicate are "DATE",
"OP",
"HOURS", and "ASSET". Any help is appreciated.

Thanks.

Erik
Click to expand...
Click to expand...
Click to expand...
 
Back
Top