Hi there,

I have a table containing the fields 'PropertyCode' and 'KPType', amongst others. There may be multiple rows that have the same PropertyCode AND KPType. Essentially, I need to select all the rows that have more than one of the same KPType for each PropertyCode.

For example:


Row PropertyCode KPType

1 aa type1
2 bb type1
3 aa type2
4 aa type1
5 bb type2
6 bb type2


The query I need would select rows 1, 4, 5 and 6 because they have more than one row with the same PropertyCode AND KPType.

Can anyone help me?

Thanks

Matt

Two steps:
1)
Create a group by / count query to have all combinations returned that have duplicates

2)
Select the rows that have records in query1.

I hope you can take it from here, or atleast get started.

Thanks Mailman, I will see if I can cobble something together!

Sorry to ask, but any idea what the syntax for the group by query would be? Can't quite seem to find an example of quite what I'm trying to do anywhere! :(

Select PropertyCode , KPType
from yourtable
group by PropertyCode , KPType
having count(*) > 1

Wonderful, thanks very much :)

on the access toolbar there is a sigma (sum) button that will turn any select query into a totals query

just click that, and go from there


theres not just count, you have sum, max, min, and various statistical functions avaialble also