Format number of decimal places in a query
Posted on 2007-04-01
I am trying to create the union of two crosstab queries. The first collates the data by week and all the results are to two decimal places.
The second query (qryAveragor) calculates the average by Year and so the average is to multiple decimal places. For this query I am able to fix the display in design view by changing the format to Fixed.
However when I unite them I the averages display to multiple decimal places. Is there any way of defining the number of decimal places to appear in the result of my union query? Following is the code I am using for the UNION query.
select * from qryComparator
Select * from qryAveragor;
The code of qryAveragor is
PARAMETERS [Forms]![frmMain]![CboCat] Value;
TRANSFORM Avg(tblSales.Value) AS AvgOfValue
SELECT tlCategories.CatDesc, "Average " & Year([salesdate]) AS [Year]
FROM tblSiteData INNER JOIN (tblSales INNER JOIN tlCategories ON tblSales.Cat = tlCategories.Cat) ON tblSiteData.Ident = tblSales.Ident
GROUP BY tlCategories.CatDesc, "Average " & Year([salesdate])
PIVOT [LocnName] & " (" & tblsales.Ident & ")";