Going mad :) (1 Viewer)

[Twincam]

The following code should sum a field depending on the date. The field IncidentYear is created and set to Last, This or "-" depending on the date. I should get three rows, one for each of the three values..

I get..... you tried to execute a query that does not include the specified expression as part of an aggregate function

I'm sure I've missed something obvious and I can't see it! If I remove the group by clause and the sum field..... it works.

SELECT IIf([Incident Date]>=#01 July 2012#And [Incident Date]<=#30 June 2013#,'Last',
IIf([Incident Date]>=#01 July 2013# And [Incident Date]<=#30 June 2014#,'This','-'))
AS IncidentYear,
Sum(tbl_2c_Acc.[Vehicle Off Road Time]) AS VOR_Acc_Days
FROM (tbl_2c_Acc
LEFT JOIN tbl_Value_Proposition
ON tbl_2c_Acc.Lessee = tbl_Value_Proposition.[Lessee No])
LEFT JOIN tbl_Vehicle_Type
ON tbl_2c_Acc.[Vehicle Reg] = tbl_Vehicle_Type.[Reg No]
GROUP BY IncidentYear;

 

[pr2-eugin]

Why does this not work?

SELECT IIf([Incident Date] >= #01 July 2012# And [Incident Date] <= #30 June 2013#, 'Last', IIf([Incident Date] >= #01 July 2013# And [Incident Date] <= #30 June 2014#, 'This', '-')) AS IncidentYear, Sum(tbl_2c_Acc.[Vehicle Off Road Time]) AS VOR_Acc_Days
FROM (tbl_2c_Acc LEFT JOIN tbl_Value_Proposition ON tbl_2c_Acc.Lessee = tbl_Value_Proposition.[Lessee No])
LEFT JOIN tbl_Vehicle_Type
ON tbl_2c_Acc.[Vehicle Reg] = tbl_Vehicle_Type.[Reg No];

 

[recyan]

[Edit]Missed out typing : "In addition to pr2eugin's SUM() suggestion",[/Edit]
Do not know if below is the problem.
I once faced a problem using Alias field in the Group By. Ended up with :

GROUP BY IIf([IncidentDate]>=#7/1/2012# And [IncidentDate]<=#6/30/2013#,'Last',IIf([IncidentDate]>=#7/1/2013# And [IncidentDate]<=#6/30/2014#,'This','-'));

Thanks

 

[vbaInet]

[Edit]Missed out typing : "In addition to pr2eugin's SUM() suggestion",[/Edit]
Do not know if below is the problem.
I once faced a problem using Alias field in the Group By. Ended up with :

GROUP BY IIf([IncidentDate]>=#7/1/2012# And [IncidentDate]<=#6/30/2013#,'Last',IIf([IncidentDate]>=#7/1/2013# And [IncidentDate]<=#6/30/2014#,'This','-'));

Thanks

That's it.

You GROUP BY the field (if it's not calculated) or you GROUP BY the entire calculation. Which is why it's probably better to save the query with the calculation and create another query based on the saved one to do your SUM and GROUP BY the already calculated field.

 

[Twincam]

Thanks to you all for your help/ideas. In the end it was quickly sorted by repeating the expression in the "Group By" clause. I need to nest SELECTS so I only have the expression in there once (in the inner select), then I can refer to the field name in the outer select as was mentioned above.

No time to do that right now, I'm the fourth contractor in a row working on this program!!

Priorities

1. Get it to work
9,999. Make it faster/neater/commented etc.

 

[vbaInet]

...I'm the fourth contractor in a row working on this program!!

More grease to your elbows

 

[recyan]

That's it.

You GROUP BY the field (if it's not calculated) or you GROUP BY the entire calculation. Which is why it's probably better to save the query with the calculation and create another query based on the saved one to do your SUM and GROUP BY the already calculated field.

Have noticed that irrespective of whatever, I was not able to use an alias in the GROUP BY in the query.
For eg. In @TwinCams query,
even using ( assuming there are similar multiple IncidentDate's )

SELECT IncidentDate AS IncidentYear, Sum(tbl_2c_Acc_1.VehicleOffRoadTime) AS SumOfVehicleOffRoadTime
FROM tbl_2c_Acc_1
GROUP BY IncidentYear;

will throw up an error,
while

SELECT IncidentDate AS IncidentYear, Sum(tbl_2c_Acc_1.VehicleOffRoadTime) AS SumOfVehicleOffRoadTime
FROM tbl_2c_Acc_1
GROUP BY IncidentDate;

will work.
I don't know, but perhaps it could be the way access reads & then executes the query.
Would greatly welcome insights on this behavior ( the way access executes queries ).

Thanks

 

[vbaInet]

... the same way you can't reference an alias in a calculated field when a GROUP BY is applied. I think that the query groups the records, performs any calculations before aliasing the field.

 

[recyan]

... the same way you can't reference an alias in a calculated field when a GROUP BY is applied. I think that the query groups the records, performs any calculations before aliasing the field.

That appears to be the case.

Thanks