counting duplicates

[earad]

Good morning,

I have a query with about 17000 lines that shows me a date for each time a patient has seen his dr in a year. I would like to only see the patients that have seen the doctor 6 or more times. the unique data to each patient is thier medical record number. Thanks in advance.


Adam

 

[Trevor G]

Good morning,

I have a query with about 17000 lines that shows me a date for each time a patient has seen his dr in a year. I would like to only see the patients that have seen the doctor 6 or more times. the unique data to each patient is thier medical record number. Thanks in advance.


Adam

Adam welcome to the forum,

One option for you is use a grouping in a query and then set the field to count on changing the grouping to count and then add the criteria to >6

Trevor

 

[vbaInet]

Also, a DCount function is worthwhile checking out.

 

[earad]

Hi Trevor,

Thank you for the response. When I group by count it changes the column from the column of medical record numbers (ex. 102-302-369) into a 1; so when I set the criteria to >6 I get no data.

thanks,
Adam

 

[MSAccessRookie]

Hi Trevor,

Thank you for the response. When I group by count it changes the column from the column of medical record numbers (ex. 102-302-369) into a 1; so when I set the criteria to >6 I get no data.

thanks,
Adam

The GROUP BY Statement includes an Optional HAVING Statement that works with grouped objects in a similar manner to a WHERE statement. Something like this might work:

SELECT PatientID, Count(*) FROM PatientVisits
GROUP BY PatientID HAVING Count(*) > 6
​

 

[earad]

That worked; thanks