Between dates with single date field 1

[byurow]

Hi,

I am trying to pull data where the data is between 2 dates. The problem is, I don't want the user to have select the dates. I want to be able to pull the data that is between the most current 2 dates.

My tables are as follows:

TblHospitalVisit
HospitalVisitID
HospitalVisitDateTime
PatientID
(and lots of other fields)

TblAssessment
AssessmentID
PatientID
AssessmentDateTime
(and lots of other fields)

I need to be able to get all of the hospital visits that occurred between the last assessment and the assessment before that. So basically the user will select the patient and the assessment date they are interested in viewing. I want to show all hospital visits that have occurred between the date selected and the previous date. They will not select the previous assessment date, I want access to do that behind the scenes.

Is this possible? Any help would be most appreciated!

Thanks,

Brenda

 

[Golom]

First you need a query to pick up the Last and previous assessments

Select A.PatientID, 
       MAX(A.AssessmentDateTime) As [LastDT],
       MAX(B.AssessmentDateTime) As [PrevDT]
 
From tblAssessment A INNER JOIN tblAssessment B
     ON     A.PatientID = B.PatientID
        AND B.AssessmentDateTime < A.AssessmentDateTime

Group By A.PatientID

Save that as "qryAssess" (for example) then

Select H.PatientID, H.HospitalVisit, H.HospitalVisitDateTime

From   TblHospitalVisit H INNER JOIN qryAssess A
       ON H.PatientID = A.PatientID

Where  H.HospitalVisitDateTime BETWEEN A.PrevDT AND A.LastDT

 

[byurow]

Ahhh you are a geneous! Thank you so much. That makes perfect sense. I will give it a try but I'm sure it will work.

Thanks so much for the quick reply!

Brenda

 

[byurow]

Golom,

Your code works perfectly. Now to throw a wrench in! What I need to do now is allow the user to select an assessment and have only those hospital visits that occurred between the date selected and the previous assessment. I know that I need to use a parameter (and usually have no trouble doing so), but I can't figure out how to call it!

On the 2nd query, I already have a parameter that limits the results to only the patient selected. I have the parameter get filled through the code as follows:

Set qdfHospitalVisit = CurrentDb.QueryDefs("QryFindCurrentHospitalVisit&quot

qdfHospitalVisit.Parameters("Patient_No&quot.Value = plngPatient_No

Set rsHospitalVisit = qdfHospitalVisit.OpenRecordset

This works beautifully with the code you gave me. I put the parameter on the 2nd query and all is well.

What I THINK I need to do is put a parameter on the first query to set the LastDT = to the value in the cbo box the user selected. I have no idea how to do this or if it is even possible! I guess what I am asking is: Is there a way to call a query, using a parameter, then using that recordset, call another query with another parameter? Can you even put a parameter on a query that has a self-join like the one you supplied me? Or is there a simpler way to do this? Would it be possible to maybe have a field called "txtPreviousAssessmentDate" and have that fill with the previous assessment date then call a query using the 2 dates (txtAssessmentDate and txtPreviousAssessmentDate) as the parameters?

I hope this all makes sense to you as I am pretty confused myself

HELP!

Thanks again!

Brenda

 

[Golom]

Just to be sure I understand, let me rephrase the requirement.

You want to have the user select a "Last Appointment Date" from the possible list of appointments for a particular patient and then perform the same processing only use the selected appointment rather than the most recent one on file in your reporting. An example would be

tblAssessment
PatientID     AssessmentDateTime

  123            12/01/2003
  123            11/15/2003
  123            11/01/2003
  123            10/15/2003

You want to present a list of these dates in a combo box and then show hospital visits between the selected date and the date that precedes it for a specific patient. (presumably you don't want the earliest date to appear because it does not have a preceding date.)

Assuming all that is more or less correct, you first need a query to populate the combo box.

PARAMETERS [Patient ID] text;
Select A.PatientID, A.AssessmentDateTime
From tblAssessment A
Where  A.PatientID = [Patient ID]
Group By A.PatientID, A.AssessmentDateTime
Having A.AssessmentDateTime > MIN(A.AssessmentDateTime)

You will of course set the parameter value in your code with something like

qdfAssessment.Parameters("Patient ID").Value = plngPatient_No

Your qryAssess needs to be modified to

Select A.PatientID, A.AssessmentDateTime As [LastDT],
       (Select MAX(B.AssessmentDateTime) From tblAssessment B
        Where      B.PatientID = A.PatientID
               AND B.AssessmentDateTime < A.AssessmentDateTime) As [PrevDT]
 
From tblAssessment A

This gives you all the date intervals instead of just the most recent ones.

Then, after the user selects a datetime from the combo box

PARAMETERS [AssessDTStart] Date;
Select H.PatientID, H.HospitalVisit, H.HospitalVisitDateTime

From   TblHospitalVisit H INNER JOIN qryAssess A
       ON H.PatientID = A.PatientID

Where  A.LastDT = [AssessDTStart] AND
       H.HospitalVisitDateTime BETWEEN A.PrevDT AND A.LastDT

and of course you will set the parameter value in your code before running the query.

This approach may require that you set up two sets of queries ... the first to automatically pick up the latest date and the second to use the user-selected date

OR

you could use this second query exclusively and "behind the scenes" look up the latest date and supply it as the query parameter when they are not selecting a date.

Sorry ... my copy of Access is temporarily sick due to a virus so I'm doing all this from memory and can't really test any of it at the moment.

Good Luck!

 

[byurow]

you pretty much have th jist of what I need done. You have definetly given me what I think I need (at least at the moment as I'm not sure my client really knows what he needs)

Thank you so much. I'm pretty sure I can get this to work for me!

Thanks again!

Brenda

 

[byurow]

Golom,

You are INCREDIBLE! Thank you soooo much. The code works perfectly (I just now got around to testing it).

Thank you, thank you, thank you!

Brenda