DateAdd Function

L

Linda RQ

Hi Everyone,

Using Access 2003. I have my query working....almost. I need to see
patients who have been on therapy for more than 10 days. I have a field
ThpyStDtTm and in my query grid, my Criteria is <=DateAdd("d",-10,Date()).
This shows me all the records of therapy that is >10 days old. The problem
is it is not showing me the records for that patient that are less than 10
days old and I need to see these records too but only for the patients on
therapy more than 10 days.

I am thinking I need to take the criteria off of my ThpyStDtTm field and add
a new field to my query grid with an expression that includes the
<=DateAdd("d",-10,Date()) with something else to say show all the records
for this patient but I don't know how to say this in query language.

Hope it can be done....I am so close!

Thanks, Linda

My sql is below so you can see all my tables and joins but I use the query
grid to get this.

SELECT tblAreaList.AreaSortOrder, tblAreaList.AreaName, [PtLocRmNum] & "
"& [BedNumber] AS RmAndBd, [PtLocRmNum] & " " & [BedNumber] & " " &
[PtLName] & ", " & [PtFName] & " ( " & [AdmitNum] & ")" AS
PtNameRmAdmitNum, tblPatients.AdmitNum, tblTherapyType.TherapyDesc,
tblFrequency.Freq, tblPatients.PtLName, tblPatients.PtFName,
tblPatients.PtActive, tblPatients.Isolation, tblPtThpy.ProtocolEvalDate,
tblPatients.PtMemo, tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm,
tblPtLocation.PtLocRmNum, tblBedsList.BedNumber, tblPtThpy.ThpyStDtTm,
tblPtThpy.ThpyEndDtTm, tblPtThpy.ThpyCurrOrdNum, tblPtThpy.ThpyRenDate,
tblPtThpy.ThpyOrigOrdNum, tblPtThpy.ThpyHomeMeds, tblEquipType.TagNum,
[Model] & " " & [TagNum] AS VentAndTag, tblEquipType.Model,
tblPatients.Diagnosis, tblMedList.Med, tblPhysicians.PhysLName,
tblPhysicians.PhysFName, [PhysLName] & ", " & [PhysFName] AS Physician,
tblTherapyType.ThpySortOrder, tblPatients.TxPrtclCan, tblPatients.AppThpy,
tblPatients.AuditorMemo, DateDiff("d",[ThpyStDtTm],Date()) AS DaysInTherapy
FROM (tblPatients LEFT JOIN (tblAreaList RIGHT JOIN (tblBedsList RIGHT
JOINtblPtLocation ON tblBedsList.BedID = tblPtLocation.BedID_fk) ON
tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk) LEFT JOIN (tblTherapyType RIGHT JOIN (tblEquipType
RIGHT JOIN (tblFrequency RIGHT JOIN (tblPhysicians RIGHT JOIN (tblPtThpy
LEFT JOIN tblMedList ON tblPtThpy.ThpyMeds_fk = tblMedList.MedID) ON
tblPhysicians.PhysID = tblPtThpy.PhysID_fk) ON tblFrequency.FreqID =
tblPtThpy.FreqID_fk) ON tblEquipType.EquipID = tblPtThpy.EquipID_fk) ON
tblTherapyType.TherapyTypeID = tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID= tblPtThpy.PtID_fkWHERE (((tblPatients.PtActive)=True) AND
((tblPtLocation.PtLocEnDtTm) Is
Null) AND ((tblPtThpy.ThpyStDtTm)<=DateAdd("d",-10,Date())))
ORDER BY tblAreaList.AreaSortOrder, tblTherapyType.ThpySortOrder;
 
J

Jerry Whittle

I'm confuse. You say that it returns the all records of therapy that is >10
days old, but doesn't return records for patient that are less than 10 days
old and I need to see these records too but only for the patients on therapy
more than 10 days.

Are you talking about something like a patient who may have multiple therapy
sessions and their cumulative therapy is over 10 days even if their current
session is less than 10 days?

Or is it something like they can have multiple types of therapy during a
time period and the concurrent total can be more than 10 days even if the
current visit is less than 10 days?

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Linda RQ said:
Hi Everyone,

Using Access 2003. I have my query working....almost. I need to see
patients who have been on therapy for more than 10 days. I have a field
ThpyStDtTm and in my query grid, my Criteria is <=DateAdd("d",-10,Date()).
This shows me all the records of therapy that is >10 days old. The problem
is it is not showing me the records for that patient that are less than 10
days old and I need to see these records too but only for the patients on
therapy more than 10 days.

I am thinking I need to take the criteria off of my ThpyStDtTm field and add
a new field to my query grid with an expression that includes the
<=DateAdd("d",-10,Date()) with something else to say show all the records
for this patient but I don't know how to say this in query language.

Hope it can be done....I am so close!

Thanks, Linda

My sql is below so you can see all my tables and joins but I use the query
grid to get this.

SELECT tblAreaList.AreaSortOrder, tblAreaList.AreaName, [PtLocRmNum] & "
"& [BedNumber] AS RmAndBd, [PtLocRmNum] & " " & [BedNumber] & " " &
[PtLName] & ", " & [PtFName] & " ( " & [AdmitNum] & ")" AS
PtNameRmAdmitNum, tblPatients.AdmitNum, tblTherapyType.TherapyDesc,
tblFrequency.Freq, tblPatients.PtLName, tblPatients.PtFName,
tblPatients.PtActive, tblPatients.Isolation, tblPtThpy.ProtocolEvalDate,
tblPatients.PtMemo, tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm,
tblPtLocation.PtLocRmNum, tblBedsList.BedNumber, tblPtThpy.ThpyStDtTm,
tblPtThpy.ThpyEndDtTm, tblPtThpy.ThpyCurrOrdNum, tblPtThpy.ThpyRenDate,
tblPtThpy.ThpyOrigOrdNum, tblPtThpy.ThpyHomeMeds, tblEquipType.TagNum,
[Model] & " " & [TagNum] AS VentAndTag, tblEquipType.Model,
tblPatients.Diagnosis, tblMedList.Med, tblPhysicians.PhysLName,
tblPhysicians.PhysFName, [PhysLName] & ", " & [PhysFName] AS Physician,
tblTherapyType.ThpySortOrder, tblPatients.TxPrtclCan, tblPatients.AppThpy,
tblPatients.AuditorMemo, DateDiff("d",[ThpyStDtTm],Date()) AS DaysInTherapy
FROM (tblPatients LEFT JOIN (tblAreaList RIGHT JOIN (tblBedsList RIGHT
JOINtblPtLocation ON tblBedsList.BedID = tblPtLocation.BedID_fk) ON
tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk) LEFT JOIN (tblTherapyType RIGHT JOIN (tblEquipType
RIGHT JOIN (tblFrequency RIGHT JOIN (tblPhysicians RIGHT JOIN (tblPtThpy
LEFT JOIN tblMedList ON tblPtThpy.ThpyMeds_fk = tblMedList.MedID) ON
tblPhysicians.PhysID = tblPtThpy.PhysID_fk) ON tblFrequency.FreqID =
tblPtThpy.FreqID_fk) ON tblEquipType.EquipID = tblPtThpy.EquipID_fk) ON
tblTherapyType.TherapyTypeID = tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID= tblPtThpy.PtID_fkWHERE (((tblPatients.PtActive)=True) AND
((tblPtLocation.PtLocEnDtTm) Is
Null) AND ((tblPtThpy.ThpyStDtTm)<=DateAdd("d",-10,Date())))
ORDER BY tblAreaList.AreaSortOrder, tblTherapyType.ThpySortOrder;


.
Click to expand...
 
J

Jeff Boyce

Linda

I'm not sure I quite get what you are looking for...

If a patient's record is "less than 10 days old", how could they (also) be
"on therapy more than 10 days"?

If you'll describe your data structure a bit more, folks here may be able to
help ... (or maybe I'm just under-caffeinated this morning!)

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Linda RQ said:
Hi Everyone,

Using Access 2003. I have my query working....almost. I need to see
patients who have been on therapy for more than 10 days. I have a field
ThpyStDtTm and in my query grid, my Criteria is <=DateAdd("d",-10,Date()).
This shows me all the records of therapy that is >10 days old. The
problem is it is not showing me the records for that patient that are less
than 10 days old and I need to see these records too but only for the
patients on therapy more than 10 days.

I am thinking I need to take the criteria off of my ThpyStDtTm field and
add a new field to my query grid with an expression that includes the
<=DateAdd("d",-10,Date()) with something else to say show all the records
for this patient but I don't know how to say this in query language.

Hope it can be done....I am so close!

Thanks, Linda

My sql is below so you can see all my tables and joins but I use the query
grid to get this.

SELECT tblAreaList.AreaSortOrder, tblAreaList.AreaName, [PtLocRmNum] & "
"& [BedNumber] AS RmAndBd, [PtLocRmNum] & " " & [BedNumber] & " " &
[PtLName] & ", " & [PtFName] & " ( " & [AdmitNum] & ")" AS
PtNameRmAdmitNum, tblPatients.AdmitNum, tblTherapyType.TherapyDesc,
tblFrequency.Freq, tblPatients.PtLName, tblPatients.PtFName,
tblPatients.PtActive, tblPatients.Isolation, tblPtThpy.ProtocolEvalDate,
tblPatients.PtMemo, tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm,
tblPtLocation.PtLocRmNum, tblBedsList.BedNumber, tblPtThpy.ThpyStDtTm,
tblPtThpy.ThpyEndDtTm, tblPtThpy.ThpyCurrOrdNum, tblPtThpy.ThpyRenDate,
tblPtThpy.ThpyOrigOrdNum, tblPtThpy.ThpyHomeMeds, tblEquipType.TagNum,
[Model] & " " & [TagNum] AS VentAndTag, tblEquipType.Model,
tblPatients.Diagnosis, tblMedList.Med, tblPhysicians.PhysLName,
tblPhysicians.PhysFName, [PhysLName] & ", " & [PhysFName] AS Physician,
tblTherapyType.ThpySortOrder, tblPatients.TxPrtclCan, tblPatients.AppThpy,
tblPatients.AuditorMemo, DateDiff("d",[ThpyStDtTm],Date()) AS
DaysInTherapy
FROM (tblPatients LEFT JOIN (tblAreaList RIGHT JOIN (tblBedsList RIGHT
JOINtblPtLocation ON tblBedsList.BedID = tblPtLocation.BedID_fk) ON
tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk) LEFT JOIN (tblTherapyType RIGHT JOIN (tblEquipType
RIGHT JOIN (tblFrequency RIGHT JOIN (tblPhysicians RIGHT JOIN (tblPtThpy
LEFT JOIN tblMedList ON tblPtThpy.ThpyMeds_fk = tblMedList.MedID) ON
tblPhysicians.PhysID = tblPtThpy.PhysID_fk) ON tblFrequency.FreqID =
tblPtThpy.FreqID_fk) ON tblEquipType.EquipID = tblPtThpy.EquipID_fk) ON
tblTherapyType.TherapyTypeID = tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID= tblPtThpy.PtID_fkWHERE (((tblPatients.PtActive)=True)
AND ((tblPtLocation.PtLocEnDtTm) Is
Null) AND ((tblPtThpy.ThpyStDtTm)<=DateAdd("d",-10,Date())))
ORDER BY tblAreaList.AreaSortOrder, tblTherapyType.ThpySortOrder;
Click to expand...
 
J

John Spencer

Which field uniquely identifies the patient?

You can use your existing query to identify the patients and then pull all
records for that patient. For instance if AdmitNum uniquely identifies the
patient you want to see then your query could become something like the
following (Watch out for line wraps causing errors in the SQL).

SELECT tblAreaList.AreaSortOrder, tblAreaList.AreaName
, [PtLocRmNum] & "" & [BedNumber] AS RmAndBd
, [PtLocRmNum] & " " & [BedNumber] & " " &
[PtLName] & ", " & [PtFName] & " ( " & [AdmitNum] & ")" AS
PtNameRmAdmitNum
, tblPatients.AdmitNum
, tblTherapyType.TherapyDesc,
tblFrequency.Freq, tblPatients.PtLName, tblPatients.PtFName,
tblPatients.PtActive, tblPatients.Isolation, tblPtThpy.ProtocolEvalDate,
tblPatients.PtMemo, tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm,
tblPtLocation.PtLocRmNum, tblBedsList.BedNumber, tblPtThpy.ThpyStDtTm,
tblPtThpy.ThpyEndDtTm, tblPtThpy.ThpyCurrOrdNum, tblPtThpy.ThpyRenDate,
tblPtThpy.ThpyOrigOrdNum, tblPtThpy.ThpyHomeMeds, tblEquipType.TagNum,
[Model] & " " & [TagNum] AS VentAndTag, tblEquipType.Model,
tblPatients.Diagnosis, tblMedList.Med, tblPhysicians.PhysLName,
tblPhysicians.PhysFName, [PhysLName] & ", " & [PhysFName] AS Physician,
tblTherapyType.ThpySortOrder, tblPatients.TxPrtclCan, tblPatients.AppThpy,
tblPatients.AuditorMemo, DateDiff("d",[ThpyStDtTm],Date()) AS DaysInTherapy
FROM (tblPatients LEFT JOIN (tblAreaList RIGHT JOIN (tblBedsList RIGHT
JOINtblPtLocation ON tblBedsList.BedID = tblPtLocation.BedID_fk) ON
tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk) LEFT JOIN (tblTherapyType RIGHT JOIN (tblEquipType
RIGHT JOIN (tblFrequency RIGHT JOIN (tblPhysicians RIGHT JOIN (tblPtThpy
LEFT JOIN tblMedList ON tblPtThpy.ThpyMeds_fk = tblMedList.MedID) ON
tblPhysicians.PhysID = tblPtThpy.PhysID_fk) ON tblFrequency.FreqID =
tblPtThpy.FreqID_fk) ON tblEquipType.EquipID = tblPtThpy.EquipID_fk) ON
tblTherapyType.TherapyTypeID = tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID= tblPtThpy.PtID_fk

WHERE tblPatients.AdmitNum IN (

SELECT tblPatients.AdmitNum
FROM (tblPatients LEFT JOIN (tblAreaList RIGHT JOIN (tblBedsList RIGHT
JOINtblPtLocation ON tblBedsList.BedID = tblPtLocation.BedID_fk) ON
tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk) LEFT JOIN (tblTherapyType RIGHT JOIN (tblEquipType
RIGHT JOIN (tblFrequency RIGHT JOIN (tblPhysicians RIGHT JOIN (tblPtThpy
LEFT JOIN tblMedList ON tblPtThpy.ThpyMeds_fk = tblMedList.MedID) ON
tblPhysicians.PhysID = tblPtThpy.PhysID_fk) ON tblFrequency.FreqID =
tblPtThpy.FreqID_fk) ON tblEquipType.EquipID = tblPtThpy.EquipID_fk) ON
tblTherapyType.TherapyTypeID = tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID= tblPtThpy.PtID_fk
WHERE tblPatients.PtActive=True AND
tblPtLocation.PtLocEnDtTm Is Null AND
tblPtThpy.ThpyStDtTm<=DateAdd("d",-10,Date())
)

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

Linda said:
Hi Everyone,

Using Access 2003. I have my query working....almost. I need to see
patients who have been on therapy for more than 10 days. I have a field
ThpyStDtTm and in my query grid, my Criteria is <=DateAdd("d",-10,Date()).
This shows me all the records of therapy that is >10 days old. The problem
is it is not showing me the records for that patient that are less than 10
days old and I need to see these records too but only for the patients on
therapy more than 10 days.

I am thinking I need to take the criteria off of my ThpyStDtTm field and add
a new field to my query grid with an expression that includes the
<=DateAdd("d",-10,Date()) with something else to say show all the records
for this patient but I don't know how to say this in query language.

Hope it can be done....I am so close!

Thanks, Linda

My sql is below so you can see all my tables and joins but I use the query
grid to get this.

SELECT tblAreaList.AreaSortOrder, tblAreaList.AreaName, [PtLocRmNum] & "
"& [BedNumber] AS RmAndBd, [PtLocRmNum] & " " & [BedNumber] & " " &
[PtLName] & ", " & [PtFName] & " ( " & [AdmitNum] & ")" AS
PtNameRmAdmitNum, tblPatients.AdmitNum, tblTherapyType.TherapyDesc,
tblFrequency.Freq, tblPatients.PtLName, tblPatients.PtFName,
tblPatients.PtActive, tblPatients.Isolation, tblPtThpy.ProtocolEvalDate,
tblPatients.PtMemo, tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm,
tblPtLocation.PtLocRmNum, tblBedsList.BedNumber, tblPtThpy.ThpyStDtTm,
tblPtThpy.ThpyEndDtTm, tblPtThpy.ThpyCurrOrdNum, tblPtThpy.ThpyRenDate,
tblPtThpy.ThpyOrigOrdNum, tblPtThpy.ThpyHomeMeds, tblEquipType.TagNum,
[Model] & " " & [TagNum] AS VentAndTag, tblEquipType.Model,
tblPatients.Diagnosis, tblMedList.Med, tblPhysicians.PhysLName,
tblPhysicians.PhysFName, [PhysLName] & ", " & [PhysFName] AS Physician,
tblTherapyType.ThpySortOrder, tblPatients.TxPrtclCan, tblPatients.AppThpy,
tblPatients.AuditorMemo, DateDiff("d",[ThpyStDtTm],Date()) AS DaysInTherapy
FROM (tblPatients LEFT JOIN (tblAreaList RIGHT JOIN (tblBedsList RIGHT
JOINtblPtLocation ON tblBedsList.BedID = tblPtLocation.BedID_fk) ON
tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk) LEFT JOIN (tblTherapyType RIGHT JOIN (tblEquipType
RIGHT JOIN (tblFrequency RIGHT JOIN (tblPhysicians RIGHT JOIN (tblPtThpy
LEFT JOIN tblMedList ON tblPtThpy.ThpyMeds_fk = tblMedList.MedID) ON
tblPhysicians.PhysID = tblPtThpy.PhysID_fk) ON tblFrequency.FreqID =
tblPtThpy.FreqID_fk) ON tblEquipType.EquipID = tblPtThpy.EquipID_fk) ON
tblTherapyType.TherapyTypeID = tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID= tblPtThpy.PtID_fkWHERE (((tblPatients.PtActive)=True) AND
((tblPtLocation.PtLocEnDtTm) Is
Null) AND ((tblPtThpy.ThpyStDtTm)<=DateAdd("d",-10,Date())))
ORDER BY tblAreaList.AreaSortOrder, tblTherapyType.ThpySortOrder;
Click to expand...
 
L

Linda RQ

Hi Jeff,

I don't drink caffein and I am not a programmer so either may be the
problem. Now that I have read some feedback, I think I can explain a little
better. My unique identifier is PtID. Patients can have many therapies
that start and stop throughout their admission. Some have been started and
stopped so they have both a start date and time and end date and time. Some
therapies have not been ended yet but could be less than 10 days old.

I have a patient who started on a therapy 12 days ago but that therapy ended
and a new therapy started 4 days ago. The therapy that started 12 days ago
shows up but not the therapy that started 4 days ago. I need all the
therapy that the patient was on to show up as long as one of their therapies
was older than 10 days old. In the mean time I'll try John Spencer's code
to see what that does.

Thanks,
Linda


Jeff Boyce said:
Linda

I'm not sure I quite get what you are looking for...

If a patient's record is "less than 10 days old", how could they (also) be
"on therapy more than 10 days"?

If you'll describe your data structure a bit more, folks here may be able
to help ... (or maybe I'm just under-caffeinated this morning!)

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned
in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Linda RQ said:
Hi Everyone,

Using Access 2003. I have my query working....almost. I need to see
patients who have been on therapy for more than 10 days. I have a field
ThpyStDtTm and in my query grid, my Criteria is
<=DateAdd("d",-10,Date()). This shows me all the records of therapy that
is >10 days old. The problem is it is not showing me the records for
that patient that are less than 10 days old and I need to see these
records too but only for the patients on therapy more than 10 days.

I am thinking I need to take the criteria off of my ThpyStDtTm field and
add a new field to my query grid with an expression that includes the
<=DateAdd("d",-10,Date()) with something else to say show all the records
for this patient but I don't know how to say this in query language.

Hope it can be done....I am so close!

Thanks, Linda

My sql is below so you can see all my tables and joins but I use the
query grid to get this.

SELECT tblAreaList.AreaSortOrder, tblAreaList.AreaName, [PtLocRmNum] & "
"& [BedNumber] AS RmAndBd, [PtLocRmNum] & " " & [BedNumber] & " " &
[PtLName] & ", " & [PtFName] & " ( " & [AdmitNum] & ")" AS
PtNameRmAdmitNum, tblPatients.AdmitNum, tblTherapyType.TherapyDesc,
tblFrequency.Freq, tblPatients.PtLName, tblPatients.PtFName,
tblPatients.PtActive, tblPatients.Isolation, tblPtThpy.ProtocolEvalDate,
tblPatients.PtMemo, tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm,
tblPtLocation.PtLocRmNum, tblBedsList.BedNumber, tblPtThpy.ThpyStDtTm,
tblPtThpy.ThpyEndDtTm, tblPtThpy.ThpyCurrOrdNum, tblPtThpy.ThpyRenDate,
tblPtThpy.ThpyOrigOrdNum, tblPtThpy.ThpyHomeMeds, tblEquipType.TagNum,
[Model] & " " & [TagNum] AS VentAndTag, tblEquipType.Model,
tblPatients.Diagnosis, tblMedList.Med, tblPhysicians.PhysLName,
tblPhysicians.PhysFName, [PhysLName] & ", " & [PhysFName] AS Physician,
tblTherapyType.ThpySortOrder, tblPatients.TxPrtclCan,
tblPatients.AppThpy,
tblPatients.AuditorMemo, DateDiff("d",[ThpyStDtTm],Date()) AS
DaysInTherapy
FROM (tblPatients LEFT JOIN (tblAreaList RIGHT JOIN (tblBedsList RIGHT
JOINtblPtLocation ON tblBedsList.BedID = tblPtLocation.BedID_fk) ON
tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk) LEFT JOIN (tblTherapyType RIGHT JOIN (tblEquipType
RIGHT JOIN (tblFrequency RIGHT JOIN (tblPhysicians RIGHT JOIN (tblPtThpy
LEFT JOIN tblMedList ON tblPtThpy.ThpyMeds_fk = tblMedList.MedID) ON
tblPhysicians.PhysID = tblPtThpy.PhysID_fk) ON tblFrequency.FreqID =
tblPtThpy.FreqID_fk) ON tblEquipType.EquipID = tblPtThpy.EquipID_fk) ON
tblTherapyType.TherapyTypeID = tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID= tblPtThpy.PtID_fkWHERE (((tblPatients.PtActive)=True)
AND ((tblPtLocation.PtLocEnDtTm) Is
Null) AND ((tblPtThpy.ThpyStDtTm)<=DateAdd("d",-10,Date())))
ORDER BY tblAreaList.AreaSortOrder, tblTherapyType.ThpySortOrder;
Click to expand...
Click to expand...
 
L

Linda RQ

Hi Jerry,

Both of your explainations look right. See my response to Jeff's post. I
think I did better there....

Thanks for helping. I need it!

Linda

Jerry Whittle said:
I'm confuse. You say that it returns the all records of therapy that is
10
Click to expand...
days old, but doesn't return records for patient that are less than 10
days
old and I need to see these records too but only for the patients on
therapy
more than 10 days.

Are you talking about something like a patient who may have multiple
therapy
sessions and their cumulative therapy is over 10 days even if their
current
session is less than 10 days?

Or is it something like they can have multiple types of therapy during a
time period and the concurrent total can be more than 10 days even if the
current visit is less than 10 days?

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Linda RQ said:
Hi Everyone,

Using Access 2003. I have my query working....almost. I need to see
patients who have been on therapy for more than 10 days. I have a field
ThpyStDtTm and in my query grid, my Criteria is
<=DateAdd("d",-10,Date()).
This shows me all the records of therapy that is >10 days old. The
problem
is it is not showing me the records for that patient that are less than
10
days old and I need to see these records too but only for the patients on
therapy more than 10 days.

I am thinking I need to take the criteria off of my ThpyStDtTm field and
add
a new field to my query grid with an expression that includes the
<=DateAdd("d",-10,Date()) with something else to say show all the records
for this patient but I don't know how to say this in query language.

Hope it can be done....I am so close!

Thanks, Linda

My sql is below so you can see all my tables and joins but I use the
query
grid to get this.

SELECT tblAreaList.AreaSortOrder, tblAreaList.AreaName, [PtLocRmNum] & "
"& [BedNumber] AS RmAndBd, [PtLocRmNum] & " " & [BedNumber] & " " &
[PtLName] & ", " & [PtFName] & " ( " & [AdmitNum] & ")" AS
PtNameRmAdmitNum, tblPatients.AdmitNum, tblTherapyType.TherapyDesc,
tblFrequency.Freq, tblPatients.PtLName, tblPatients.PtFName,
tblPatients.PtActive, tblPatients.Isolation, tblPtThpy.ProtocolEvalDate,
tblPatients.PtMemo, tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm,
tblPtLocation.PtLocRmNum, tblBedsList.BedNumber, tblPtThpy.ThpyStDtTm,
tblPtThpy.ThpyEndDtTm, tblPtThpy.ThpyCurrOrdNum, tblPtThpy.ThpyRenDate,
tblPtThpy.ThpyOrigOrdNum, tblPtThpy.ThpyHomeMeds, tblEquipType.TagNum,
[Model] & " " & [TagNum] AS VentAndTag, tblEquipType.Model,
tblPatients.Diagnosis, tblMedList.Med, tblPhysicians.PhysLName,
tblPhysicians.PhysFName, [PhysLName] & ", " & [PhysFName] AS Physician,
tblTherapyType.ThpySortOrder, tblPatients.TxPrtclCan,
tblPatients.AppThpy,
tblPatients.AuditorMemo, DateDiff("d",[ThpyStDtTm],Date()) AS
DaysInTherapy
FROM (tblPatients LEFT JOIN (tblAreaList RIGHT JOIN (tblBedsList RIGHT
JOINtblPtLocation ON tblBedsList.BedID = tblPtLocation.BedID_fk) ON
tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk) LEFT JOIN (tblTherapyType RIGHT JOIN (tblEquipType
RIGHT JOIN (tblFrequency RIGHT JOIN (tblPhysicians RIGHT JOIN (tblPtThpy
LEFT JOIN tblMedList ON tblPtThpy.ThpyMeds_fk = tblMedList.MedID) ON
tblPhysicians.PhysID = tblPtThpy.PhysID_fk) ON tblFrequency.FreqID =
tblPtThpy.FreqID_fk) ON tblEquipType.EquipID = tblPtThpy.EquipID_fk) ON
tblTherapyType.TherapyTypeID = tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID= tblPtThpy.PtID_fkWHERE (((tblPatients.PtActive)=True)
AND
((tblPtLocation.PtLocEnDtTm) Is
Null) AND ((tblPtThpy.ThpyStDtTm)<=DateAdd("d",-10,Date())))
ORDER BY tblAreaList.AreaSortOrder, tblTherapyType.ThpySortOrder;


.
Click to expand...
Click to expand...
 
J

John W. Vinson

Hi Everyone,

Using Access 2003. I have my query working....almost. I need to see
patients who have been on therapy for more than 10 days. I have a field
ThpyStDtTm and in my query grid, my Criteria is <=DateAdd("d",-10,Date()).
This shows me all the records of therapy that is >10 days old. The problem
is it is not showing me the records for that patient that are less than 10
days old and I need to see these records too but only for the patients on
therapy more than 10 days.

I am thinking I need to take the criteria off of my ThpyStDtTm field and add
a new field to my query grid with an expression that includes the
<=DateAdd("d",-10,Date()) with something else to say show all the records
for this patient but I don't know how to say this in query language.
Click to expand...

Based on the discussions downthread, I think an EXISTS() clause will help, to
find those patients who have an older start time:

SELECT tblAreaList.AreaSortOrder, tblAreaList.AreaName, [PtLocRmNum] & "
"& [BedNumber] AS RmAndBd, [PtLocRmNum] & " " & [BedNumber] & " " &
[PtLName] & ", " & [PtFName] & " ( " & [AdmitNum] & ")" AS
PtNameRmAdmitNum, tblPatients.AdmitNum, tblTherapyType.TherapyDesc,
tblFrequency.Freq, tblPatients.PtLName, tblPatients.PtFName,
tblPatients.PtActive, tblPatients.Isolation, tblPtThpy.ProtocolEvalDate,
tblPatients.PtMemo, tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm,
tblPtLocation.PtLocRmNum, tblBedsList.BedNumber, tblPtThpy.ThpyStDtTm,
tblPtThpy.ThpyEndDtTm, tblPtThpy.ThpyCurrOrdNum, tblPtThpy.ThpyRenDate,
tblPtThpy.ThpyOrigOrdNum, tblPtThpy.ThpyHomeMeds, tblEquipType.TagNum,
[Model] & " " & [TagNum] AS VentAndTag, tblEquipType.Model,
tblPatients.Diagnosis, tblMedList.Med, tblPhysicians.PhysLName,
tblPhysicians.PhysFName, [PhysLName] & ", " & [PhysFName] AS Physician,
tblTherapyType.ThpySortOrder, tblPatients.TxPrtclCan, tblPatients.AppThpy,
tblPatients.AuditorMemo, DateDiff("d",[ThpyStDtTm],Date()) AS DaysInTherapy
FROM (tblPatients LEFT JOIN (tblAreaList RIGHT JOIN (tblBedsList RIGHT
JOINtblPtLocation ON tblBedsList.BedID = tblPtLocation.BedID_fk) ON
tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk) LEFT JOIN (tblTherapyType RIGHT JOIN (tblEquipType
RIGHT JOIN (tblFrequency RIGHT JOIN (tblPhysicians RIGHT JOIN (tblPtThpy
LEFT JOIN tblMedList ON tblPtThpy.ThpyMeds_fk = tblMedList.MedID) ON
tblPhysicians.PhysID = tblPtThpy.PhysID_fk) ON tblFrequency.FreqID =
tblPtThpy.FreqID_fk) ON tblEquipType.EquipID = tblPtThpy.EquipID_fk) ON
tblTherapyType.TherapyTypeID = tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID= tblPtThpy.PtID_fkWHERE (((tblPatients.PtActive)=True) AND
((tblPtLocation.PtLocEnDtTm) Is
Null) AND ((tblPtThpy.ThpyStDtTm)<=DateAdd("d",-10,Date())))
AND EXISTS(SELECT X.PtID FROM tblPatients AS X WHERE X.PtID = tblPatients.PtID
AND X.ThpyStDtTm < DateAdd("d", -10, Date()))
ORDER BY tblAreaList.AreaSortOrder, tblTherapyType.ThpySortOrder;
 
L

Linda RQ

The PtID is my patient unique identifier and my foreign key in each table
that links to the other tables but Admit number uniqly identifies this
patient admission.

I pasted your sql in and removed the word wraps. When I switch to design
view I get an error

Syntax error in Join Operation. This line was highlited

JOINtblPTLocation ON tblBedsList.BedID

I put a space between JOINtbl here and a few lines below that and it worked!
I'll know for sure after my meeting where all the managers bring their list
of patients in the hospital >10 days too. I looked at the query grid and
wow, that's a big expression in the criteria under AdmitNum.

As always, thanks for your help John,

Linda


John Spencer said:
Which field uniquely identifies the patient?

You can use your existing query to identify the patients and then pull all
records for that patient. For instance if AdmitNum uniquely identifies
the patient you want to see then your query could become something like
the following (Watch out for line wraps causing errors in the SQL).

SELECT tblAreaList.AreaSortOrder, tblAreaList.AreaName
, [PtLocRmNum] & "" & [BedNumber] AS RmAndBd
, [PtLocRmNum] & " " & [BedNumber] & " " &
[PtLName] & ", " & [PtFName] & " ( " & [AdmitNum] & ")" AS
PtNameRmAdmitNum
, tblPatients.AdmitNum
, tblTherapyType.TherapyDesc,
tblFrequency.Freq, tblPatients.PtLName, tblPatients.PtFName,
tblPatients.PtActive, tblPatients.Isolation, tblPtThpy.ProtocolEvalDate,
tblPatients.PtMemo, tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm,
tblPtLocation.PtLocRmNum, tblBedsList.BedNumber, tblPtThpy.ThpyStDtTm,
tblPtThpy.ThpyEndDtTm, tblPtThpy.ThpyCurrOrdNum, tblPtThpy.ThpyRenDate,
tblPtThpy.ThpyOrigOrdNum, tblPtThpy.ThpyHomeMeds, tblEquipType.TagNum,
[Model] & " " & [TagNum] AS VentAndTag, tblEquipType.Model,
tblPatients.Diagnosis, tblMedList.Med, tblPhysicians.PhysLName,
tblPhysicians.PhysFName, [PhysLName] & ", " & [PhysFName] AS Physician,
tblTherapyType.ThpySortOrder, tblPatients.TxPrtclCan, tblPatients.AppThpy,
tblPatients.AuditorMemo, DateDiff("d",[ThpyStDtTm],Date()) AS
DaysInTherapy
FROM (tblPatients LEFT JOIN (tblAreaList RIGHT JOIN (tblBedsList RIGHT
JOINtblPtLocation ON tblBedsList.BedID = tblPtLocation.BedID_fk) ON
tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk) LEFT JOIN (tblTherapyType RIGHT JOIN (tblEquipType
RIGHT JOIN (tblFrequency RIGHT JOIN (tblPhysicians RIGHT JOIN (tblPtThpy
LEFT JOIN tblMedList ON tblPtThpy.ThpyMeds_fk = tblMedList.MedID) ON
tblPhysicians.PhysID = tblPtThpy.PhysID_fk) ON tblFrequency.FreqID =
tblPtThpy.FreqID_fk) ON tblEquipType.EquipID = tblPtThpy.EquipID_fk) ON
tblTherapyType.TherapyTypeID = tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID= tblPtThpy.PtID_fk

WHERE tblPatients.AdmitNum IN (

SELECT tblPatients.AdmitNum
FROM (tblPatients LEFT JOIN (tblAreaList RIGHT JOIN (tblBedsList RIGHT
JOINtblPtLocation ON tblBedsList.BedID = tblPtLocation.BedID_fk) ON
tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk) LEFT JOIN (tblTherapyType RIGHT JOIN (tblEquipType
RIGHT JOIN (tblFrequency RIGHT JOIN (tblPhysicians RIGHT JOIN (tblPtThpy
LEFT JOIN tblMedList ON tblPtThpy.ThpyMeds_fk = tblMedList.MedID) ON
tblPhysicians.PhysID = tblPtThpy.PhysID_fk) ON tblFrequency.FreqID =
tblPtThpy.FreqID_fk) ON tblEquipType.EquipID = tblPtThpy.EquipID_fk) ON
tblTherapyType.TherapyTypeID = tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID= tblPtThpy.PtID_fk
WHERE tblPatients.PtActive=True AND
tblPtLocation.PtLocEnDtTm Is Null AND
tblPtThpy.ThpyStDtTm<=DateAdd("d",-10,Date())
)

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

Linda said:
Hi Everyone,

Using Access 2003. I have my query working....almost. I need to see
patients who have been on therapy for more than 10 days. I have a field
ThpyStDtTm and in my query grid, my Criteria is
<=DateAdd("d",-10,Date()). This shows me all the records of therapy that
is >10 days old. The problem is it is not showing me the records for
that patient that are less than 10 days old and I need to see these
records too but only for the patients on therapy more than 10 days.

I am thinking I need to take the criteria off of my ThpyStDtTm field and
add a new field to my query grid with an expression that includes the
<=DateAdd("d",-10,Date()) with something else to say show all the records
for this patient but I don't know how to say this in query language.

Hope it can be done....I am so close!

Thanks, Linda

My sql is below so you can see all my tables and joins but I use the
query grid to get this.

SELECT tblAreaList.AreaSortOrder, tblAreaList.AreaName, [PtLocRmNum] & "
"& [BedNumber] AS RmAndBd, [PtLocRmNum] & " " & [BedNumber] & " " &
[PtLName] & ", " & [PtFName] & " ( " & [AdmitNum] & ")" AS
PtNameRmAdmitNum, tblPatients.AdmitNum, tblTherapyType.TherapyDesc,
tblFrequency.Freq, tblPatients.PtLName, tblPatients.PtFName,
tblPatients.PtActive, tblPatients.Isolation, tblPtThpy.ProtocolEvalDate,
tblPatients.PtMemo, tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm,
tblPtLocation.PtLocRmNum, tblBedsList.BedNumber, tblPtThpy.ThpyStDtTm,
tblPtThpy.ThpyEndDtTm, tblPtThpy.ThpyCurrOrdNum, tblPtThpy.ThpyRenDate,
tblPtThpy.ThpyOrigOrdNum, tblPtThpy.ThpyHomeMeds, tblEquipType.TagNum,
[Model] & " " & [TagNum] AS VentAndTag, tblEquipType.Model,
tblPatients.Diagnosis, tblMedList.Med, tblPhysicians.PhysLName,
tblPhysicians.PhysFName, [PhysLName] & ", " & [PhysFName] AS Physician,
tblTherapyType.ThpySortOrder, tblPatients.TxPrtclCan,
tblPatients.AppThpy,
tblPatients.AuditorMemo, DateDiff("d",[ThpyStDtTm],Date()) AS
DaysInTherapy
FROM (tblPatients LEFT JOIN (tblAreaList RIGHT JOIN (tblBedsList RIGHT
JOINtblPtLocation ON tblBedsList.BedID = tblPtLocation.BedID_fk) ON
tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk) LEFT JOIN (tblTherapyType RIGHT JOIN (tblEquipType
RIGHT JOIN (tblFrequency RIGHT JOIN (tblPhysicians RIGHT JOIN (tblPtThpy
LEFT JOIN tblMedList ON tblPtThpy.ThpyMeds_fk = tblMedList.MedID) ON
tblPhysicians.PhysID = tblPtThpy.PhysID_fk) ON tblFrequency.FreqID =
tblPtThpy.FreqID_fk) ON tblEquipType.EquipID = tblPtThpy.EquipID_fk) ON
tblTherapyType.TherapyTypeID = tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID= tblPtThpy.PtID_fkWHERE (((tblPatients.PtActive)=True)
AND ((tblPtLocation.PtLocEnDtTm) Is
Null) AND ((tblPtThpy.ThpyStDtTm)<=DateAdd("d",-10,Date())))
ORDER BY tblAreaList.AreaSortOrder, tblTherapyType.ThpySortOrder;
Click to expand...
Click to expand...
 
L

Linda RQ

Thanks John [MVP]. I'll try this too and see what the difference is in my
results. Funny....My next report to make is on MVP % for the year
(Mechanical Ventilation Protocols).

Linda


John W. Vinson said:
Hi Everyone,

Using Access 2003. I have my query working....almost. I need to see
patients who have been on therapy for more than 10 days. I have a field
ThpyStDtTm and in my query grid, my Criteria is <=DateAdd("d",-10,Date()).
This shows me all the records of therapy that is >10 days old. The
problem
is it is not showing me the records for that patient that are less than 10
days old and I need to see these records too but only for the patients on
therapy more than 10 days.

I am thinking I need to take the criteria off of my ThpyStDtTm field and
add
a new field to my query grid with an expression that includes the
<=DateAdd("d",-10,Date()) with something else to say show all the records
for this patient but I don't know how to say this in query language.
Click to expand...

Based on the discussions downthread, I think an EXISTS() clause will help,
to
find those patients who have an older start time:

SELECT tblAreaList.AreaSortOrder, tblAreaList.AreaName, [PtLocRmNum] & "
"& [BedNumber] AS RmAndBd, [PtLocRmNum] & " " & [BedNumber] & " " &
[PtLName] & ", " & [PtFName] & " ( " & [AdmitNum] & ")" AS
PtNameRmAdmitNum, tblPatients.AdmitNum, tblTherapyType.TherapyDesc,
tblFrequency.Freq, tblPatients.PtLName, tblPatients.PtFName,
tblPatients.PtActive, tblPatients.Isolation, tblPtThpy.ProtocolEvalDate,
tblPatients.PtMemo, tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm,
tblPtLocation.PtLocRmNum, tblBedsList.BedNumber, tblPtThpy.ThpyStDtTm,
tblPtThpy.ThpyEndDtTm, tblPtThpy.ThpyCurrOrdNum, tblPtThpy.ThpyRenDate,
tblPtThpy.ThpyOrigOrdNum, tblPtThpy.ThpyHomeMeds, tblEquipType.TagNum,
[Model] & " " & [TagNum] AS VentAndTag, tblEquipType.Model,
tblPatients.Diagnosis, tblMedList.Med, tblPhysicians.PhysLName,
tblPhysicians.PhysFName, [PhysLName] & ", " & [PhysFName] AS Physician,
tblTherapyType.ThpySortOrder, tblPatients.TxPrtclCan, tblPatients.AppThpy,
tblPatients.AuditorMemo, DateDiff("d",[ThpyStDtTm],Date()) AS
DaysInTherapy
FROM (tblPatients LEFT JOIN (tblAreaList RIGHT JOIN (tblBedsList RIGHT
JOINtblPtLocation ON tblBedsList.BedID = tblPtLocation.BedID_fk) ON
tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk) LEFT JOIN (tblTherapyType RIGHT JOIN (tblEquipType
RIGHT JOIN (tblFrequency RIGHT JOIN (tblPhysicians RIGHT JOIN (tblPtThpy
LEFT JOIN tblMedList ON tblPtThpy.ThpyMeds_fk = tblMedList.MedID) ON
tblPhysicians.PhysID = tblPtThpy.PhysID_fk) ON tblFrequency.FreqID =
tblPtThpy.FreqID_fk) ON tblEquipType.EquipID = tblPtThpy.EquipID_fk) ON
tblTherapyType.TherapyTypeID = tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID= tblPtThpy.PtID_fkWHERE (((tblPatients.PtActive)=True)
AND
((tblPtLocation.PtLocEnDtTm) Is
Null) AND ((tblPtThpy.ThpyStDtTm)<=DateAdd("d",-10,Date())))
AND EXISTS(SELECT X.PtID FROM tblPatients AS X WHERE X.PtID =
tblPatients.PtID
AND X.ThpyStDtTm < DateAdd("d", -10, Date()))
ORDER BY tblAreaList.AreaSortOrder, tblTherapyType.ThpySortOrder;
Click to expand...
 
J

John W. Vinson

Thanks John [MVP]. I'll try this too and see what the difference is in my
results. Funny....My next report to make is on MVP % for the year
(Mechanical Ventilation Protocols).
Click to expand...

Ooops! clashing TLA's (Three Letter Acronyms)!

I'm hoping I won't need your kind of MVP anytime soon, or at all... the one in
my .sig is in reference to
http://mvp.support.microsoft.com/default.aspx
 

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

Parameter query with expressions and calculations 3
Between Dates Parameters missing a day 5
Need query in hours not days 2
Show only most recent records 2
No records with date criteria 7
multiple Not "this ID" 7
Crosstab showing too many rows 4
Filtering records for 2 types of dates 10

Top