Re: Date Criteria Question

Gary,
Thanks, I have tryed your suggestions, the first one returns 0 records and
the second one works but it show all previous record to the date entered by
the user.
What I would like is
If a client has a training session for 07/0307 & 09/03/07 & 10/03/07
and the user enters 10/03/07 then I want the 10th & 09th to show but not the
07th
If the client dosen't have a record for the 10th then I don't want any
record for that client to show.

Thanks heaps for your help
Rodney

"Gary Walter" wrote:

actually...I'd probably just create a separate query

qryLatestPriorDate

SELECT
q.ClientID,
Max(q.[Date]) As LatestPriorDate
FROM
tblTrainingSessions As q
WHERE
q.[Date]<[Forms]![frmPrintClientPreSessionReport]![SessionDate]
GROUP BY
q.ClientID;

then join this prequery to original query...

also...I wonder if you won't get better performance
if you move "HAVING" clause to the "WHERE" clause...

"Gary Walter" wrote:

"Rodney" wrote:
I have a query that groups records by Client and uses the date entered by
the
user which all works fine, but I also want to show the last entered date
prior to the date entered by the user for each client that has an entry
on
the date entered by user.
This is my query

SELECT tblClientDetails.ClientID, tblClientDetails.ClientFirstName,
tblClientDetails.ClientLastName, tblClientDetails.ClientHeightOnStart,
tblClientDetails.ClientChestOnStart, tblClientDetails.ClientWaistOnStart,
tblClientDetails.ClientHipsOnStart, tblClientDetails.ClientWeightOnStart,
tblClientDetails.ClientMedicalHistory,
tblClientDetails.ClientEmergencyContact, tblTrainingSessions.Date,
tblTrainingSessions.ClientChest, tblTrainingSessions.ClientWaist,
tblTrainingSessions.ClientHips, tblTrainingSessions.ClientCurrentWieght,
tblTrainingSessions.TrainingSessionNotes
FROM tblClientDetails INNER JOIN tblTrainingSessions ON
tblClientDetails.ClientID = tblTrainingSessions.ClientID
GROUP BY tblClientDetails.ClientID, tblClientDetails.ClientFirstName,
tblClientDetails.ClientLastName, tblClientDetails.ClientHeightOnStart,
tblClientDetails.ClientChestOnStart, tblClientDetails.ClientWaistOnStart,
tblClientDetails.ClientHipsOnStart, tblClientDetails.ClientWeightOnStart,
tblClientDetails.ClientMedicalHistory, tblClientDetails.ClientGoals,
tblClientDetails.ClientEmergencyContact, tblTrainingSessions.Date,
tblTrainingSessions.ClientChest, tblTrainingSessions.ClientWaist,
tblTrainingSessions.ClientHips, tblTrainingSessions.ClientCurrentWieght,
tblTrainingSessions.TrainingSessionNotes
HAVING
(((tblTrainingSessions.Date)=[Forms]![frmPrintClientPreSessionReport]![SessionDate]))
ORDER BY tblTrainingSessions.Date;
hope someone can help
Thanks
Rodney

here might be...

I'm sorry but I have to get to work...

I've separated out a correlated subquery for you
but I don't have time to test and I cannot remember
if wrapping in First aggregate will work for you...

SELECT
C.ClientID,
C.ClientFirstName,
C.ClientLastName,
C.ClientHeightOnStart,
C.ClientChestOnStart,
C.ClientWaistOnStart,
C.ClientHipsOnStart,
C.ClientWeightOnStart,
C.ClientMedicalHistory,
C.ClientEmergencyContact,
T.[Date],
T.ClientChest,
T.ClientWaist,
T.ClientHips,
T.ClientCurrentWieght,
T.TrainingSessionNotes,
First(

(SELECT
Max(q.[Date])
FROM
tblTrainingSessions As q
WHERE
q.ClientID=C.ClientID
AND
q.[Date]<[Forms]![frmPrintClientPreSessionReport]![SessionDate])

) As LatestPriorDate
FROM
tblClientDetails As C
INNER JOIN
tblTrainingSessions As T
ON
C.ClientID =T.ClientID
GROUP BY
C.ClientID,
C.ClientFirstName,
C.ClientLastName,
C.ClientHeightOnStart,
C.ClientChestOnStart,
C.ClientWaistOnStart,
C.ClientHipsOnStart,
C.ClientWeightOnStart,
C.ClientMedicalHistory,
C.ClientGoals,
C.ClientEmergencyContact,
T.[Date],
T.ClientChest,
T.ClientWaist,
T.ClientHips,
T.ClientCurrentWieght,
T.TrainingSessionNotes
HAVING
T.[Date]=[Forms]![frmPrintClientPreSessionReport]![SessionDate]
ORDER BY
T.Date;






.

Relevant Pages

  • Re: Date Criteria Question
    ... "Gary Walter" wrote: ... MaxAs LatestPriorDate ... FROM tblClientDetails INNER JOIN tblTrainingSessions ON ...
    (microsoft.public.access.queries)
  • Re: Date Criteria Question
    ... actually...I'd probably just create a separate query ... MaxAs LatestPriorDate ... FROM tblClientDetails INNER JOIN tblTrainingSessions ON ...
    (microsoft.public.access.queries)
  • Re: Date Criteria Question
    ... "Rodney" wrote: ... prior to the date entered by the user for each client that has an entry on ... FROM tblClientDetails INNER JOIN tblTrainingSessions ON ...
    (microsoft.public.access.queries)
Loading