Filtering on results

[Kasa]

Hi,
I have a report or am running a query for patients and their blood
pressures. Some patients have more than 4 blood pressures listed(and these
are on diff. dates). I would only like to show the last four blood pressure
readings. Any suggestions?
Thanks!!!

 

[John W. Vinson]

Hi,
I have a report or am running a query for patients and their blood
pressures. Some patients have more than 4 blood pressures listed(and these
are on diff. dates). I would only like to show the last four blood pressure
readings. Any suggestions?
Thanks!!!

Probably a TOP VALUES query, but without knowing anything about the structure
of your tables it's all but impossible to say. Please post the SQL view of
your query, and if necessary some more info about your tables.

 

[Kasa]

Here is the SQL view of the query:

SELECT qry_99_New_WKST_ver2.[PCP Site], qry_99_New_WKST_ver2.[PCP Name],
qry_99_New_WKST_ver2.[Patient Name], qry_99_New_WKST_ver2.EMRN,
qry_99_New_WKST_ver2.[SHC#], qry_99_New_WKST_ver2.[Last PCP Appt Date],
qry_99_New_WKST_ver2.[MinOfAppt Date],
qry_99_New_WKST_ver2.Date_Vitals_Performed,
qry_99_New_WKST_ver2.Time_Vitals_Performed, qry_99_New_WKST_ver2.Systolic,
qry_99_New_WKST_ver2.Diastolic
FROM qry_99_New_WKST_ver2


WHERE (((qry_99_New_WKST_ver2.[PCP Site])=[Select the site: Carmel Del Mar,
Chula Vista, Downtown, El Cajon, Genesee, La Mesa, Mira Mesa, Otay Ranch,
Point Loma, Rancho Bernardo, San Carlos, Scripps Ranch]));

 

[John W. Vinson]

Here is the SQL view of the query:

SELECT qry_99_New_WKST_ver2.[PCP Site], qry_99_New_WKST_ver2.[PCP Name],
qry_99_New_WKST_ver2.[Patient Name], qry_99_New_WKST_ver2.EMRN,
qry_99_New_WKST_ver2.[SHC#], qry_99_New_WKST_ver2.[Last PCP Appt Date],
qry_99_New_WKST_ver2.[MinOfAppt Date],
qry_99_New_WKST_ver2.Date_Vitals_Performed,
qry_99_New_WKST_ver2.Time_Vitals_Performed, qry_99_New_WKST_ver2.Systolic,
qry_99_New_WKST_ver2.Diastolic
FROM qry_99_New_WKST_ver2


WHERE (((qry_99_New_WKST_ver2.[PCP Site])=[Select the site: Carmel Del Mar,
Chula Vista, Downtown, El Cajon, Genesee, La Mesa, Mira Mesa, Otay Ranch,
Point Loma, Rancho Bernardo, San Carlos, Scripps Ranch]));

Ummm... ok. What is this query producing and what do you want it to produce?
What's qry_99_New_WKST_ver2?

My GUESS is that there are underlying tables in a one to many relationship,
with the "one" side having patient information and the "many" having systolic,
diastolic and probably a few other fields. You'll probably want a Subquery. I
have no idea what the relationship is between ApptDate and
Date_Vitals_Performed; it would be easier if you stored a date and time of
Vitals Performed in one field, but they can be combined if need be.

 

[Kasa]

Example of what I am trying to do:
EMRN PT NAME DATE VITALS PERFORMED SYSTOLIC DIASTOLIC
1234 ABCDEF 1/10/09 130
76
1234 ABCDEF 1/2/09
130 70
1234 ABCDEF 12/5/08 138
80
1234 ABCDEF 11/30/08 136
84
5678 GHIJK 12/30/08
120 70
What I’d like to see: (criteria changed to last 2 blood pressures only)
EMRN PT NAME DATE VITALS PERFORMED SYSTOLIC DIASTOLIC
1234 ABCDEF 1/10/09 130
76
1234 ABCDEF 1/2/09
130 70
5678 GHIJK 12/30/08
120 70

Thanks again for looking into this.

 

[John W. Vinson]

Example of what I am trying to do:
EMRN PT NAME DATE VITALS PERFORMED SYSTOLIC DIASTOLIC
1234 ABCDEF 1/10/09 130
76
1234 ABCDEF 1/2/09
130 70
1234 ABCDEF 12/5/08 138
80
1234 ABCDEF 11/30/08 136
84
5678 GHIJK 12/30/08
120 70
What I’d like to see: (criteria changed to last 2 blood pressures only)
EMRN PT NAME DATE VITALS PERFORMED SYSTOLIC DIASTOLIC
1234 ABCDEF 1/10/09 130
76
1234 ABCDEF 1/2/09
130 70
5678 GHIJK 12/30/08
120 70

Thanks again for looking into this.

You'll need a Subquery referencing the primary key of the table containing the
blood [Date Vitals Performed] field, using a TOP VALUES query to find the four
most recent values: a criterion on the primary key of

IN (SELECT TOP 4 [primarykeyfieldname] FROM [tablename] AS Z WHERE [Z].EMRN =
qry_99_New_WKST_ver2.EMRN)