Incorrect calculations in a query.

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

Guest

Hi, can someone please put me out of my misery. This is driving me nuts.

I have a database based on a newsagent shop where I have set up a query that
has grouped the papers together. I am now trying to work out the weekly
values for each paper.

I have a query with a calculated field thus:
total: [monday]+[tuesday]+[wednesday]+[thursday]+[friday]+[saturday]

where the day of the week is made up of the following formula:
Sum(Abs([tbldelivery].[Monday])) - it's based on a yes/no field hence the ABS

My total column is:
WeeklyTotal: [total]*[price]

All well and good. But in a couple of instances the calculation isn't
correct, e.g. it multiplies 27 * 0.39 and gives me 10.40, not 10.53!

I am using Access 2000 and Windows XP.
 
Where is the .39 coming from? Is that a hardcoded value or a stored value
or a calculated value? Is it possible that you are viewing .39 but are
actually working with a value that is something like .385185...?

When Access calculates it will use the actual stored value versus the
displayed (rounded) value.
 
Hi John, the 0.39 is a value from a field called price (not sure what
hardcoded means here, sorry, but I keyed the value in). The field is
formatted as Currency, 2 dec places.
 
I don't see any reason that you would get an invalid value. It appears to
be multiplying 26.6667 * .39. For purposes of validating everything,
could you temporarily add the column Total to your query and make sure it is
27.

Other than that I have no idea what could be causing your problem.

If you are still getting an error, perhaps you could post the SQL text of
your queries.
 
Back
Top