SQL code that looks for Duplicates

[Guest]

I am trying to query for duplicate cheques numbers (where the currency of the
cheque is the same). The following works when only a single currency is
involved.
Note the cheques are recorded in 2 tables [Cheques to Assignor] and Cheques
to Consultant].


SELECT ChequeNumber
FROM [Cheques to Assignor]
UNION ALL SELECT ConsChequeNumber
FROM [Cheques to Consultant];



SELECT First([Find Duplicate Cheques subquery].ChequeNumber) AS
[ChequeNumber Field], Count([Find Duplicate Cheques subquery].ChequeNumber)
AS NumberOfDups
FROM [Find Duplicate Cheques subquery]
GROUP BY [Find Duplicate Cheques subquery].ChequeNumber
HAVING (((Count([Find Duplicate Cheques subquery].ChequeNumber))>1));


Each cheque is associated with a [CURRENCYID]. How can I amend this coed
to ensure that duplicates are only found where the [CURRENCYID] field is the
same ?

Thanks.

 

[Ken Snell \(MVP\)]

Add the CURRENCYID field to the Group By clause:

SELECT [Find Duplicate Cheques subquery].ChequeNumber AS
[ChequeNumber Field],
[Find Duplicate Cheques subquery].CURRENCYID AS CurrID,
Count([Find Duplicate Cheques subquery].ChequeNumber)
AS NumberOfDups
FROM [Find Duplicate Cheques subquery]
GROUP BY [Find Duplicate Cheques subquery].ChequeNumber,
[Find Duplicate Cheques subquery].CURRENCYID
HAVING (((Count([Find Duplicate Cheques subquery].ChequeNumber))>1));

 

[Guest]

Thanks.

Since I know nothing about SQL could you let me know how to add the
CURRENCYID as CurrID withihn the Find Duplicate Cheques subquery.

Currently its:

SELECT ChequeNumber
FROM [Cheques to Assignor]
UNION ALL SELECT ConsChequeNumber
FROM [Cheques to Consultant];


FJ

Add the CURRENCYID field to the Group By clause:

SELECT [Find Duplicate Cheques subquery].ChequeNumber AS
[ChequeNumber Field],
[Find Duplicate Cheques subquery].CURRENCYID AS CurrID,
Count([Find Duplicate Cheques subquery].ChequeNumber)
AS NumberOfDups
FROM [Find Duplicate Cheques subquery]
GROUP BY [Find Duplicate Cheques subquery].ChequeNumber,
[Find Duplicate Cheques subquery].CURRENCYID
HAVING (((Count([Find Duplicate Cheques subquery].ChequeNumber))>1));


--

Ken Snell
<MS ACCESS MVP>

I am trying to query for duplicate cheques numbers (where the currency of
the
cheque is the same). The following works when only a single currency is
involved.
Note the cheques are recorded in 2 tables [Cheques to Assignor] and
Cheques
to Consultant].


SELECT ChequeNumber
FROM [Cheques to Assignor]
UNION ALL SELECT ConsChequeNumber
FROM [Cheques to Consultant];



SELECT First([Find Duplicate Cheques subquery].ChequeNumber) AS
[ChequeNumber Field], Count([Find Duplicate Cheques
subquery].ChequeNumber)
AS NumberOfDups
FROM [Find Duplicate Cheques subquery]
GROUP BY [Find Duplicate Cheques subquery].ChequeNumber
HAVING (((Count([Find Duplicate Cheques subquery].ChequeNumber))>1));


Each cheque is associated with a [CURRENCYID]. How can I amend this
coed
to ensure that duplicates are only found where the [CURRENCYID] field is
the
same ?

Thanks.

 

[Ken Snell \(MVP\)]

Sure, sorry about that:

SELECT ChequeNumber, CurrencyID
FROM [Cheques to Assignor]
UNION ALL SELECT ConsChequeNumber, CurrencyID
FROM [Cheques to Consultant];

--

Ken Snell
<MS ACCESS MVP>

Thanks.

Since I know nothing about SQL could you let me know how to add the
CURRENCYID as CurrID withihn the Find Duplicate Cheques subquery.

Currently its:

SELECT ChequeNumber
FROM [Cheques to Assignor]
UNION ALL SELECT ConsChequeNumber
FROM [Cheques to Consultant];


FJ

Add the CURRENCYID field to the Group By clause:

SELECT [Find Duplicate Cheques subquery].ChequeNumber AS
[ChequeNumber Field],
[Find Duplicate Cheques subquery].CURRENCYID AS CurrID,
Count([Find Duplicate Cheques subquery].ChequeNumber)
AS NumberOfDups
FROM [Find Duplicate Cheques subquery]
GROUP BY [Find Duplicate Cheques subquery].ChequeNumber,
[Find Duplicate Cheques subquery].CURRENCYID
HAVING (((Count([Find Duplicate Cheques subquery].ChequeNumber))>1));


--

Ken Snell
<MS ACCESS MVP>

I am trying to query for duplicate cheques numbers (where the currency
of
the
cheque is the same). The following works when only a single currency
is
involved.
Note the cheques are recorded in 2 tables [Cheques to Assignor] and
Cheques
to Consultant].


SELECT ChequeNumber
FROM [Cheques to Assignor]
UNION ALL SELECT ConsChequeNumber
FROM [Cheques to Consultant];



SELECT First([Find Duplicate Cheques subquery].ChequeNumber) AS
[ChequeNumber Field], Count([Find Duplicate Cheques
subquery].ChequeNumber)
AS NumberOfDups
FROM [Find Duplicate Cheques subquery]
GROUP BY [Find Duplicate Cheques subquery].ChequeNumber
HAVING (((Count([Find Duplicate Cheques subquery].ChequeNumber))>1));


Each cheque is associated with a [CURRENCYID]. How can I amend this
coed
to ensure that duplicates are only found where the [CURRENCYID] field
is
the
same ?

Thanks.

 

[Guest]

Worked like a charm. Thanks a lot.

Sure, sorry about that:

SELECT ChequeNumber, CurrencyID
FROM [Cheques to Assignor]
UNION ALL SELECT ConsChequeNumber, CurrencyID
FROM [Cheques to Consultant];

--

Ken Snell
<MS ACCESS MVP>

Thanks.

Since I know nothing about SQL could you let me know how to add the
CURRENCYID as CurrID withihn the Find Duplicate Cheques subquery.

Currently its:

SELECT ChequeNumber
FROM [Cheques to Assignor]
UNION ALL SELECT ConsChequeNumber
FROM [Cheques to Consultant];


FJ

Add the CURRENCYID field to the Group By clause:

SELECT [Find Duplicate Cheques subquery].ChequeNumber AS
[ChequeNumber Field],
[Find Duplicate Cheques subquery].CURRENCYID AS CurrID,
Count([Find Duplicate Cheques subquery].ChequeNumber)
AS NumberOfDups
FROM [Find Duplicate Cheques subquery]
GROUP BY [Find Duplicate Cheques subquery].ChequeNumber,
[Find Duplicate Cheques subquery].CURRENCYID
HAVING (((Count([Find Duplicate Cheques subquery].ChequeNumber))>1));


--

Ken Snell
<MS ACCESS MVP>

I am trying to query for duplicate cheques numbers (where the currency
of
the
cheque is the same). The following works when only a single currency
is
involved.
Note the cheques are recorded in 2 tables [Cheques to Assignor] and
Cheques
to Consultant].


SELECT ChequeNumber
FROM [Cheques to Assignor]
UNION ALL SELECT ConsChequeNumber
FROM [Cheques to Consultant];



SELECT First([Find Duplicate Cheques subquery].ChequeNumber) AS
[ChequeNumber Field], Count([Find Duplicate Cheques
subquery].ChequeNumber)
AS NumberOfDups
FROM [Find Duplicate Cheques subquery]
GROUP BY [Find Duplicate Cheques subquery].ChequeNumber
HAVING (((Count([Find Duplicate Cheques subquery].ChequeNumber))>1));


Each cheque is associated with a [CURRENCYID]. How can I amend this
coed
to ensure that duplicates are only found where the [CURRENCYID] field
is
the
same ?

Thanks.