473,324 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

using count to filter data

15
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$.DATETIME
WHERE (Sheet1$.[Operator Index] >= 90)
GROUP BY Sheet1$.[Patient ID]
HAVING (COUNT(*) <= 4)
Jan 29 '07 #1
4 2673
iburyak
1,017 Expert 512MB
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$.DATETIME
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.
Jan 29 '07 #2
AtCor
15
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.
Jan 29 '07 #3
iburyak
1,017 Expert 512MB
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.
Jan 29 '07 #4
AtCor
15
Yes, that is true
Jan 29 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Random Person | last post by:
Does anyone know how to use VBA to relink tables between two MS Access databases? We have two databases, one with VBA code and the other with data tables. The tables are referenced by linked...
1
by: Simon Matthews | last post by:
Hope someone can help an Access beginner! I've just started keeping my surgical logbook on access and it's a simple flat-file affair. I have created several queries that will list cases...
11
by: Grasshopper | last post by:
Hi, I am automating Access reports to PDF using PDF Writer 6.0. I've created a DTS package to run the reports and schedule a job to run this DTS package. If I PC Anywhere into the server on...
1
by: Daveyk0 | last post by:
Hello there, I have a front end database that I have recently made very many changes to to allow off-line use. I keep copies of the databases on my hard drive and link to them rather than the...
1
by: Prasad Karunakaran | last post by:
I am using the C# DirectoryEntry class to retrieve the Properties of an user object in the Active Directory. I need to get the First Name and Last Name as properties. I know it is not supported...
2
by: Alpha | last post by:
I have a window application. In one of the form, a datagrid has a dataview as its datasource. Initial filtering result would give the datavew 3 items. When I double click on the datagrid to edit...
0
by: anonieko | last post by:
This approach I found very efficient and FAST when compared to the rowcount, or Subquery Approaches. This is before the advent of a ranking function from DB such as ROW_NUMBER() in SQL Server...
2
by: Tugrul HELVACI | last post by:
I'm using Delphi 2006 and I have a class defination like this: TPerson = class fPersonName : String; fPersonSurName : String; fPersonAge : Integer; published property PersonName : String...
2
by: AtCor | last post by:
SELECT Sheet1$. AS , CASE Sheet1$. WHEN 'CSPP100A2344' THEN '0' END AS Extension, SUBSTRING(Sheet1$., 1, 4) AS , SUBSTRING(Sheet1$., 5, 8) AS , SUBSTRING(Sheet1$., 1, 4) ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.