This accomplishes the same thing but is a little easier on the eyes:
SELECT Field1, COUNT(*) AS Total
FROM has_duplicates
GROUP BY Field1
ORDER BY Field1
Sample table data and output below, I think this is what you were looking
for.
ID Field1 Field2
1 Test1 1
2 Test1 6
3 Test1 1
4 Test2 0
5 Test2 9
6 Test4 1
7 Test4 8
8 Test4 7
9 Test4 3
10 Test8 0
Field1 Total
Test1 3
Test2 2
Test4 4
Test8 1
Erik
----- Original Message -----
From: "Joseph Thompson" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, June 05, 2001 2:03 AM
Subject: Re: Show Duplicates
> Hunted down, found and installed the query analyzer...
>
> It looks odd but it works, Thank you!
>
> <cfquery datasource="cfhub" name="test">
> SELECT [Field1], [ID]
> FROM has_duplicates
> WHERE [Field1] In (SELECT [Field1] FROM [has_duplicates] As Tmp GROUP BY
> [Field1] HAVING Count(*)>1 )
> ORDER BY [Field1];
> </cfquery>
>
>
> >
> > There is a "Find Duplicates Query wizard" in Access
> > In Query, New
> >
> > just put the SQL inside a cfquery tag
> >
> > Seamus
> >
> >
> > At 02:32 pm 5/06/01 , you wrote:
> > >There must be a way to display a list of duplicates from an Access
table.
> > >(going a little batty)
> > >
> > >Say I want to display:
> > >test1 --> 3 occurences
> > >test2 --> 2 ocurences
> > >
> > >TableName: has_duplicates
> > >Fields:
> > >
> > >ID Field1 Field2
> > >1 test1 1
> > >2 test2 2
> > >3 test3 3
> > >4 test1 4
> > >5 test2 5
> > >6 test3 6
> > >7 test1 1
> > >
> > >
> > >(Not "important" but... I was a little disturbed that I couldn't figure
> it
> > >out)
> > >
> > >Thanks for any help :)
> > >
> > >
> > >
> > >
> > >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
