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! 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/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
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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
