Apparent glitch with DSum in Query - begins with October, 2005

  • Thread starter Thread starter Gene Monroe
  • Start date Start date
G

Gene Monroe

Hi all! I think I may be experiencing a glitch. I've successfully created a
cumulative total function using DSum in a query, which is fed by a table from
a make-table query. I get accurate monthly totals and cumulative totals...up
until October, 2005 - when the cumulative totals begin to get out of whack.
I've tried running the database on other computers, exporting the table out
to excel and re-importing as a fresh table and re-writing the queries - no
luck. For 2003 through September, 2005 everything is fine, from then on -
the cumulative totals show no relationship to the monthly values from which
they're drawn. Anyone else aware of this glitch?
 
I've successfully created a cumulative total function using DSum in a
query, which is fed by a table from a make-table query.
No one can answer with looking at your 'cumulative total function.'

Care to share?
 
Sure, Karl! Here's the DSum string:

CLDays: DSum("LDays","LostDays","DatePart('m',[DOI By Month])<=" & [Month] &
" And DatePart('yyyy',[DOI By Month])<=" & [Year] & "")

Where:

LDays = Field (number)
LostDays = Table created summing LDays by month and sorting chronologically

The same thing is in there for LtDays
Here is what I get (look what happens beginning with Oct 2005)

CDays
Year Month LDays CLDays LtDays CLtDays
2004 10 65 65 19 19
2004 11 5 70 30 49
2004 12 0 70 77 126
2005 1 8 8 116 116
2005 2 10 18 197 313
2005 3 2 20 21 334
2005 4 4 24 54 388
2005 5 36 60 226 614
2005 6 1 61 0 614
2005 7 13 74 0 614
2005 8 182 256 200 814
2005 9 0 256 36 850
2005 10 18 339 75 944
2005 11 5 349 6 980
2005 12 69 418 132 1189
2006 1 49 57 42 158
2006 2 6 73 14 369
2006 3 8 83 37 427
2006 4 96 183 10 491
2006 5 44 263 22 739
2006 6 222 486 31 770
2006 7 3 502 35 805
2006 8 2 686 14 1019
2006 9 47 733 16 1071
2006 10 8 824 67 1232
2006 11 16 850 130 1398
2006 12 0 919 0 1607
2007 1 17 74 36 194
2007 2 0 90 14 419
2007 3 2 102 48 525
2007 4 33 235 33 622
2007 5 9 324 18 888
2007 6 11 558 106 1025
2007 7 198 772 121 1181
2007 8 14 970 2 1397
2007 9 29 1046 3 1452
2007 10 19 1156 74 1687
2007 11 33 1215 93 1946
2007 12 14 1298 58 2213
2008 1 8 82 20 214
2008 2 32 130 45 484
2008 3 4 146 1 591
2008 4 128 407 54 742
2008 5 35 531 54 1062
2008 6 6 771 9 1208
2008 7 7 992 14 1378
2008 8 8 1198 99 1693
 
It does not work at all for me, I get the same results for each month and
year no accumulation.
--
KARL DEWEY
Build a little - Test a little


Gene Monroe said:
Sure, Karl! Here's the DSum string:

CLDays: DSum("LDays","LostDays","DatePart('m',[DOI By Month])<=" & [Month] &
" And DatePart('yyyy',[DOI By Month])<=" & [Year] & "")

Where:

LDays = Field (number)
LostDays = Table created summing LDays by month and sorting chronologically

The same thing is in there for LtDays
Here is what I get (look what happens beginning with Oct 2005)

CDays
Year Month LDays CLDays LtDays CLtDays
2004 10 65 65 19 19
2004 11 5 70 30 49
2004 12 0 70 77 126
2005 1 8 8 116 116
2005 2 10 18 197 313
2005 3 2 20 21 334
2005 4 4 24 54 388
2005 5 36 60 226 614
2005 6 1 61 0 614
2005 7 13 74 0 614
2005 8 182 256 200 814
2005 9 0 256 36 850
2005 10 18 339 75 944
2005 11 5 349 6 980
2005 12 69 418 132 1189
2006 1 49 57 42 158
2006 2 6 73 14 369
2006 3 8 83 37 427
2006 4 96 183 10 491
2006 5 44 263 22 739
2006 6 222 486 31 770
2006 7 3 502 35 805
2006 8 2 686 14 1019
2006 9 47 733 16 1071
2006 10 8 824 67 1232
2006 11 16 850 130 1398
2006 12 0 919 0 1607
2007 1 17 74 36 194
2007 2 0 90 14 419
2007 3 2 102 48 525
2007 4 33 235 33 622
2007 5 9 324 18 888
2007 6 11 558 106 1025
2007 7 198 772 121 1181
2007 8 14 970 2 1397
2007 9 29 1046 3 1452
2007 10 19 1156 74 1687
2007 11 33 1215 93 1946
2007 12 14 1298 58 2213
2008 1 8 82 20 214
2008 2 32 130 45 484
2008 3 4 146 1 591
2008 4 128 407 54 742
2008 5 35 531 54 1062
2008 6 6 771 9 1208
2008 7 7 992 14 1378
2008 8 8 1198 99 1693




KARL DEWEY said:
query, which is fed by a table from a make-table query.
No one can answer with looking at your 'cumulative total function.'

Care to share?
Click to expand...
Click to expand...
 
IF you'd like a better look, send me an email addy and I'll zip the file and
send it to ya.

I will need to remove some information from the table, for privacy reasons,
but none of the fields concerned would be removed.

Thanks for the help.

-Gene

KARL DEWEY said:
It does not work at all for me, I get the same results for each month and
year no accumulation.
--
KARL DEWEY
Build a little - Test a little


Gene Monroe said:
Sure, Karl! Here's the DSum string:

CLDays: DSum("LDays","LostDays","DatePart('m',[DOI By Month])<=" & [Month] &
" And DatePart('yyyy',[DOI By Month])<=" & [Year] & "")

Where:

LDays = Field (number)
LostDays = Table created summing LDays by month and sorting chronologically

The same thing is in there for LtDays
Here is what I get (look what happens beginning with Oct 2005)

CDays
Year Month LDays CLDays LtDays CLtDays
2004 10 65 65 19 19
2004 11 5 70 30 49
2004 12 0 70 77 126
2005 1 8 8 116 116
2005 2 10 18 197 313
2005 3 2 20 21 334
2005 4 4 24 54 388
2005 5 36 60 226 614
2005 6 1 61 0 614
2005 7 13 74 0 614
2005 8 182 256 200 814
2005 9 0 256 36 850
2005 10 18 339 75 944
2005 11 5 349 6 980
2005 12 69 418 132 1189
2006 1 49 57 42 158
2006 2 6 73 14 369
2006 3 8 83 37 427
2006 4 96 183 10 491
2006 5 44 263 22 739
2006 6 222 486 31 770
2006 7 3 502 35 805
2006 8 2 686 14 1019
2006 9 47 733 16 1071
2006 10 8 824 67 1232
2006 11 16 850 130 1398
2006 12 0 919 0 1607
2007 1 17 74 36 194
2007 2 0 90 14 419
2007 3 2 102 48 525
2007 4 33 235 33 622
2007 5 9 324 18 888
2007 6 11 558 106 1025
2007 7 198 772 121 1181
2007 8 14 970 2 1397
2007 9 29 1046 3 1452
2007 10 19 1156 74 1687
2007 11 33 1215 93 1946
2007 12 14 1298 58 2213
2008 1 8 82 20 214
2008 2 32 130 45 484
2008 3 4 146 1 591
2008 4 128 407 54 742
2008 5 35 531 54 1062
2008 6 6 771 9 1208
2008 7 7 992 14 1378
2008 8 8 1198 99 1693




KARL DEWEY said:
I've successfully created a cumulative total function using DSum in a
query, which is fed by a table from a make-table query.
No one can answer with looking at your 'cumulative total function.'

Care to share?
--
KARL DEWEY
Build a little - Test a little


:

Hi all! I think I may be experiencing a glitch. I've successfully created a
cumulative total function using DSum in a query, which is fed by a table from
a make-table query. I get accurate monthly totals and cumulative totals...up
until October, 2005 - when the cumulative totals begin to get out of whack.
I've tried running the database on other computers, exporting the table out
to excel and re-importing as a fresh table and re-writing the queries - no
luck. For 2003 through September, 2005 everything is fine, from then on -
the cumulative totals show no relationship to the monthly values from which
they're drawn. Anyone else aware of this glitch?
Click to expand...
Click to expand...
Click to expand...
 
No. Maybe someone else can help.
--
KARL DEWEY
Build a little - Test a little


Gene Monroe said:
IF you'd like a better look, send me an email addy and I'll zip the file and
send it to ya.

I will need to remove some information from the table, for privacy reasons,
but none of the fields concerned would be removed.

Thanks for the help.

-Gene

KARL DEWEY said:
It does not work at all for me, I get the same results for each month and
year no accumulation.
--
KARL DEWEY
Build a little - Test a little


Gene Monroe said:
Sure, Karl! Here's the DSum string:

CLDays: DSum("LDays","LostDays","DatePart('m',[DOI By Month])<=" & [Month] &
" And DatePart('yyyy',[DOI By Month])<=" & [Year] & "")

Where:

LDays = Field (number)
LostDays = Table created summing LDays by month and sorting chronologically

The same thing is in there for LtDays
Here is what I get (look what happens beginning with Oct 2005)

CDays
Year Month LDays CLDays LtDays CLtDays
2004 10 65 65 19 19
2004 11 5 70 30 49
2004 12 0 70 77 126
2005 1 8 8 116 116
2005 2 10 18 197 313
2005 3 2 20 21 334
2005 4 4 24 54 388
2005 5 36 60 226 614
2005 6 1 61 0 614
2005 7 13 74 0 614
2005 8 182 256 200 814
2005 9 0 256 36 850
2005 10 18 339 75 944
2005 11 5 349 6 980
2005 12 69 418 132 1189
2006 1 49 57 42 158
2006 2 6 73 14 369
2006 3 8 83 37 427
2006 4 96 183 10 491
2006 5 44 263 22 739
2006 6 222 486 31 770
2006 7 3 502 35 805
2006 8 2 686 14 1019
2006 9 47 733 16 1071
2006 10 8 824 67 1232
2006 11 16 850 130 1398
2006 12 0 919 0 1607
2007 1 17 74 36 194
2007 2 0 90 14 419
2007 3 2 102 48 525
2007 4 33 235 33 622
2007 5 9 324 18 888
2007 6 11 558 106 1025
2007 7 198 772 121 1181
2007 8 14 970 2 1397
2007 9 29 1046 3 1452
2007 10 19 1156 74 1687
2007 11 33 1215 93 1946
2007 12 14 1298 58 2213
2008 1 8 82 20 214
2008 2 32 130 45 484
2008 3 4 146 1 591
2008 4 128 407 54 742
2008 5 35 531 54 1062
2008 6 6 771 9 1208
2008 7 7 992 14 1378
2008 8 8 1198 99 1693




:

I've successfully created a cumulative total function using DSum in a
query, which is fed by a table from a make-table query.
No one can answer with looking at your 'cumulative total function.'

Care to share?
--
KARL DEWEY
Build a little - Test a little


:

Hi all! I think I may be experiencing a glitch. I've successfully created a
cumulative total function using DSum in a query, which is fed by a table from
a make-table query. I get accurate monthly totals and cumulative totals...up
until October, 2005 - when the cumulative totals begin to get out of whack.
I've tried running the database on other computers, exporting the table out
to excel and re-importing as a fresh table and re-writing the queries - no
luck. For 2003 through September, 2005 everything is fine, from then on -
the cumulative totals show no relationship to the monthly values from which
they're drawn. Anyone else aware of this glitch?
Click to expand...
Click to expand...
Click to expand...
 
can you post the query for lostdays and the query for the above the
full sql and the raw data without the calculations applied so i can
build it and have a sqiz

Regards
Kelvan
 
Most often cause of problems like this is years in the wrong century,
like 2504, cause by typing month or day into the year field, hidden
by short-date display.

(david)
 
Back
Top