Don't use more than one duplicate

B

bw

When I have duplicate information, I only want to include one of them in my
report.
I have a "Find duplicates" query as follows:

SELECT qryModules.BankName, qryModules.[Acct Mgr], qryModules.DBNum,
qryModules.[Inst Num], qryModules.Mnemonic
FROM qryModules
WHERE (((qryModules.BankName) In (SELECT [BankName] FROM [qryModules] As Tmp
GROUP BY [BankName],[Acct Mgr],[DBNum],[Inst Num] HAVING Count(*)>1 And
[Acct Mgr] = [qryModules].[Acct Mgr] And [DBNum] = [qryModules].[DBNum] And
[Inst Num] = [qryModules].[Inst Num])))
ORDER BY qryModules.BankName, qryModules.[Acct Mgr], qryModules.DBNum,
qryModules.[Inst Num];

I thought using DISTINCTROW as follows would do that for my report, but each
of these produces exactly the same results. Can someone explain?

SELECT DISTINCTROW qryModules.BankName, qryModules.[Acct Mgr],
qryModules.DBNum, qryModules.[Inst Num], qryModules.Mnemonic
FROM qryModules
WHERE (((qryModules.BankName) In (SELECT [BankName] FROM [qryModules] As Tmp
GROUP BY [BankName],[Acct Mgr],[DBNum],[Inst Num] HAVING Count(*)>1 And
[Acct Mgr] = [qryModules].[Acct Mgr] And [DBNum] = [qryModules].[DBNum] And
[Inst Num] = [qryModules].[Inst Num])))
ORDER BY qryModules.BankName, qryModules.[Acct Mgr], qryModules.DBNum,
qryModules.[Inst Num];
 
K

KARL DEWEY

Try this --
SELECT qryModules.BankName, qryModules.[Acct Mgr], qryModules.DBNum,
qryModules.[Inst Num], qryModules.Mnemonic
FROM qryModules
GROUP BY qryModules.BankName, qryModules.[Acct Mgr], qryModules.DBNum,
qryModules.[Inst Num], qryModules.Mnemonic
HAVING (((Count(qryModules.BankName))>1));
 
B

bw

Thanks Karl, but that doesn't give me any records...either.
This is strange, and has me stumped.
Bernie


KARL DEWEY said:
Try this --
SELECT qryModules.BankName, qryModules.[Acct Mgr], qryModules.DBNum,
qryModules.[Inst Num], qryModules.Mnemonic
FROM qryModules
GROUP BY qryModules.BankName, qryModules.[Acct Mgr], qryModules.DBNum,
qryModules.[Inst Num], qryModules.Mnemonic
HAVING (((Count(qryModules.BankName))>1));

--
KARL DEWEY
Build a little - Test a little


bw said:
When I have duplicate information, I only want to include one of them in
my
report.
I have a "Find duplicates" query as follows:

SELECT qryModules.BankName, qryModules.[Acct Mgr], qryModules.DBNum,
qryModules.[Inst Num], qryModules.Mnemonic
FROM qryModules
WHERE (((qryModules.BankName) In (SELECT [BankName] FROM [qryModules] As
Tmp
GROUP BY [BankName],[Acct Mgr],[DBNum],[Inst Num] HAVING Count(*)>1 And
[Acct Mgr] = [qryModules].[Acct Mgr] And [DBNum] = [qryModules].[DBNum]
And
[Inst Num] = [qryModules].[Inst Num])))
ORDER BY qryModules.BankName, qryModules.[Acct Mgr], qryModules.DBNum,
qryModules.[Inst Num];

I thought using DISTINCTROW as follows would do that for my report, but
each
of these produces exactly the same results. Can someone explain?

SELECT DISTINCTROW qryModules.BankName, qryModules.[Acct Mgr],
qryModules.DBNum, qryModules.[Inst Num], qryModules.Mnemonic
FROM qryModules
WHERE (((qryModules.BankName) In (SELECT [BankName] FROM [qryModules] As
Tmp
GROUP BY [BankName],[Acct Mgr],[DBNum],[Inst Num] HAVING Count(*)>1 And
[Acct Mgr] = [qryModules].[Acct Mgr] And [DBNum] = [qryModules].[DBNum]
And
[Inst Num] = [qryModules].[Inst Num])))
ORDER BY qryModules.BankName, qryModules.[Acct Mgr], qryModules.DBNum,
qryModules.[Inst Num];
Click to expand...
Click to expand...
 
J

John Spencer

First, try DISTINCT instead of DistinctRow. Distinct limits the
"duplication" based on the fields in the select clause. DistinctRow limits
the "duplication" based on all the fields in the table(s).

SELECT DISTINCT qryModules.BankName, qryModules.[Acct Mgr],
qryModules.DBNum, qryModules.[Inst Num], qryModules.Mnemonic
FROM qryModules
WHERE (((qryModules.BankName) In (SELECT [BankName] FROM [qryModules] As Tmp
GROUP BY [BankName],[Acct Mgr],[DBNum],[Inst Num] HAVING Count(*)>1 And
[Acct Mgr] = [qryModules].[Acct Mgr] And [DBNum] = [qryModules].[DBNum] And
[Inst Num] = [qryModules].[Inst Num])))
ORDER BY qryModules.BankName, qryModules.[Acct Mgr], qryModules.DBNum,
qryModules.[Inst Num];

And perhaps all you really need is
SELECT DISTINCT qryModules.BankName, qryModules.[Acct Mgr],
qryModules.DBNum, qryModules.[Inst Num], qryModules.Mnemonic
FROM qryModules
ORDER BY qryModules.BankName, qryModules.[Acct Mgr], qryModules.DBNum,
qryModules.[Inst Num];

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
B

bw

Okay John, that worked just fine. I now see that my problem was including
additional fields that did not have duplicate values.
I appreciate your help.
Also, thanks for defining DISTINCT vs DISTINCTROW. That also helped...
Bernie


John Spencer said:
First, try DISTINCT instead of DistinctRow. Distinct limits the
"duplication" based on the fields in the select clause. DistinctRow
limits the "duplication" based on all the fields in the table(s).

SELECT DISTINCT qryModules.BankName, qryModules.[Acct Mgr],
qryModules.DBNum, qryModules.[Inst Num], qryModules.Mnemonic
FROM qryModules
WHERE (((qryModules.BankName) In (SELECT [BankName] FROM [qryModules] As
Tmp
GROUP BY [BankName],[Acct Mgr],[DBNum],[Inst Num] HAVING Count(*)>1 And
[Acct Mgr] = [qryModules].[Acct Mgr] And [DBNum] = [qryModules].[DBNum]
And
[Inst Num] = [qryModules].[Inst Num])))
ORDER BY qryModules.BankName, qryModules.[Acct Mgr], qryModules.DBNum,
qryModules.[Inst Num];

And perhaps all you really need is
SELECT DISTINCT qryModules.BankName, qryModules.[Acct Mgr],
qryModules.DBNum, qryModules.[Inst Num], qryModules.Mnemonic
FROM qryModules
ORDER BY qryModules.BankName, qryModules.[Acct Mgr], qryModules.DBNum,
qryModules.[Inst Num];

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

bw said:
When I have duplicate information, I only want to include one of them in
my report.
I have a "Find duplicates" query as follows:

SELECT qryModules.BankName, qryModules.[Acct Mgr], qryModules.DBNum,
qryModules.[Inst Num], qryModules.Mnemonic
FROM qryModules
WHERE (((qryModules.BankName) In (SELECT [BankName] FROM [qryModules] As
Tmp GROUP BY [BankName],[Acct Mgr],[DBNum],[Inst Num] HAVING Count(*)>1
And [Acct Mgr] = [qryModules].[Acct Mgr] And [DBNum] =
[qryModules].[DBNum] And [Inst Num] = [qryModules].[Inst Num])))
ORDER BY qryModules.BankName, qryModules.[Acct Mgr], qryModules.DBNum,
qryModules.[Inst Num];

I thought using DISTINCTROW as follows would do that for my report, but
each of these produces exactly the same results. Can someone explain?

SELECT DISTINCTROW qryModules.BankName, qryModules.[Acct Mgr],
qryModules.DBNum, qryModules.[Inst Num], qryModules.Mnemonic
FROM qryModules
WHERE (((qryModules.BankName) In (SELECT [BankName] FROM [qryModules] As
Tmp GROUP BY [BankName],[Acct Mgr],[DBNum],[Inst Num] HAVING Count(*)>1
And [Acct Mgr] = [qryModules].[Acct Mgr] And [DBNum] =
[qryModules].[DBNum] And [Inst Num] = [qryModules].[Inst Num])))
ORDER BY qryModules.BankName, qryModules.[Acct Mgr], qryModules.DBNum,
qryModules.[Inst Num];
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

OUTER join problem 8
Joining 3 Queries That Count 8
DCount? 2
Transpose data in a report-Field values broken down to separate co 9
Query and Duplicate Rows 2
Sum Miscalculation Help 1
Report breaks on dynamic recordsource, but returns correct data 0
PLEASE HELP!!!! with Date range and InStr function. 2

Top