a2k - rounding of figures, comments?

[Deano]

One thing that slightly annoys me about my app is a side-affect from the way
it processes figures. It calculates salaries very precisely (to the penny)
but there are rounding issues that cause figures to differ a few pence.
This then affects some reports - my main report might differ from the
detailed reports (which pick up their data from different fields in the same
table) by anything up to 10 pence.

I suppose it's crazy to worry about but for me it would be nice finishing
touch to sort this out (all the reports should tally exactly). I will give
you the main scenario in which this happens;

Someone selects a salary point for an employee. It's worth £12,500. This
is recorded in a field and shown on the form.
Next this figure is divided up into 12 months and again these figures are
recorded on the table and on the form.

£12,500/12 = £1041.67 per month

At a later stage I may need to multiply up these months but this gives me
£12500.04. This is the crux of the problem. The calculation code can be
quite complex and I always knew this might happen but I'm determined to fix
it.

The fix is to put code in that performs this calculation and then makes the
adjustments to as many months as is required.
e.g in this case I would adjust the first four months figures downward by a
penny each. So when the 12 months are combined I get the total figure of
£12,500 exactly.

I'm going to work this out myself, mainly because my problem is so specific
to my application but wondered if anyone's got any general remarks about
this kind of problem or have had similar experiences.

ta
Martin

[John Winterbottom]

"Deano" <de*********@ho tmail.com> wrote in message
news:O7******** **********@ston es.force9.net.. .

Someone selects a salary point for an employee. It's worth £12,500. This
is recorded in a field and shown on the form.
Next this figure is divided up into 12 months and again these figures are
recorded on the table and on the form.

£12,500/12 = £1041.67 per month

At a later stage I may need to multiply up these months but this gives me
£12500.04. This is the crux of the problem. The calculation code can be
quite complex and I always knew this might happen but I'm determined to fix it.

Are you sure you're using a money data type? When I do this:

----------------------------
create table employees
(
empID int not null primary key,
salaryPoint money not null,
monthlySalary money null
)

insert into employees ( empID, salaryPoint )
values(1, 12500)

updateemployees
set monthlySalary = salaryPoint/12

select empID, monthlySalary*1 2
from employees
--------------------------

I get 12,500 as expected.

[Deano]

John Winterbottom wrote:

"Deano" <de*********@ho tmail.com> wrote in message
news:O7******** **********@ston es.force9.net.. .

Someone selects a salary point for an employee. It's worth £12,500.
This is recorded in a field and shown on the form.
Next this figure is divided up into 12 months and again these
figures are recorded on the table and on the form.

£12,500/12 = £1041.67 per month

At a later stage I may need to multiply up these months but this
gives me £12500.04. This is the crux of the problem. The
calculation code can be quite complex and I always knew this might
happen but I'm determined to fix it.

Are you sure you're using a money data type? When I do this:

----------------------------
create table employees
(
empID int not null primary key,
salaryPoint money not null,
monthlySalary money null
)

insert into employees ( empID, salaryPoint )
values(1, 12500)

updateemployees
set monthlySalary = salaryPoint/12

select empID, monthlySalary*1 2
from employees
--------------------------

I get 12,500 as expected.

Cheers John, you've got me thinking. I'm going to play with my datatypes
and see what happens.