ROUND won't !

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone tell me why the following gives me 12 decimal places in the [UF 1]
column instead of 1? (This an ADP in Access 2000 format under Office 2003.)

SELECT TOP 100 PERCENT a.LOCATION, a.ITEM_NUMBER, ROUND(a.UNDER_FORECAST_1
/ b.AVERAGE_COST, 1) AS [UF 1]
FROM dbo.View_REPORTS_FORECAST_PERFORMANCE a INNER JOIN
dbo.View_REC_STOCK_EX1 b ON a.LOCATION = b.LOCATION AND
a.ITEM_NUMBER = b.ITEM_NUMBER
WHERE ((a.UNDER_FORECAST_1 + a.UNDER_FORECAST_2 + a.UNDER_FORECAST_3) / 3 >
a.UNDER_FORECAST_AVE)
ORDER BY (a.UNDER_FORECAST_1 + a.UNDER_FORECAST_2 + a.UNDER_FORECAST_3) /
(a.SALES_1 + a.SALES_2 + a.SALES_3 + 1) DESC
 
Hi Allen

In itself your SQL statement is OK.

1. What are you doing with the result? I suspect the Rounding issue is to do
with how you handle the result.
2. Note that you do not need the 'TOP 100 PERCENT' statement as, by
definition, you get 100% of the results if you omit it.

Cheers.

BW
 
Thanks, BW.
1. What are you doing with the result? I suspect the Rounding issue is to do
with how you handle the result.
Click to expand...

The SQL belongs to a 'view' query. I see no way to format the output
columns. I guess I could use a form, but then what's the point of having the
ROUND function?
2. Note that you do not need the 'TOP 100 PERCENT' statement as, by
definition, you get 100% of the results if you omit it.
Click to expand...

Access bungs that in automatically when I use the ORDER BY clause.
 
Back
Top