Solved Error with date calc in Query

[Kayleigh]

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?

Can you suggest another way to do this?

 

[theDBguy]

Hi. Can you please post the SQL statement for your query?

 

[Kayleigh]

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]

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]

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]

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?