Why is Access rounding up?

[Nothnless]

Hi,

For some reason this is rounding up to 40.

SumOfSum Of Gross Amount	SumOfSumProduct	Weighted Average
12,492,289,384.91	46,675,715,013.42	40.00

<tbody>
</tbody>

It should just equal 3.736

I can figure out why. All the properties are sent to standard with 2 decimal places.

 

[Joe4]

What did you choose for the "Field Size" property?
If you chose something that does NOT allow decimals, like Integer or Long Integer, that will trump what you enter in for decimal places (since Integers do not have decimals).

 

[Nothnless]

I used double

 

[Joe4]

Is that calculation done in a Query?
If so, please post the SQL code of that query.
Or, post the calculation, wherever it is.

 

[Nothnless]

Is that calculation done in a Query?
If so, please post the SQL code of that query.
Or, post the calculation, wherever it is.

Yes, here is the SQL:

SELECT DISTINCTROW Sum([Billing Schedule SubTotal Query].[Sum Of Gross Amount]) AS [Sum Of Sum Of Gross Amount], Sum([Billing Schedule SubTotal Query].[SumProduct]) AS [Sum Of SumProduct], Sum(([SumProduct])/([Sum Of Gross Amount])) AS [Weighted Average]
FROM [Billing Schedule SubTotal Query];

Essentially I'm doing a weighted average calculation which works however it should equal 3.736

 

[Joe4]

I did one query, where I just got the SUM of each field, then another where I just divided the SUMs, and it seemed to work out for me, i.e.

SumOfGross Amount	SumOfProduct	ave
12492289384.91	46675715013.42	3.73636197299445

<caption> Query4 </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

Note: If you click on that Weighed Average field in the Query Builder, and right-click and select Properties, what does it show for Format and Decimal Places?

 

[Nothnless]

I did one query, where I just got the SUM of each field, then another where I just divided the SUMs, and it seemed to work out for me, i.e.

SumOfGross Amount	SumOfProduct	ave
12492289384.91	46675715013.42	3.73636197299445

<tbody>
</tbody>

Note: If you click on that Weighed Average field in the Query Builder, and right-click and select Properties, what does it show for Format and Decimal Places?

I made sure I chaged all the fields to standard 2 decimal places.

Yeah I basically did the same thing here but its spitting out 40:

Sum Of Sum Of Gross Amount: Sum([Billing Schedule SubTotal Query].[Sum Of Gross Amount]) Sum Of SumProduct: Sum([Billing Schedule SubTotal Query].[SumProduct]) Weighted Average: Sum(([SumProduct])/([Sum Of Gross Amount]))

 

[Joe4]

Trying splitting it up into two queries, like I did.
Sometimes, when you try to do too much in one single query in Access, it doesn't work as expected.

 

[xenou]

BTW, the sum of the dividends is not necessarily the same as the average of the sums (assuming you have two columns of data you are querying, and not just two scalar values).