Filtering records for 2 types of dates

L

Linda RQ

Using Access 2003 and I use the query grid for my criteria etc. I have a
list of patients who were on a particular therapy and a list of all the
units they were in for their entire stay. I have filtered it down to only
the patients who where in the unit I am looking at. I need to find the
patients who started on a therapy after they arrived in a unit and who were
off the therapy before they left the unit. Many where on the therapy in
other units before getting transferred or on the therapy after they left, I
don't want them in my results. My Date units show me Date and Time in the
same field. They are named..I have been thinking maybe I need 2 more
queries, one to show the records of patients who started on therapy after
they arrived then make another query from that one for patients off the
therapy before they left but I still don't quite know how to write up that
expression.

PtLocStDtTm
PtLocEndDtTm
PtThpyStDtTm
PtThpyEndDtTm

Thanks,
Linda
 
J

John Spencer

A guess.

WHERE ptThdpyStDtTm>=PtLocStDtTm AND ptThpyEndDtTm <=ptLocEndDtTm

That assumes that the four fields are in the same record. If not, you need to
tell us where the fields are and how they tables or records are related.

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

Linda RQ

Hi John,

They are in the same record. Related by the PtID_pk in tblPatients,
PtID_fkPtThpy, PtID_fkPtLoc. I'll try this tomorrow when I get to work and
let you know.

Thanks,
Linda
 
L

Linda RQ

Linda RQ said:
Hi John,

They are in the same record. Related by the PtID_pk in tblPatients,
PtID_fkPtThpy, PtID_fkPtLoc. I'll try this tomorrow when I get to work
and let you know.

Thanks,
Linda
Click to expand...
 
L

Linda RQ

Hi John,

I entered that expression into my query in a new row in my query grid and I
got the same number of records as I did without the expression. I also
tried to just enter ptThdpyStDtTm>=PtLocStDtTm in and got the same results.
I do already have some criteria in this query too like first is to only show
records from a certain area (MICU) and to only show records after a certain
date. If I post the sql would that help? I have many tables in this query
so it will take a while for me to type up my relationships etc but I'll get
a start on it as that sometimes helps me to figure out what is going on.

Thanks,
Linda
 
J

John Spencer

If you post the SQL of query it might be possible to diagnose your problem.
As a guess I would say that the date range is not being applied to all the
records because of an or clause somewhere in the where clause.

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

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

John Spencer

Rereading you post and noted that you entered the criteria in a NEW criteria
row. That means the criteria would return all the records based on the first
criteria row PLUS any records found in the second criteria row.

You need the date criteria in the same row (or rows) as your existing criteria.

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

Linda RQ

I entered it in the field row at the end of my query grid. Do you mean put
it in the criteria row under all my date fields? Here is my sql. In the
meantime I'll keep trying different things and looking in my Access Bible.

SELECT tblPatients.PtLName, tblPtLocation.PtLocEnDtTm, tblAreaList.AreaName,
tblPatients.DCDtTm, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm,
Format([ThpyEndDtTm]-[ThpyStDtTm],"Fixed") AS VentLOS,
tblPatients.AdmitDtTm, Format([DCDtTm]-[AdmitDtTm],"Fixed") AS AdmLOS,
tblTherapyType.TherapyTypeID, tblPtLocation.PtLocStDtTm,
[ThpyStDtTm]>=[PtLocStDtTm] And [ThpyEndDtTm]<=[PtLocEnDtTm] AS Expr1
FROM (tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID=tblPtLocation.AreaID_fk) ON
tblPatients.PtID=tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID=tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID=tblPtThpy.PtID_fk
WHERE (((tblAreaList.AreaName)="MICU") AND
((tblPtThpy.ThpyStDtTm)<#10/1/2009#) AND ((tblTherapyType.TherapyTypeID)=70
Or (tblTherapyType.TherapyTypeID)=97));

My last child graduates from HS in June, I promise you all I'll take some
classes <g>

Linda
 
J

John Spencer

PERHAPS the following will work for you. I note that you placed the
expression in the SELECT clause and that will return true or false in the
column. The expression should be in the WHERE clause to limit the records
returned.

SELECT tblPatients.PtLName, tblPtLocation.PtLocEnDtTm, tblAreaList.AreaName,
tblPatients.DCDtTm, tblPatients.PtID, tblPatients.AdmitNum
, tblPtThpy.ThpyStDtTm
, tblPtThpy.ThpyEndDtTm
, Format([ThpyEndDtTm]-[ThpyStDtTm],"Fixed") AS VentLOS
, tblPatients.AdmitDtTm
, Format([DCDtTm]-[AdmitDtTm],"Fixed") AS AdmLOS
, tblTherapyType.TherapyTypeID
, tblPtLocation.PtLocStDtTm
, [ThpyStDtTm]>=[PtLocStDtTm] And [ThpyEndDtTm]<=[PtLocEnDtTm] AS Expr1

FROM (tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID=tblPtLocation.AreaID_fk) ON
tblPatients.PtID=tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID=tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID=tblPtThpy.PtID_fk

WHERE tblAreaList.AreaName="MICU" AND
tblPtThpy.ThpyStDtTm<#10/1/2009# AND
tblTherapyType.TherapyTypeIDIn (70,97) AND
ptThdpyStDtTm>=PtLocStDtTm AND
ptThpyEndDtTm <=ptLocEndDtTm


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

Linda said:
I entered it in the field row at the end of my query grid. Do you mean put
it in the criteria row under all my date fields? Here is my sql. In the
meantime I'll keep trying different things and looking in my Access Bible.

SELECT tblPatients.PtLName, tblPtLocation.PtLocEnDtTm, tblAreaList.AreaName,
tblPatients.DCDtTm, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm,
Format([ThpyEndDtTm]-[ThpyStDtTm],"Fixed") AS VentLOS,
tblPatients.AdmitDtTm, Format([DCDtTm]-[AdmitDtTm],"Fixed") AS AdmLOS,
tblTherapyType.TherapyTypeID, tblPtLocation.PtLocStDtTm,
[ThpyStDtTm]>=[PtLocStDtTm] And [ThpyEndDtTm]<=[PtLocEnDtTm] AS Expr1
FROM (tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID=tblPtLocation.AreaID_fk) ON
tblPatients.PtID=tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID=tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID=tblPtThpy.PtID_fk
WHERE (((tblAreaList.AreaName)="MICU") AND
((tblPtThpy.ThpyStDtTm)<#10/1/2009#) AND ((tblTherapyType.TherapyTypeID)=70
Or (tblTherapyType.TherapyTypeID)=97));

My last child graduates from HS in June, I promise you all I'll take some
classes <g>

Linda


John Spencer said:
Rereading you post and noted that you entered the criteria in a NEW
criteria row. That means the criteria would return all the records based
on the first criteria row PLUS any records found in the second criteria
row.

You need the date criteria in the same row (or rows) as your existing
criteria.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
Click to expand...
 
L

Linda RQ

Thanks John! That worked well. I pasted this in the sql view and then when
I looked at the query grid, it showed me how to do it and it pulled the
records great. Did you just type this out like you were writing a letter to
someone? I really appreciate the time you took to do this.

Linda

John Spencer said:
PERHAPS the following will work for you. I note that you placed the
expression in the SELECT clause and that will return true or false in the
column. The expression should be in the WHERE clause to limit the records
returned.

SELECT tblPatients.PtLName, tblPtLocation.PtLocEnDtTm,
tblAreaList.AreaName,
tblPatients.DCDtTm, tblPatients.PtID, tblPatients.AdmitNum
, tblPtThpy.ThpyStDtTm
, tblPtThpy.ThpyEndDtTm
, Format([ThpyEndDtTm]-[ThpyStDtTm],"Fixed") AS VentLOS
, tblPatients.AdmitDtTm
, Format([DCDtTm]-[AdmitDtTm],"Fixed") AS AdmLOS
, tblTherapyType.TherapyTypeID
, tblPtLocation.PtLocStDtTm
, [ThpyStDtTm]>=[PtLocStDtTm] And [ThpyEndDtTm]<=[PtLocEnDtTm] AS Expr1

FROM (tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID=tblPtLocation.AreaID_fk) ON
tblPatients.PtID=tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID=tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID=tblPtThpy.PtID_fk

WHERE tblAreaList.AreaName="MICU" AND
tblPtThpy.ThpyStDtTm<#10/1/2009# AND
tblTherapyType.TherapyTypeIDIn (70,97) AND
ptThdpyStDtTm>=PtLocStDtTm AND
ptThpyEndDtTm <=ptLocEndDtTm


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

Linda said:
I entered it in the field row at the end of my query grid. Do you mean
put it in the criteria row under all my date fields? Here is my sql. In
the meantime I'll keep trying different things and looking in my Access
Bible.

SELECT tblPatients.PtLName, tblPtLocation.PtLocEnDtTm,
tblAreaList.AreaName, tblPatients.DCDtTm, tblPatients.PtID,
tblPatients.AdmitNum, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm,
Format([ThpyEndDtTm]-[ThpyStDtTm],"Fixed") AS VentLOS,
tblPatients.AdmitDtTm, Format([DCDtTm]-[AdmitDtTm],"Fixed") AS AdmLOS,
tblTherapyType.TherapyTypeID, tblPtLocation.PtLocStDtTm,
[ThpyStDtTm]>=[PtLocStDtTm] And [ThpyEndDtTm]<=[PtLocEnDtTm] AS Expr1
FROM (tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID=tblPtLocation.AreaID_fk) ON
tblPatients.PtID=tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID=tblPtThpy.ThpyTypeID_fk)
ON tblPatients.PtID=tblPtThpy.PtID_fk
WHERE (((tblAreaList.AreaName)="MICU") AND
((tblPtThpy.ThpyStDtTm)<#10/1/2009#) AND
((tblTherapyType.TherapyTypeID)=70 Or
(tblTherapyType.TherapyTypeID)=97));

My last child graduates from HS in June, I promise you all I'll take some
classes <g>

Linda


John Spencer said:
Rereading you post and noted that you entered the criteria in a NEW
criteria row. That means the criteria would return all the records
based on the first criteria row PLUS any records found in the second
criteria row.

You need the date criteria in the same row (or rows) as your existing
criteria.

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

Linda RQ wrote:
Hi John,

I entered that expression into my query in a new row in my query grid
and I got the same number of records as I did without the expression.
I also tried to just enter ptThdpyStDtTm>=PtLocStDtTm in and got the
same results. I do already have some criteria in this query too like
first is to only show records from a certain area (MICU) and to only
show records after a certain date. If I post the sql would that help?
I have many tables in this query so it will take a while for me to type
up my relationships etc but I'll get a start on it as that sometimes
helps me to figure out what is going on.

Thanks,
Linda

A guess.

WHERE ptThdpyStDtTm>=PtLocStDtTm AND ptThpyEndDtTm <=ptLocEndDtTm

That assumes that the four fields are in the same record. If not, you
need to tell us where the fields are and how they tables or records
are related.

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

Linda RQ wrote:
Using Access 2003 and I use the query grid for my criteria etc. I
have a list of patients who were on a particular therapy and a list
of all the units they were in for their entire stay. I have filtered
it down to only the patients who where in the unit I am looking at.
I need to find the patients who started on a therapy after they
arrived in a unit and who were off the therapy before they left the
unit. Many where on the therapy in other units before getting
transferred or on the therapy after they left, I don't want them in
my results. My Date units show me Date and Time in the same field.
They are named..I have been thinking maybe I need 2 more queries, one
to show the records of patients who started on therapy after they
arrived then make another query from that one for patients off the
therapy before they left but I still don't quite know how to write up
that expression.

PtLocStDtTm
PtLocEndDtTm
PtThpyStDtTm
PtThpyEndDtTm

Thanks,
Linda
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Yes, I typed it out. And I am glad you were able to get this to work.

The query design view actually generates an SQL statement (as you noted) and
sometimes it does a good job. Sometimes it does a confusing job.

I often use the design view to do the basic setup (saves a lot of typing) and
then switch to SQL view to fine tune my query.

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

Linda said:
Thanks John! That worked well. I pasted this in the sql view and then when
I looked at the query grid, it showed me how to do it and it pulled the
records great. Did you just type this out like you were writing a letter to
someone? I really appreciate the time you took to do this.

Linda

John Spencer said:
PERHAPS the following will work for you. I note that you placed the
expression in the SELECT clause and that will return true or false in the
column. The expression should be in the WHERE clause to limit the records
returned.

SELECT tblPatients.PtLName, tblPtLocation.PtLocEnDtTm,
tblAreaList.AreaName,
tblPatients.DCDtTm, tblPatients.PtID, tblPatients.AdmitNum
, tblPtThpy.ThpyStDtTm
, tblPtThpy.ThpyEndDtTm
, Format([ThpyEndDtTm]-[ThpyStDtTm],"Fixed") AS VentLOS
, tblPatients.AdmitDtTm
, Format([DCDtTm]-[AdmitDtTm],"Fixed") AS AdmLOS
, tblTherapyType.TherapyTypeID
, tblPtLocation.PtLocStDtTm
, [ThpyStDtTm]>=[PtLocStDtTm] And [ThpyEndDtTm]<=[PtLocEnDtTm] AS Expr1

FROM (tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID=tblPtLocation.AreaID_fk) ON
tblPatients.PtID=tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID=tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID=tblPtThpy.PtID_fk

WHERE tblAreaList.AreaName="MICU" AND
tblPtThpy.ThpyStDtTm<#10/1/2009# AND
tblTherapyType.TherapyTypeIDIn (70,97) AND
ptThdpyStDtTm>=PtLocStDtTm AND
ptThpyEndDtTm <=ptLocEndDtTm


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

Linda said:
I entered it in the field row at the end of my query grid. Do you mean
put it in the criteria row under all my date fields? Here is my sql. In
the meantime I'll keep trying different things and looking in my Access
Bible.

SELECT tblPatients.PtLName, tblPtLocation.PtLocEnDtTm,
tblAreaList.AreaName, tblPatients.DCDtTm, tblPatients.PtID,
tblPatients.AdmitNum, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm,
Format([ThpyEndDtTm]-[ThpyStDtTm],"Fixed") AS VentLOS,
tblPatients.AdmitDtTm, Format([DCDtTm]-[AdmitDtTm],"Fixed") AS AdmLOS,
tblTherapyType.TherapyTypeID, tblPtLocation.PtLocStDtTm,
[ThpyStDtTm]>=[PtLocStDtTm] And [ThpyEndDtTm]<=[PtLocEnDtTm] AS Expr1
FROM (tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID=tblPtLocation.AreaID_fk) ON
tblPatients.PtID=tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID=tblPtThpy.ThpyTypeID_fk)
ON tblPatients.PtID=tblPtThpy.PtID_fk
WHERE (((tblAreaList.AreaName)="MICU") AND
((tblPtThpy.ThpyStDtTm)<#10/1/2009#) AND
((tblTherapyType.TherapyTypeID)=70 Or
(tblTherapyType.TherapyTypeID)=97));

My last child graduates from HS in June, I promise you all I'll take some
classes <g>

Linda


Rereading you post and noted that you entered the criteria in a NEW
criteria row. That means the criteria would return all the records
based on the first criteria row PLUS any records found in the second
criteria row.

You need the date criteria in the same row (or rows) as your existing
criteria.

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

Linda RQ wrote:
Hi John,

I entered that expression into my query in a new row in my query grid
and I got the same number of records as I did without the expression.
I also tried to just enter ptThdpyStDtTm>=PtLocStDtTm in and got the
same results. I do already have some criteria in this query too like
first is to only show records from a certain area (MICU) and to only
show records after a certain date. If I post the sql would that help?
I have many tables in this query so it will take a while for me to type
up my relationships etc but I'll get a start on it as that sometimes
helps me to figure out what is going on.

Thanks,
Linda

A guess.

WHERE ptThdpyStDtTm>=PtLocStDtTm AND ptThpyEndDtTm <=ptLocEndDtTm

That assumes that the four fields are in the same record. If not, you
need to tell us where the fields are and how they tables or records
are related.

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

Linda RQ wrote:
Using Access 2003 and I use the query grid for my criteria etc. I
have a list of patients who were on a particular therapy and a list
of all the units they were in for their entire stay. I have filtered
it down to only the patients who where in the unit I am looking at.
I need to find the patients who started on a therapy after they
arrived in a unit and who were off the therapy before they left the
unit. Many where on the therapy in other units before getting
transferred or on the therapy after they left, I don't want them in
my results. My Date units show me Date and Time in the same field.
They are named..I have been thinking maybe I need 2 more queries, one
to show the records of patients who started on therapy after they
arrived then make another query from that one for patients off the
therapy before they left but I still don't quite know how to write up
that expression.

PtLocStDtTm
PtLocEndDtTm
PtThpyStDtTm
PtThpyEndDtTm

Thanks,
Linda
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter in between dates 6
Add a new field that enters Y or N 1
DateAdd Function 9
Crosstab showing too many rows 4
Parameter query with expressions and calculations 3
Lock only updated record 5
No records with date criteria 7
Between Dates Parameters missing a day 5

Top