Hi there,
I'm in a fix and need some help here. I have a database in Access that has 10 or so fields. I need to find all the records in this database where two or more records have duplicate values in three fields.

heres a sample table:

1, 2, 3, 4, 5
1, 5, 3, 6, 5
2, 3, 4, 5, 6

So in the above example I want to evaulate the 1st, 3rd, and 5th fields to see if they match and return them. The output from the above table should be:

1,2,3,4,5
1,5,3,6,5

Because the three fields have the same values in them.

Is this possible using SQL in Access? HELP!!! Thanks for any help you can offer.

Are you saying any 3 columns, or just some particular 3 columns like col1, col3, col5 as in your example?

If the latter, here is one way:

select *
from t
where (col1, col3, col5) in
( select col1, col3, col5
from t
group by col1, col3, col5
having count(*) > 1
);

Yah, its specifically those three fields. So anytime there is a record that has values in the three fields that match another record (the other record has the same values in the three fields) then it should return those two recrods. Basically my database should never have two or more records with matching values in these three fields so I need a way to see if there are.

When I try your idea I get "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.

Thank you so much for your reply, I hope there is a way to do this. Thanks.

OK, let's try again (apparently IN clause doesn't work with Access?):

select *
from t
where exists
( select count(*)
from t t2
where t2.col1 = t.col1
and t2.col2 = t.col2
and t2.col3 = t.col3
having count(*) > 1
);

or

select *
from t
where 1 <
( select count(*)
from t t2
where t2.col1 = t.col1
and t2.col2 = t.col2
and t2.col3 = t.col3
);

I think this actually worked the way I wanted! I cant believe it. I was typing in the second one (cuz I had killed the first one thinking it was stuck in an ednless loop) and I was thinking to mysel, I dont understand how this will work, but it does. THANKS VERY MUCH, you made my day.

I dont know if the first one works or not, it looked like Access had stopped responding so I killed it and typed in the second one. The second one looked like it had locked up Access as well so I took a lunch break. an hour later I arrive to find the exact results I was looking for sitting right here on my screen.

Thanks again,
James