> I want to output all records in a table where a certain
> field is duplicated at least once. My first instinct is
> to filter in the SQL - but how?! I'm using Access 2000,
> and the field in question is VARCHAR.
>
> DISTINCT doesn't *seem* to support being negated with NOT,
> and anyway, isn't it only meant for numeric fields?
>
> Will I have to run some complex CF code to rebuild the
> query once it's returned?
If you want to include records with duplicate field values, you just omit
DISTINCT - that's all there is to it. You can use DISTINCT with a numeric or
character field. You can't use "NOT DISTINCT", as you've discovered.
If you want to only select records where there's a duplicate value in one or
more fields, you'll need to perform a self-join, and it'll help if you do
have some primary key defined on the table - otherwise, it might be
difficult to determine one identical record from another. If you're using a
natural primary key that is a composite key (more than one field), and you
wanted to look for duplicate values in one of the fields, you'd have to join
on the other fields of the primary key.
SELECT t1.field_to_match,
t1.primary_key,
t2.field_to_match,
t2.primary_key
FROM table_with_dups t1
INNER JOIN table_with_dups t2 ON t1.primary_key = t2.primary_key
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
