Solved Error with date calc in Query (1 Viewer)

Kayleigh

Member
Local time
Today, 12:07
Joined
Sep 24, 2020
Messages
706
I am writing a query to average the points per pupil for dates since they were last awarded. I have used an aggregate function to average the points but now an error comes up when trying to run the query. Is it not possible to use this expression?
error with query.png

Can you suggest another way to do this?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:07
Joined
Oct 29, 2018
Messages
21,358
Hi. Can you please post the SQL statement for your query?
 

Kayleigh

Member
Local time
Today, 12:07
Joined
Sep 24, 2020
Messages
706
SELECT tblPupil.fldPplForename, tblPupil.fldPplSurname, Avg((IIf([fldPunctuality]=-1,1,0))+(IIf([fldOnTask]=-1,1,0))+(IIf([fldRespect]=-1,1,0))+(IIf([fldBehaviour]=-1,1,0))) AS fldTotalPoints, jtblPointLog.fldDate, (IIf([fldTotalPoints]<1.5,"No Award",IIf([fldTotalPoints]<2.5,"Bronze",IIf([fldTotalPoints]<3.2,"Silver","Gold")))) AS fldAward
FROM qryRecentDate, tblAwardLog INNER JOIN (tblPupil INNER JOIN jtblPointLog ON tblPupil.fldPplID = jtblPointLog.[fldPPplID]) ON (tblPupil.fldPplID = tblAwardLog.fldAPplID) AND (tblAwardLog.fldAPplID = tblPupil.fldPplID)
GROUP BY tblPupil.fldPplForename, tblPupil.fldPplSurname, jtblPointLog.fldDate, qryRecentDate.MaxOffldDateAwarded, (IIf([fldTotalPoints]<1.5,"No Award",IIf([fldTotalPoints]<2.5,"Bronze",IIf([fldTotalPoints]<3.2,"Silver","Gold"))))
HAVING (((jtblPointLog.fldDate)>[MaxOffldDateAwarded]))
ORDER BY tblPupil.fldPplSurname;
 

plog

Banishment Pending
Local time
Today, 07:07
Joined
May 11, 2011
Messages
11,613
Your error message is because your HAVING is incorrect. However, even after fixing it I don't think your query will work because you can't calculate a field and immediately use it (fldTotalPoints) in the same query.

First, HAVING is criteria on the aggregate data, WHERE is used on individual records. That means HAVING must use aggregate functions (SUM, COUNT, AVG, etc.). Your criteria is comparing data to individual recrods therefore it should go in a WHERE. So change the HAVING to WHERE and move it above the GROUP BY clause.

But then, fldTotalPoints is going to screw you up. Its a calculated value that doesn't exist until you calculate it, but you are trying to use it in another calculated field which I don't think will work. So, to get around that you need to calculate fldTotalPoints, then save the query and start a new query built on that query to do further calculations.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:07
Joined
May 7, 2009
Messages
19,175
first of all my suggestion is to Create New table for those Awards:

tblAwards(table)
LowPoint (single)
HighPoint(single)
Award(text)

example records:

LowPoint.............HighPoint.............Award
0.............................1.4...........................No Award
1.5..........................2.4...........................Bronze
2.5..........................3.1...........................Silver
3.2..........................9999.......................Gold

Next you need to modify the query you posted.
this part:

...Avg((IIf([fldPunctuality]=-1,1,0))+(IIf([fldOnTask]=-1,1,0))+(IIf([fldRespect]=-1,1,0))+(IIf([fldBehaviour]=-1,1,0))) AS fldTotalPoints

can be simplified without using IFFs:

Avg(Abs([fldPunctuality])+Abs([fldOnTask])+Abs([fldRespect])+Abs([fldBehaviour])) AS fldTotalPoints

try averaging first and drop all Joining tables.
then, Create New record that will Join tblAwards to your Query, eg:

SELECT yourQuery.fldPplForename, yourQuery.fldPplSurname, yourQuery.fldTotalPoints,
DLookup("Award","tblAwards", [fldTotalPoints] & " Between LowPoint And HighPoint") As fldAward
From yourQuery;

Create the Final Query to join the Other tables/queries.
 

Kayleigh

Member
Local time
Today, 12:07
Joined
Sep 24, 2020
Messages
706
Thanks. Appreciate your step by step guidance. I was a little muddled there!

I have reworked that calculation and it worked best like this:
  1. Query to find most recent date of award using aggregate function.
  2. Then select query to display all points for each pupil from that date till current.
  3. Then Average all points for each pupil using aggregate function.
  4. Finally another query to work out which award has been achieved based on table with low and high points.
I'm just wondering if there was any way to do this in less steps as it seems that each aggregate function must be in its own query?
 
Last edited:

Users who are viewing this thread

Top Bottom