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