Re: Show Duplicates
SELECT Field1, Count(Field1) as Total_Number FROM Has_Duplicates GROUP BY Field1 Output should read: Test13 Test22 Test32 etc... (query syntax untested) HTH Brian - Original Message - From: "Joseph Thompson" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, June 04, 2001 11:32 PM Subject: Show Duplicates > 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Show Duplicates
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 Test13 Test22 Test44 Test81 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! > > > 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]; > > > > > > > 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Show Duplicates
Hunted down, found and installed the query analyzer... It looks odd but it works, Thank you! 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]; > > 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Show Duplicates
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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Show Duplicates
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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
