help with Duplicates Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

good day all,

I have the following code that I use to find duplicates withing my
checking account .

SELECT qrycheckingaccountfullreporting.Date,
qrycheckingaccountfullreporting.Debit_Credit,
Count(qrycheckingaccountfullreporting.Debit_Credit) AS CountOfDebit_Credit
FROM qrycheckingaccountfullreporting
GROUP BY qrycheckingaccountfullreporting.Date,
qrycheckingaccountfullreporting.Debit_Credit
HAVING (((Count(qrycheckingaccountfullreporting.Debit_Credit))>1));

What I would like to to do is have a small form or something that I can
open and have the option to delete one of the duplicates of my choice? How
would I go about doing that? Any ideas ...suggestions?

Thanks,

Brook
 
Brook

A different approach to cleaning up a table with duplicates is to create a
new table, create a query in which you set the Unique Values property, and
append (only unique records) to the new table. This works well for a
one-time cleanup.

If you expect to have ongoing issues with duplicate records, perhaps you'll
want to look into why/how duplicate records get entered?

Regards

Jeff Boyce
<Office/Access MVP>
 
Hello Jeff,

Thanks for the post... Well, what i'm looking for is something that would
be run on a periodic basis... mabye every two weeks. So do you think the
first option would be something I should research futher?

Thanks,

BRook
 
Brook

The need for a periodic cleanup presents an ... "opportunity". By all
means, feel free to take the system down and do the "manual" labor of
removing duplicates and restoring table names to the ones that your queries,
forms and reports know.

The longer term issue is figuring out if there's a way to prevent
duplication of records in the first place...
(unless this is a job-security issue, in which case, the first option offers
more work <g>).
 
Jeff,

Well, your right about the fact that I need to find out why there are
duplicate values in my table, but I also would like an easy way to find the
duplicates and delete the duplicate value of my choice...

If there is not a way just let me know...

brook
 
Brook

Please re-check my first response. If it wasn't enough information about
how to approach your solution, re-post.
 
Hello Jeff,

i'm sorry that I don't understand your first response... I don't
understand the setting of the unique properties that your mentioning..

Brook
 
When you've got the query open in design mode, select Views | Properties
from the menu.

If the Properties box that opens up has "Field Properties" at the top, click
in the top part of the query builder (where the tables are, above the grid)
so that it says "Query Properties". Right near the top of the properties
(depending on the version of Access, it should be the 4th or 5th property)
is a property labelled "Unique Values". Choose Yes for that property.
 
Thanks...

Once I do that what do I need to do? should my query be updated with
anything?

Brook
 
Jeff Boyce suggested writing an Append Query to append to your Table
only the unique records you wish to append. In Query Design View, you
can do this by clicking on the "!" icon. You'll probably get a dialog
box asking if you really want to append the 51 new records to your Table
and warning you that once they're appended, you're stuck with having
them in your Table, even if some of what you wanted to add might be
missing or be duplicates. (That's part of why you will FIRST have made
a backup copy of your database, or at least have copied the Table and
saved it using a new name!)

If you have trouble opening the Properties window that Doug Steele
suggested, another way to do the same thing is to open your Query in SQL
view and insert, after the word "UPDATE", the word "DISTINCTROW".

Incidentally, I have a personal situation somewhat similar to yours. I
import bank-statement records every few days into my database. Each one
contains about a month's supply of records, so there's almost always a
bunch of duplication. What I do is to import them from the bank's Web
site into a temporary Table, then run a Delete Query that erases any
records in the temporary Table that also exist in the main Table.
Having done that, I append the entire (remaining) contents of the
temporary Table to the main Table, and I have no trouble with duplicate
records in my main Table.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Thanks....

I'm getting there... I have my tblcheckingtemp, qryappendcheckingnew, and
my qryappendunique values, but I have one other question... can I check for
only one unique value? my bank assigns a transaction code to all transactions
and is unique for each transaction, can I peform a unique value append only
for this value?

Brook
 
Hello,

I am getting there....

I have my tblchecking, tblcheckingtmp, qryappendcheckingtmp, but for the
append qry I was wondering if I could append all the record data based on one
value and that one value being a unique transation ID the bank assigns to
transactions?

Thanks,

BRook
 
Not knowing what's in your Queries and Tables, I'm not sure. Maybe this
will help...

Suppose your Table (or Query) contains records like the following. I
used a random Autonumber field to simulate the bank's transaction number:

[qrycheckingaccountfullreporting] Datasheet View:
transactionID Date Debit_Credit
------------- ---------- ------------
17328758 11/6/2005 $10.00
-100316567 11/8/2005 $18.00
1131879688 11/8/2005 $18.00
444303744 11/8/2005 $15.00
-940653093 11/10/2005 $10.00
-1426436222 11/10/2005 $10.00

Then the following Query will list each duplicate record, including its
[transactionID] value:

[Q_ListDuplicates] SQL
SELECT [qrycheckingaccountfullreporting].[Date],
[qrycheckingaccountfullreporting].[Debit_Credit],
[qrycheckingaccountfullreporting].[transactionID]
FROM qrycheckingaccountfullreporting,
qrycheckingaccountfullreporting AS Copy1
WHERE (((Copy1.Date)
=[qrycheckingaccountfullreporting]![Date]) AND
((Copy1.Debit_Credit)
=[qrycheckingaccountfullreporting]![Debit_Credit]))
GROUP BY [qrycheckingaccountfullreporting].[Date],
[qrycheckingaccountfullreporting].[Debit_Credit],
[qrycheckingaccountfullreporting].[transactionID]
HAVING (((Count(Copy1.transactionID))>1))
ORDER BY [qrycheckingaccountfullreporting].[Date],
[qrycheckingaccountfullreporting].[transactionID];

[Q_ListDuplicates] Query Datasheet View:
Date Debit_Credit transactionID
---------- ------------ -------------
11/8/2005 $18.00 -100316567
11/8/2005 $18.00 1131879688
11/10/2005 $10.00 -1426436222
11/10/2005 $10.00 -940653093

Now that you have a list of the suspicious [transactionID] values, you
could do various things with them, such as using a Make-Table Query to
store them in a Table that you could edit.

If your records already include the bank's [transactionID] value, then I
think you should include that as a field in your main Table and
declaring the field to be unique there, so you would not be able to
store duplicate records. (If you then try to store a record with a
duplicate value, Access will complain.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Back
Top