Re: Date Criteria Question

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
    ... If the client dosen't have a record for the 10th then I don't want any ... MaxAs LatestPriorDate ... FROM tblClientDetails INNER JOIN tblTrainingSessions ON ...
    (microsoft.public.access.queries)
  • Re: Date Criteria Question
    ... "Gary Walter" wrote: ... 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