Please help. Question about querying a subform

[Gamefewel]

I asked this yesterday and see all kinds of questions around me being
answered and suppose i just did not word this correctly.

I have a table of lab results. It is related to my patient table. It is a
one-to-many relationship. I have a date field, which is the date of last
labs. I am trying to build a query that will pull only the most previous lab
from each patient, with the criteria of it having been more than 6 months
ago. I do not want to pull lab dates from 10 months ago, if there is one
within the last 6 months. This is for a Dr and it is a priority that I get
it done.

a table migh look like this

MRN LDL HDL Systolic Diastolic Weight Date
11111 2 37 95 152 301 01/31/2008
11111 3 32 87 144 275 08/29/2009
22222 7 25 99 210 195 06/03/2008
22222 9 22 75 180 190 03/02/2009

If I ran my query with the criteria of Between [12/01/2006] And [07/01/2009]
I would not want anything from the person with the Medical Record of 11111
to show up, but would want the date of 03/02/2009 to show on the person with
the Medical Record of 22222.

I appreciate any help on this.
Thanks

[Bob Barrows]

Gamefewel wrote:
> I asked this yesterday and see all kinds of questions around me being
> answered and suppose i just did not word this correctly.
>
> I have a table of lab results. It is related to my patient table.
> It is a one-to-many relationship. I have a date field, which is the
> date of last labs. I am trying to build a query that will pull only
> the most previous lab from each patient, with the criteria of it
> having been more than 6 months ago. I do not want to pull lab dates
> from 10 months ago, if there is one within the last 6 months. This
> is for a Dr and it is a priority that I get it done.
>
> a table migh look like this
>
> MRN LDL HDL Systolic Diastolic Weight Date

I hope "Date" is not really the name of this field. "Date" is a reserved
keyword and should never be used as the name of a field (or any other
user-defined object). LabDate would be a much better name. I'm assuming
you will be able to rename it. If not, just correct the query I show you
below.

> 11111 2 37 95 152 301
> 01/31/2008 11111 3 32 87 144
> 275 08/29/2009 22222 7 25 99 210
> 195 06/03/2008 22222 9 22 75 180
> 190 03/02/2009
>
> If I ran my query with the criteria of Between [12/01/2006] And
> [07/01/2009] I would not want anything from the person with the
> Medical Record of 11111 to show up, but would want the date of
> 03/02/2009 to show on the person with the Medical Record of 22222.

This query will get you a list of patients and their latest lab results:
SELECT MRN,MAX(LabDate) As MaxLabDate
FROM LabResults
GROUP BY LabResults

Try it. You can paste the statement into the SQL View of a Query Builder
window and correct the field and table names.
Add a HAVING clause to restrict it to those patients whose latest labs
were more than 6 months ago:

SELECT MRN,MAX(LabDate) As MaxLabDate
FROM LabResults
GROUP BY LabResults
HAVING MAX(LabDate) < DateAdd("m",-6,Date())

Again, try it.

Now you can use the above as a derived table in a query that joins these
results to the source table to get all the fields:

SELECT l.MRN,LDL, HDL , Systolic,Diastolic, Weight, MaxLabDate
FROM LabResults As l join
(

SELECT MRN,MAX(LabDate) As MaxLabDate
FROM LabResults
GROUP BY LabResults
HAVING MAX(LabDate) < DateAdd("m",-6,Date())
) as q ON l.MRN=q.MRN AND l.LabDate=q.MaxLabDate
WHERE LabDate BETWEEN [Enter Start Date] AND [Enter End Date]

--
HTH,
Bob Barrows

[John Spencer]

Use a correlated sub-query to identify the desired record. The SQL would look
like the following.

SELECT *
FROM SomeTable
WHERE SomeTable.[Date] =
(SELECT MAX(Temp.[Date])
FROM [SomeTable] as TEMP
WHERE Temp.MRN = [SomeTable].MRN)
AND SomeTable.[Date] Between #12/1/2006# and #7/1/2009#

You can do this in two queries if needed.
First query is a totals query that returns the max date per MRN
== Select your table as the source
== Add MRN and Date fields
== SELECT View: Totals from the menu
== Change GROUP BY to MAX under the Date field
== Save this query - you will use it in step two

Start a new query
== Add your table and the saved query as sources
== Set up relationship between the table and the query by dragging from the
MRN to the MRN and then dragging from the Date field to the MaxOfDate field
== Add the fields from the table you want to display
== Under the date field enter the date range you are interested in.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

[Gamefewel]

No date is not the field name. It is actually last lab....sorry. thanks i
will read this and try it out.

"Bob Barrows" wrote:

> .
>

[Bob Barrows]

Gamefewel wrote:
> No date is not the field name. It is actually last lab....sorry.
> thanks i will read this and try it out.
>

typo alert. This:
GROUP BY LabResults
Should be:
GROUP BY MRN

Sorry

--
HTH,
Bob Barrows