using expression in another field of query give wrong answer

[RECrerar]

I have an anomally happening in a query.

From the fields:
Total Hours
Total Cost
Week 1
Week 2
Week 3
..
Week n

I am doing a simple calculation such that (this converts a cost into hours) - Before you think it is this is not the same as my other thread

Expr: ([Week n]/[Total Cost])*[Total Hours]

When I enter the expression as such into each weekly field it works fine.

However, if I try and split the equation in two, in order to create another field of Hourly rate I get completely wrong answers. The other method is.

Expr1: [Total Cost]/[Total Hours]
Expr2: [Week n]/[Expr1]

The two are mathematically equavilent and Excel doesn't have a problem with it (I used Excel to check I wasn't just being a muppet), so why does it give me wrong answers in Access?

 

[boblarson]

Change this:
Expr2: [Week n]/[Expr1]
to this:
Expr2: [Week n]/([Total Cost]/[Total Hours])

 

[RECrerar]

Hi,

Yeah I know that would work, hence why I am using the first equation in my post. But the question is, why doesn't the second option work. One specific example is I have:

Total Cost = 100400
Total Hours = 591
Week n = 1259.

When I do the equation all at once without an intermediate step it give the correct answer = 8.

When I do it with the intermediate step I get:

Expr1 = 169.8815567, this is correct
But then from [week n]/Expr1 I get 10....... (I can't remember the exact didgits and not at work now) rather than 8.

I can solve the issue by doing it all in one but the question is why does this happen?

 

[ParanoidAndroid]

When I do the equation all at once without an intermediate step it give the correct answer = 8.

The correct answer is 7.41104581673307... which even with rounding is only 7

Running your numbers through a database I get the same answer for both methods you describe.

 

[RECrerar]

Okay, sorry about the maths I was trying to remember the values off the top of my head without actually calculating them, hence the error. Interesting that you get the same answer for both, I really don't but I'll look at it again sometime

 

[RECrerar]

Hi again,

Following on from this. Can I use this expression directly in a CrossTab query or do I have to generate these values in an initial query and then use this query to create the CrossTab query? If it is possible how do I do this?