using count to filter data

[AtCor]

I am trying to filter data using count. For a given day and patient, I would like to return all the valid tests. When I use a count this way, it only returns the patients with at least 4 tests. I want it to display those patients that have 1, 2, 3, or 4 valid tests, but not if 5, 6 or .... we taken on a given day. I only need the first four valid tests. Any suggesions?

SELECT Sheet1$.[Patient ID]
FROM M_PWA INNER JOIN
Sheet1$ ON M_PWA.DATETIME = Sheet1$.DATETIM E
WHERE (Sheet1$.[Operator Index] >= 90)
GROUP BY Sheet1$.[Patient ID]
HAVING (COUNT(*) <= 4)

[iburyak]

I am trying to filter data using count. For a given day and patient, I would like to return all the valid tests. When I use a count this way, it only returns the patients with at least 4 tests. I want it to display those patients that have 1, 2, 3, or 4 valid tests, but not if 5, 6 or .... we taken on a given day. I only need the first four valid tests. Any suggesions?

SELECT Sheet1$.[Patient ID]
FROM M_PWA INNER JOIN
Sheet1$ ON M_PWA.DATETIME = Sheet1$.DATETIM E
WHERE (Sheet1$.[Operator Index] >= 90)
GROUP BY Sheet1$.[Patient ID]
HAVING (COUNT(*) <= 4)

Count looks good to me. Check your JOIN and WHERE condition.
Try to return * with no counts or Group by close
and order by Sheet1$.[Patient ID]. Just to make sure you have correct understanding of data that is counted.

[AtCor]

The patient test must be placed according to patient, and sorted by date (and time performed). My ultimate goal is to accept the first two tests above 90 or the first 4 tests above 80. If there are two 80's and then 2 90's, I want the 2 90's. Am I best off putting a row number in from of the 90's, a row number in front of the 80's and then accepting 2 90's or 4 80's? It is not working with a count function. I have a running row number, but that is cumulative, so that will not work either.

[iburyak]

The patient test must be placed according to patient, and sorted by date (and time performed). My ultimate goal is to accept the first two tests above 90 or the first 4 tests above 80. If there are two 80's and then 2 90's, I want the 2 90's. Am I best off putting a row number in from of the 90's, a row number in front of the 80's and then accepting 2 90's or 4 80's? It is not working with a count function. I have a running row number, but that is cumulative, so that will not work either.

Are you telling me that it could be count(*) = 6 but you accept only 4 first tests right? In this case your query is wrong.

[AtCor]

Yes, that is true