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

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?
 
K

KARL DEWEY

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?
 
G

Gene Monroe

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
 
K

KARL DEWEY

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...
 
G

Gene Monroe

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...
 
K

KARL DEWEY

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...
 
L

Lord Kelvan

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
 
D

david

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting YES/NO in a Table with a Query 5
DSum in Query 3
Cumulative counts in a query 2
commission query 1
SUM in a UNION query 2
Cumulative Total in a Query 1
Crosstab Query - Recalculate totals after each date 2
Access 2003 Query: Dsum function Help 1

Top