Formatting renamed fields in Aggregate Queries

[HGMonaro]

Is this normal behaviour? (I'm using Access 2010)

Basic aggregate (summary) query calculating the average of several fields.

I set the display format (eg. fixed with 2 decimals) in the properties of a field (type is Double) and it displays correctly (ie. 2 decimal places displayed). If I add a field name (eg. AVG : .....) then it won't display using the fixed format (get lots of decimal places), UNLESS it's named Expr1 or Expr2 (default naming of calculated fields). Using Expr3 stops the formatting working.

Just to add insult to my intelligence, while experimenting changing the name I managed to get one with a seperate field name to stick but can't repeat it.

 

[HGMonaro]

Just to add to my frustration, managed to get another one to work, but not the actual name I want! Also, using '1' for the name seems to work ok, but not '2'

 

[boblarson]

Post the EXACT SQL you are attempting to use.

 

[HGMonaro]

Hi Bob.

At home now so tried it on Access 2002 and behaves somewhat similar (although not identical). I created a simple DB to test.

Table called Data with 3 fields, Classification (Text), Value1 (Double), Value2(Double)

Data in table was:
Classification Value1 Value2
A 4.23 6.5
A 5.42 2.34
B 3.45 8.7
B 3.2 2.65

So, I want my result to show the averages with 2 decimal places. I set the Field Properties of the two averaged fields to Fixed with 2 decimal places.

This works:
SELECT Data.Classification, Avg(Data.Value1) AS AvgOfValue1, Avg(Data.Value2) AS AvgOfValue2
FROM Data
GROUP BY Data.Classification;

This Doesn't: (renamed the fields to Val1,Val2)
SELECT Data.Classification, Avg(Data.Value1) AS Val1, Avg(Data.Value2) AS Val2
FROM Data
GROUP BY Data.Classification;


In this one the 1st avg works, the 2nd doesn't:
SELECT Data.Classification, Avg(Data.Value1) AS x, Avg(Data.Value2) AS z
FROM Data
GROUP BY Data.Classification;

Take the 2nd rename away and it works as intended:
SELECT Data.Classification, Avg(Data.Value1) AS x, Avg(Data.Value2) AS AvgOfValue2
FROM Data
GROUP BY Data.Classification;

Very strange!

 

[HGMonaro]

ok, seem to have sussed it out...

It doesn't remember field properties (well the format ones at least since that's all I have been playing with) when you give a field a specific name (eg. want the system derived name of AvgofVal1 to be Val1). You seem to need to add your names, save the query, then specify the 'fixed' format and decimals required. That worked for me.