Using a query field in a date expression (1 Viewer)

terrytek · Feb 14, 2019

I am trying to use a date field in my query to sum hours after that date by using a "where" expression.

Code:

SELECT tblClassAssignment.StudentID, qryTestingMostRecentTest.StudentFileName, tblTesting.TestTypeID, Max(qryTestingMostRecentTest.TestDate) AS MaxOfTestDate, Sum(tblClassHours.ClassHours) AS SumOfClassHours
FROM ((qryTestingMostRecentTest INNER JOIN tblTesting ON qryTestingMostRecentTest.StudentID = tblTesting.StudentID) INNER JOIN tblClassAssignment ON tblTesting.StudentID = tblClassAssignment.StudentID) INNER JOIN tblClassHours ON tblClassAssignment.AssignmentID = tblClassHours.AssignmentID
WHERE (((tblClassHours.DateReported)>=([qryTestingMostRecentTest].[MaxOfTestDate])))
GROUP BY tblClassAssignment.StudentID, qryTestingMostRecentTest.StudentFileName, tblTesting.TestTypeID
ORDER BY qryTestingMostRecentTest.StudentFileName;

But then I get an Enter Parameter Value for qryTestingMostRecentTest.MaxOfTestDate.

How can I use that MaxOfTestDate to sum ClassHours that are reported on or after the MaxOfTestDate?

Ranman256 · Feb 14, 2019

you get params if something is wrong..
does the query : qryTestingMostRecentTest.[MaxOfTestDate] create a valid result?

theDBguy · Feb 14, 2019

Hi. Can you post the SQL statement for qryTestingMostRecentTest? Is MaxOfTestDate the alias name for the column?

plog · Feb 14, 2019

SELECT:
qryTestingMostRecentTest.TestDate

WHERE:
[qryTestingMostRecentTest].[MaxOfTestDate]

qryTestingMostRecentTest has both TestDate and MaxOfTestDate fields? The query you posted disagrees. It can't find [MaxOfTestDate] in qryTestingMostRecent.

My guess is you want to both create MaxOfTestDate in the SELECT of this query you posted and use it as criteria in the WHERE of the same query. Not possible. Please explain what it is you are trying to accomplish.

terrytek · Feb 14, 2019

plog said:
SELECT:
qryTestingMostRecentTest.TestDate

WHERE:
[qryTestingMostRecentTest].[MaxOfTestDate]

qryTestingMostRecentTest has both TestDate and MaxOfTestDate fields? The query you posted disagrees. It can't find [MaxOfTestDate] in qryTestingMostRecent.

My guess is you want to both create MaxOfTestDate in the SELECT of this query you posted and use it as criteria in the WHERE of the same query. Not possible. Please explain what it is you are trying to accomplish.

I'm not sure what you mean. The only part of the SELECT statement that includes TestDate is
Max(qryTestingMostRecentTest.TestDate) AS MaxOfTestDate.

I am trying to sum hours that have been reported since the most recent test date.

terrytek · Feb 14, 2019

theDBguy said:
Hi. Can you post the SQL statement for qryTestingMostRecentTest? Is MaxOfTestDate the alias name for the column?

Code:

SELECT tblClasses.Class, tblTesting.StudentID, qryStudentsExtended.StudentFileName, tblTesting.TestDate, tblTesting.NextForm, NextFormEven([NextForm]) AS NextFormEven
FROM tblTestType INNER JOIN (((tblStudents INNER JOIN qryStudentsExtended ON tblStudents.StudentID = qryStudentsExtended.StudentID) INNER JOIN (tblClasses INNER JOIN tblClassAssignment ON tblClasses.ClassID = tblClassAssignment.ClassID) ON tblStudents.StudentID = tblClassAssignment.StudentID) INNER JOIN tblTesting ON tblStudents.StudentID = tblTesting.StudentID) ON tblTestType.TestTypeID = tblTesting.TestTypeID
GROUP BY tblClasses.Class, tblTesting.StudentID, qryStudentsExtended.StudentFileName, tblTesting.TestDate, tblTesting.NextForm, tblClassAssignment.ClassID, tblClassAssignment.AcademicYr
HAVING (((tblClassAssignment.ClassID)<=6) AND ((tblClassAssignment.AcademicYr)="2018-19"))
ORDER BY tblClassAssignment.ClassID, qryStudentsExtended.StudentFileName;

plog · Feb 14, 2019

SELECT tblClasses.Class, tblTesting.StudentID, qryStudentsExtended.StudentFileName, tblTesting.TestDate, tblTesting.NextForm, NextFormEven([NextForm]) AS NextFormEven...

There's no field in there called [MaxOfTestDate]. You are referencing something that doesn't exist.

plog · Feb 14, 2019

I am trying to sum hours that have been reported since the most recent test date

You need a subquery to get the most recent test date:

Code:

SELECT MAX(TestDate) AS MostRecentTest
FROM YourTableHere

Name that 'sub1', then create a new query using it and the hours table:

Code:

SELECT SUM(HoursField) AS HoursSinceTest
FROM YourHoursTable, sub1
WHERE [YourHoursTable].[DateField]>=[sub1].[MostRecentTest]

Using a query field in a date expression (1 Viewer)

terrytek

Registered User.

Ranman256

Well-known member

theDBguy

I’m here to help

plog

Banishment Pending

terrytek

Registered User.

terrytek

Registered User.

plog

Banishment Pending

plog

Banishment Pending

Similar threads

Users who are viewing this thread