Re: Deleting duplicate records/same table

Yes, you can rename them after the table is created (in table design view),
removing the prefix "last". It could bring some confusion if you do it
before, since the same name will be used to describe the new result, and the
original value (eventually skipped out, but eventually kept too).


Vanderghast, Access MVP



"Stacie2410" <Stacie2410@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F353CFC0-D4E0-4226-9054-27E74267D43F@xxxxxxxxxxxxxxxx
When I create the new table "tblUniqueRecords", it's naming all my fields
the
same, except with "Lastof" infront of it. For instance instead of
"mPreparedBy", it's now "LastofmPreparedBy". For this reason, it won't
append the tblUniqueRecords fields into the fields on tblMaster. Is there
any way to avoid the renaming?

"Michel Walsh" wrote:

1- Make a backup.

2- You make the new table, tblUniqueRecords

3- You delete all records from tblMaster.

4- You append the records from tblUniqueRecords into tye now empty
tblMaster

5- You drop the table tblUniqueRecords.

6- Add the index not allowing dup in tblMaster


Step 3 may delete records from other tables if you have defined CASCADE
DELETE relations, which is what we don't want, in this case. That is why
step 1 is important.




Vanderghast, Access MVP



"Stacie2410" <Stacie2410@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EE74A7BA-BDE5-4482-9960-B092098BF96C@xxxxxxxxxxxxxxxx
Ok, I've done this, and it works, but I have one problem. The table I
used
was called tblMaster. The new table without the duplicates is called
tblUniqueRecords. If I delete the tblMaster and rename
tblUniqueRecords
to
now be tblMaster, is it going to mess up the rest of the database that
is
tied to tblMaster? I did this, and tried to run a switchboard item
that
runs
a macro and a query but it gives me a "Enter Parameter Box" that says
"tbl.Master .mPreparedBy". Prepared By is the type of query I was
trying
to
run (based off of someone's name).

Did I do something wrong?
"Michel Walsh" wrote:

You are using Jet, not a table linked to MS SQL Server? If you use
Jet,
you
should end up with one record out of the 5-dups.


An alternative is to make a group by query, then, make a table out of
it.
In
the query designer, bring your table. Click on the summation button,
the
one
with the capital Sigma, a rotated M. A new line, total, appears in the
grid.
Bring the field that should not be duplicated in the grid, keep the
GROUP
BY. Bring all other fields, change the GROUP BY to LAST. Run the query
to
see that you get what you want. Save the query. Create a second query,
a
'MAKE TABLE' query, based on the query you just saved. Once your new
table
is created, add the index not allowing duplicated values, so the
problem
won't re-occur.




Hoping it may help,
Vanderghast, Access MVP


"Stacie2410" <Stacie2410@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DE54F8F9-DC3F-4DCC-82D9-46ED6F013FC7@xxxxxxxxxxxxxxxx
That's actually what I tried, but the results were only the records
that
never had a duplicate, which was about 20 records. The other 2000+
records
didn't come over. Basically, anything that ever had a duplicate of
it,
didn't
make it.

Example: There were 5 of each records. When I did the append query,
it
didn't bring over any of those 5, and I wanted it to bring over 1,
and
leave
the other 4.

I don't know if this makes sense, I'm probably not explaining it
very
well,
my knowledge of Access isn't as extensive as I'd like it to be.

"Michel Walsh" wrote:

Don't delete, append to a new table with an index not allowing
duplicated
values. When you will append the data, some records won't be added
because
they would create a dup. This will be reported as a possible error,
but
that
is exactly what you want.


Hoping it may help,
Vanderghast, Access MVP


"Stacie2410" <Stacie2410@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:724398E1-F06E-45E4-9F42-9CD5A79FA112@xxxxxxxxxxxxxxxx
I'm having trouble trying to delete duplicate records in a same
table.

What I've got, is one table, that has around 12,000 records.
What
it
should
have, is around 2,000 records. What has happened, is that the
original
2,000
records somehow were duplicated 5 times each, and these
duplicates
need
to
be
deleted. I do not have a primary key set at the moment, but I do
have
a
field called "Record Number" that needs to be unique.

I've ran some other queries that will get rid of duplicates, but
what
it
did, was delete anything that was a duplicate of anything else.
For
instance,
if there were 5 of one record, it would delete all 5, rather than
deleting
4,
and leaving 1. There's no unique factor about each row, such as
a
more
current date, etc, they are all identical, so I don't know how to
tell
it
to
keep one, but trash the rest.

Any help you can provide is GREATLY appreciated. I'm pulling out
my
hair
trying to fix this for the past 5 days.

Thank you!











.

Quantcast