Limiting Decimal Places in Make Table Query

[Taffski]

Hi,

I have created a Make Table Query to populate a new table with Count and Average functions from two other tables.

No problems there.

The issue is that the Average:[Percent] field is outputting 12+ decimal places.

I would like to limit it to 1.

I've tried a few options but to no avail.

Here's the SQL from the MT Query.

Grateful for any assistance.



SELECT [Results-BGS].School, [Results-BGS].Year, [Results-BGS].[Month ID], [Results-BGS].Level, [Results-BGS].[Subject LO], [Results-BGS].[Subject Description], [Results-BGS].Type, [Results-BGS].Syllabus, [Results-BGS].Attempt, [Results-BGS].Result, Count([Results-BGS].Result) AS [Total Result], Avg([Results-BGS].Percent) AS Average, Min([Results-BGS].Percent) AS Minimum, Max([Results-BGS].Percent) AS Maximum INTO [RESULTS-COMBINED]
FROM [Results-BGS]
GROUP BY [Results-BGS].School, [Results-BGS].Year, [Results-BGS].[Month ID], [Results-BGS].Level, [Results-BGS].[Subject LO], [Results-BGS].[Subject Description], [Results-BGS].Type, [Results-BGS].Syllabus, [Results-BGS].Attempt, [Results-BGS].Result
HAVING ((([Results-BGS].School)="BGS"))
ORDER BY [Results-BGS].Year, [Results-BGS].[Month ID], [Results-BGS].Level, [Results-BGS].[Subject LO], [Results-BGS].Type, [Results-BGS].Syllabus, [Results-BGS].Attempt, [Results-BGS].Result DESC;

 

[Addyman]

Have you tried using the ROUND function:

http://www.w3schools.com/sql/sql_func_round.asp

 

[Taffski]

Thanks for the reply.

Yes, I've tried the ROUND function in a variety of ways but it still doesn't seem to be working so I am obviously doing something wrong.

Would one of you learned bods be so kind as to point out in the SQL posted above where I should be putting it so I can compare that with where I have tried it.

Many thanks.

 

[Pat Hartman]

When you use Single or Double, the number of decimal places is adjusted automatically to hold the calculated value. Usually, you would limit the decimal places for display purposes on your forms and reports but more precision may be required to hold the actual value.
I use the Currency data type rather than Single or Double. Currency is actually an integer that is scaled to hold specifically 4 decimal digits. Look for an article named "when Access Math doesn't add up" on www.fmsinc.com to see why I switched entirely to currency unless I need more than 4 decimal digits. They have lots of other very interesting articles also.

If you want to control the number of decimal places yourself, you need to round or truncate the data BEFORE it is saved. If you round for display purposes, you could end up with situations where a column of numbers in a report doesn't appear to add up because Access will be adding the real value but displaying the rounded value.

 

[MStef]

Try this:
Make a table (Table2New), look a column x1 (Format and Decimal places properties). Make an APPEND QUERY on this table, make a Form (with a
command button). Look at "DemoDecimalPlacesA2000.mdb" (attachment, zip).
Open Form1 and try. I think it'll work.

 

[DarwinGarcia]

I've just encountered the same problem, but I don't make a new table. I need the query output itself to be properly limited to 2 decimal places.