Re: Date Criteria Question

From: "Gary Walter" <gary@xxxxxxxxxxx>
Date: Fri, 16 Mar 2007 08:24:28 -0500

"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;

.

Follow-Ups:
- Re: Date Criteria Question
  - From: Gary Walter

Prev by Date: Re: Calculations using subquery
Next by Date: Re: Date Criteria Question
Previous by thread: Re: Question about WHERE syntax, errors with "Type Mismatch".
Next by thread: Re: Date Criteria Question
Index(es):
- Date
- Thread

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
... actually...I'd probably just create a separate query ... MaxAs LatestPriorDate ... FROM tblClientDetails INNER JOIN tblTrainingSessions ON ...
(microsoft.public.access.queries)