Find records with a duplicate field entry

[jclu]

Hi, I have a cross-ref table (called MFC_CIBC_XREF) which links a bank account to a fund number and a general ledger number. It should be a unique relationship, wherein (the "=" means corresponds/links to)

Bank Account # "=" Fund #

For each bank account, there are sometimes multiple currencies being used, so each currency then links to a general ledger account, such that

Bank Account #.Currency = Fund #.General Ledger #

Sorry, if that's kinda cryptic, but here's an example:

Bank Account - ABCD123
Currencies operated in - CAD, USD
Fund # - F30

Based on the above, ABCD123 "=" F30, and including the general ledger numbers, ABCD123.CAD "=" F30.100, ABCD123.USD "=" F30.8121.

Basically, it's a way to keep track of not just the transactions for a particular bank account, but for the transactions in a particular currency.

Anyway, as I said, it's supposed to be a 1-to-1 relationship: each BankAccount.Currency should correspond to 1 and only 1 Fund.GeneralLedger. However, there are some entries in the table which have each BankAccount.Currency corresponding to multiple General Ledger numbers. Using my example above, ABCD123.CAD has two records in the table, one corresponding to F30.100, and another corresponding to F30.8101. This was probably because F30.8101 used to be linked to a different bank account, that got merged (ie, it might have been linked to ABCD124.CAD that then got merged into ABCD123.CAD).

Now I want to run a query on MFC_CIBC_XREF and find all the records where for each BankAccount.Currency, there is more than one Fund.GeneralLedger. I don't really know that much SQL, and even in Design View, I'm not sure of the statements to use. Any help is much appreciated.

 

[ajetrumpet]

Now I want to run a query on MFC_CIBC_XREF and find all the records where for each BankAccount.Currency, there is more than one Fund.GeneralLedger.

Well hello again jclu...long time no talk to...

I would have a look at the query wizard, there is an option in there to build a "duplicates" query. I don't think you can get exactly what you need from there, but it will produce an SQL statement for you that will be of some help.

I am thinking something like...

SELECT [fields]
FROM [table]
WHERE Fund.GeneralLedger IN
(SELECT Fund.GeneralLedger FROM [table]
GROUP BY Fund.GeneralLedger HAVING COUNT>1);

 

[jclu]

Yes ajetrumpet, it's been a while

Thanks for the help; the code worked, but it turned out I needed to looked for BankAccount.Currency HAVING COUNT > 1.