Query fails to eliminate duplicate information

[EagleOne]

2003/2007


The following table is produced by the query below.

TABLE: TransPerGetAcctTbl

Tran Acct AccountDesc ContraAccDesc
74709 1410 Loan Recv 8500 · Inter Inc
74710 1410 Loan Recv 8500 · Inter Inc
74713 1410 Loan Recv 8500 · Inter Inc
75388 1410 Loan Recv 8500 · Inter Inc
75388 1410 Loan Recv 8500 · Inter Inc -Duplicate-
76937 1410 Loan Recv 8500 · Inter Inc
76938 1410 Loan Recv 8500 · Inter Inc
76939 1410 Loan Recv 8500 · Inter Inc

strSQL = "SELECT DISTINCT TransactionsTbl.Tran, TransactionsTbl.Acct, _
TransactionsTbl.AccountDesc, TransactionsTbl.ContraAccDesc" _
INTO TransPerGetAcctTbl FROM TransactionsTbl
WHERE TransactionsTbl.AccountNumber='1410';"
DoCmd.RunSQL strsql, dbFailOnError

Notice the duplicate "Tran" number 75388.

The TransactionsTbl does have multiple "Rows" of the same transaction number but with (usually)
different dollar amounts therefore, it is not an error.

That said, the query causes duplicate information the TransPerGetAcctTbl.

How can I re-write the query so as to eliminate the duplicate in TransPerGetAcctTbl?

Any thoughts appreciated.

TIA EagleOne

 

[Allen Browne]

Suggestions:

1. Run a compact/repair, in case a bad index is failing to deduplicate. If
the data is attached from another database, run the compact/repair on that
database.

2. Instead of RunSQL, try:
dbEngine(0)(0).Execute strSql, dbFailOnError
This will let you know if there is an error.

3. Examine the data type of these fields. Any floating point fields (Number
of size Single or Double) may appear to be the same, but be marginally
different. Any Memo type fields could be truncating. If all fields are
Number (size Long or Integer), or Text, this issue should not apply. (I
assume the text "-Duplicate-" was for our benefit, and is not actually in
the field.)


4. If the data is actually coming from another query (not directly from the
table), the problem could be deeper down (e.g. outer joins, subquery issues,
or data types.)

5. If it still fails, Debug.Print strSQL so you get exactly the query will
use in code. Copy it from the Immediate Window (Ctrl+G) into SQL View of a
new query, and switch to datasheet view. Does the problem still show up
here?

If so, the next step to debug this is to break the problem down until it
disappears. Drop the INTO clause (so it becomes just a SELECT query.) Add
criteria for just the problem transaction (75388.) If the problem remains,
examine this data closely. Is there something weird here, e.g. press
Shift+F2 in each field to see if there is a 2nd line that's not showing in
query view.

6. If that still doesn't solve it, create a query using 2 copies of this
TransactionsTbl, inner joined on all 4 fields. Set criteria for
AccountNumber 1410, and transaction 75388. See if JET matches both records
(in which case they really are duplicates), or if it matches no records (in
which case there is a difference in the data that you have not observed
yet.)

 

[EagleOne]

Thank you for your time and knowledge.

I did run the query in the immediate window. I will try your thoughts in the morning US EDST.

Because I wanted "Distinct" as to Tran '75388' I got back up from bed to try (to no avail):

strSQL = "SELECT
TransactionsTbl.Acct, _
TransactionsTbl.AccountDesc, _
TransactionsTbl.ContraAccDesc" _
DISTINCT TransactionsTbl.Tran, _
INTO TransPerGetAcctTbl _
FROM TransactionsTbl
WHERE TransactionsTbl.AccountNumber='1410';"
DoCmd.RunSQL strsql, dbFailOnError

 

[John W. Vinson]

Thank you for your time and knowledge.

I did run the query in the immediate window. I will try your thoughts in the morning US EDST.

Because I wanted "Distinct" as to Tran '75388' I got back up from bed to try (to no avail):

strSQL = "SELECT
TransactionsTbl.Acct, _
TransactionsTbl.AccountDesc, _
TransactionsTbl.ContraAccDesc" _
DISTINCT TransactionsTbl.Tran, _
INTO TransPerGetAcctTbl _
FROM TransactionsTbl
WHERE TransactionsTbl.AccountNumber='1410';"
DoCmd.RunSQL strsql, dbFailOnError

<g> I'm guessing you were a bit too sleepy...

The DISTINCT predicate must come immediately after the SELECT. I'd try

INSERT INTO TransPerGetAcctTbl
(Acct, AccountDesc, ContraAccDesc, Tran)
SELECT DISTINCT Acct, AccountDesc, ContraAccDesc, Tran
FROM TransactionsTbl
WhERE TransactionsTbl.AccountNumber = '1410';

as the query, if the intention is to insert those fields (redundantly???) into
TransPerGetAcctTbl.

 

[EagleOne]

Actually, John, I did intend that code as a what-the-hell-why-not-experiment basis.

You did get to the root of the problem. Apparently, all fields to the right of DISTINCT are
considered in aggregate, as for DISTINCT, meaning the four together are DISTINCT.

As I was (to your point) knowingly I was being redundant while I only needed the Tran number in
subsequent queries. When I eliminated the redundant fields, no "duplication" occured

Thanks!