How could one out of 10 not add correctly?

  • Thread starter Thread starter billN
  • Start date Start date
B

billN

The report has name, ready time, start time. The last
column called "wasted time" has
=[start time]-[ready time]
and this works fine.
the report is grouped by name (10 names) with the total of
wasted time by name with this formula
=Sum([start time]-[ready time].
here is the problem: when I run the report by just Oct or
Oct and Nov all totals are correct. When I add a Dec one
name in particuliar totals incorrectly which also throws
off the grand total. At first I thought that there was a
data imput problem with Dec. But even when I choose Sep,
Oct, Nov the same name totals incorrectly. As long as I
choose only two months all totals are correct.
Stumped?
 
Dear Stumped.
In order to give you any feedback we may need to know
aliitle more about your table structure and/or query
structure and possible relationships.
Fons
 
As a guess the one that totals incorrectly exceeds 24 hours when you are using
the longer periods.

You are trying to measure and manipulate a point in time vice a duration of
time. This is the root of the problem.

Try using the DateDiff function to get the number of minutes involved.

DateDiff("n",[Start time],[Ready Time])

Once you have minutes you can do math operations on it and return a number of
hours and minutes.
 
Thanks for your reply.
It is a straight forward single database created with
about 60 fields, no other tables linked. The Query is also
straight forward, just a listing of patients along with
some demographic information grouped by MD. The only
criteria is the date range.
The time fields I am trying to add are formatted to "Short
time"
As long as I only use two months as a criteria all totals
are correct. When I add that extra month just one MD's
total is incorrect.
-----Original Message-----
Dear Stumped.
In order to give you any feedback we may need to know
aliitle more about your table structure and/or query
structure and possible relationships.
Fons
-----Original Message-----
The report has name, ready time, start time. The last
column called "wasted time" has
=[start time]-[ready time]
and this works fine.
the report is grouped by name (10 names) with the total of
wasted time by name with this formula
=Sum([start time]-[ready time].
here is the problem: when I run the report by just Oct or
Oct and Nov all totals are correct. When I add a Dec one
name in particuliar totals incorrectly which also throws
off the grand total. At first I thought that there was a
data imput problem with Dec. But even when I choose Sep,
Oct, Nov the same name totals incorrectly. As long as I
choose only two months all totals are correct.
Stumped?
.
Click to expand...
.
Click to expand...
 
Thanks for you reply.
I tried the DateDiff formula in the detail section and it
gave me only zeros.
There are 10 different totals in the report, some do
exceed 24 hours. It is only that one MD's group totals,
the individual amounts are correct it is the total of the
individual amounts , just for one MD group,that are not
correct.
The time fields are formatted to "Short Time"
-----Original Message-----
As a guess the one that totals incorrectly exceeds 24 hours when you are using
the longer periods.

You are trying to measure and manipulate a point in time vice a duration of
time. This is the root of the problem.

Try using the DateDiff function to get the number of minutes involved.

DateDiff("n",[Start time],[Ready Time])

Once you have minutes you can do math operations on it and return a number of
hours and minutes.
The report has name, ready time, start time. The last
column called "wasted time" has
=[start time]-[ready time]
and this works fine.
the report is grouped by name (10 names) with the total of
wasted time by name with this formula
=Sum([start time]-[ready time].
here is the problem: when I run the report by just Oct or
Oct and Nov all totals are correct. When I add a Dec one
name in particuliar totals incorrectly which also throws
off the grand total. At first I thought that there was a
data imput problem with Dec. But even when I choose Sep,
Oct, Nov the same name totals incorrectly. As long as I
choose only two months all totals are correct.
Stumped?
Click to expand...
.
Click to expand...
 
I don't believe it is the formula that is the problem.
there seems to be a limit to the number of formulas Access
will sum. It will add correctly up to 86 records and when
I add in the 87th it gives me a wrong answer. If I delete
the 87th out I get the right answer.
When I first ran the report from 10/1 to 12/31 one
particuliar MD's group total was incorrect. when I ran the
report from 10/1 to 12/16 the MD's total was correct. I
deleted all the MD's entries from 12/16 to 12/31 then
started to reenter one by one running the report each time
and on the 87th entry the total went wrong.
Is there any solution?
-----Original Message-----
Thanks for your reply.
It is a straight forward single database created with
about 60 fields, no other tables linked. The Query is also
straight forward, just a listing of patients along with
some demographic information grouped by MD. The only
criteria is the date range.
The time fields I am trying to add are formatted to "Short
time"
As long as I only use two months as a criteria all totals
are correct. When I add that extra month just one MD's
total is incorrect.
-----Original Message-----
Dear Stumped.
In order to give you any feedback we may need to know
aliitle more about your table structure and/or query
structure and possible relationships.
Fons
-----Original Message-----
The report has name, ready time, start time. The last
column called "wasted time" has
=[start time]-[ready time]
and this works fine.
the report is grouped by name (10 names) with the total of
wasted time by name with this formula
=Sum([start time]-[ready time].
here is the problem: when I run the report by just Oct or
Oct and Nov all totals are correct. When I add a Dec one
name in particuliar totals incorrectly which also throws
off the grand total. At first I thought that there was a
data imput problem with Dec. But even when I choose Sep,
Oct, Nov the same name totals incorrectly. As long as I
choose only two months all totals are correct.
Stumped?
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
I don't believe it is the formula that is the problem.
there seems to be a limit to the number of formulas Access
will sum. It will add correctly up to 86 records and when
I add in the 87th it gives me a wrong answer. If I delete
the 87th out I get the right answer.
When I first ran the report from 10/1 to 12/31 one
particuliar MD's group total was incorrect. when I ran the
report from 10/1 to 12/16 the MD's total was correct. I
deleted all the MD's entries from 12/16 to 12/31 then
started to reenter one by one running the report each time
and on the 87th entry the total went wrong.
Is there any solution?
-----Original Message-----
Thanks for your reply.
It is a straight forward single database created with
about 60 fields, no other tables linked. The Query is also
straight forward, just a listing of patients along with
some demographic information grouped by MD. The only
criteria is the date range.
The time fields I am trying to add are formatted to "Short
time"
As long as I only use two months as a criteria all totals
are correct. When I add that extra month just one MD's
total is incorrect.
-----Original Message-----
Dear Stumped.
In order to give you any feedback we may need to know
aliitle more about your table structure and/or query
structure and possible relationships.
Fons
-----Original Message-----
The report has name, ready time, start time. The last
column called "wasted time" has
=[start time]-[ready time]
and this works fine.
the report is grouped by name (10 names) with the total of
wasted time by name with this formula
=Sum([start time]-[ready time].
here is the problem: when I run the report by just Oct or
Oct and Nov all totals are correct. When I add a Dec one
name in particuliar totals incorrectly which also throws
off the grand total. At first I thought that there was a
data imput problem with Dec. But even when I choose Sep,
Oct, Nov the same name totals incorrectly. As long as I
choose only two months all totals are correct.
Stumped?
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top