Solved Small error in Totals query (1 Viewer)

[nashaz]

Hi

I have a very basic issue with the total query which is doing my head in. Don't think I have had this issue before. I have the following query which works just fine as standard non-total query.

When I press Total and do the following, I get an error message saying 'enter parametric value' and gives me the error for field name 'expireson'.

However, when I click OK, the output works fine. So how do I get rid of this?

Thanks

 

[Gasman]

Why can't you use Max instead of Expression?

 

[nashaz]

Why can't you use Max instead of Expression?

When I select Max, it gives the same error, as seen below:

However, Access changes this to Expression itself when I save and close it. If I change it back to Max, I am getting following error:

 

[Gasman]

No, you remove the Max from the first line.

 

[Minty]

I don't think Access will allow you to refer to an Aliased aggregate function or expression in the same query it was derived from.

You could try referring to the calculation itself in the grouping:

Group By Max(CertificationDate - ValidFrom)

But I don't know if the query designer will let you do that, you might need to simply write the SQL query and save it.

 

[The_Doc_Man]

Taking this step-by-step, the presence of an "Enter Parameter" input box says that the "ExpiresOn" item doesn't exist or can't be found. In this case, I think the third reason is "can't be computed" (in that exact situation.)

Since the query appears to be a JOIN of three tables, and the expression for ExpiresOn comes from two different contributing tables, PERHAPS the problem is that you need to qualify the contributors. However, the aggregate of an expression here gives me pause. I'm not sure you can do this in a single query, but you CAN do a "divide and conquer" by doing the JOINs and simple expressions FIRST in a query and THEN do the GROUP BY and Aggregate functions separately.

I agree with Minty... Build an SQL query similar to this, call it ExpDatQry: (Meaning EXPression DATe QueRY)

SELECT EXC.EmployeeID AS EmpID, 
    EXC.DeliverID AS DlvrID, 
    CDT.CourseID AS CrsID, 
    EXC.CertificationDate AS CertDate, 
    (EXC.CertificationDate+ CNT.ValidFor) AS ExpiresOn, 
    (EXC.CertificationDate+ CNT.ValidFor - Date() ) AS DaysLeft
FROM ( EmployeeXCourse_JT as EXC INNER JOIN CourseDeliveryT as CDT ON EXC.DeliverID = CDT.DeliverID ) 
    INNER JOIN CourseNameT AS CNT ON CDT.CourseID = CNT.CourseID;

I gave EVERY FIELD an alias name so that the second query would be trivial to write.

Now do a query of a query:

SELECT EmpID, DlvrID, CrsID, DaysLeft, MAX( CertDate ), MAX( ExpiresOn)
FROM ExpDatQry
Group By EmpID, DlvrID, CrsID, DaysLeft

 

[nashaz]

No, you remove the Max from the first line.

Im sorry, I dont quite understand what you meants by that.. :$

 

[nashaz]

@Minty and @Doc Thank you for your inputs. Based on both your feedbacks, what I understood is that the totalling needs to happen in a separate query. I have done the following and it works just as I needed it (although its not as sophisticated a solution as what the Doc suggested). Thank you very much