Mary said:
Hi - Ok this may be simple but I am not sure how to do If
I ran a qry on a table using the duplicate wizard - I get
a listing of all duplicates. I know for certain that I
want to delete all but one record with that value ie name
and address- How do I prevent from deleting all when I
change the qry to a delete qry-- What expression would I
need in the qry to just delete the value whose count is
greater than 1.... Yikes please help
Hi Mary,
If your table were named "tbl"
with fields:
ID
AName
Address
{other don't care fields}
and you want to delete duplicate
records in "tbl" that have the same
[AName] and [Address],
a typical method for doing this is:
(apologies if you already know some of this)
1) make a copy of your table (structure only).
-right-mouse click on "tbl" and choose "Copy"
-right-mouse click in any blank area of table window
and choose "Paste"
-in dialog that comes up, give your new table a name
(say "tblNoDups") and choose "Structure Only"
2) remove the primary key from this new table.
-click on your new table and choose "Design"
-click on any field that has a "key" at the left
and toggle off by then clicking on key in
top menu.
3) make [AName] and [Address] your new primary key.
-holding down CTRL key, click on both of them
then click on key in top menu.
4) save the new table.
5) Make an append query that appends all records
from original table to the new table.
-in the Queries window, double-click on
"Create query in Design View"
- in the "Show Table" dialog box, select your
original table and click on "Add", then "Close"
- drag and drop the "*" from the table down to a Field row
in the grid
- in the very top menu select "Query/Append Query"
- in the Append dialog box, select your new table
from the Table Name drop down box, then "OK"
- run this append query by clicking on the red exclamation
mark in the top menu.
6) After the append (which will not allow duplicates on the
primary key we defined), name your original table "oldtbl"
and the new table "tbl"
7) change the primary key back to how it was.
- click on your "new table" and choose "Design"
-click on each field that has a "key" at the left
and toggle off by then clicking on key in
top menu.
- click on the field that was your original primary
key, then click on key in top menu.
- save changes
Please respond back if I have misunderstood.
Good luck,
Gary Walter
