Why is my Running Total not working?

G

Guest

Can someone explain why I don't get a running total in my 'RunTot' Column?

Run_No Quiz_ID Venue_A Address_A answer SumOfTempscore RunTot

1 23367 Library Main St Yes 2 6
3 23404 Hospital South St Yes 1 6
7 23485 Bus Stn High St Yes 1 6


I thought I would get
2
3
4

instead of
6
6
6

Below is is is my SQL (I cut the names slightly above to make the query
format work)


SELECT tbl_FinalPointsTest_A.Run_No, tbl_FinalPointsTest_A.Point_Quiz_ID,
tbl_FinalPointsTest_A.Run_point_Venue_A,
tbl_FinalPointsTest_A.Run_point_Address_A, tbl_FinalPointsTest_A.answer_box,
Sum(tbl_FinalPointsTest_A.Tempscore) AS SumOfTempscore,
Format(DSum("Tempscore","tbl_FinalPointsTest_A"),0,0) AS RunTot
FROM tbl_FinalPointsTest_A
GROUP BY tbl_FinalPointsTest_A.Run_No, tbl_FinalPointsTest_A.Point_Quiz_ID,
tbl_FinalPointsTest_A.Run_point_Venue_A,
tbl_FinalPointsTest_A.Run_point_Address_A, tbl_FinalPointsTest_A.answer_box,
Format(DSum("Tempscore","tbl_FinalPointsTest_A"),0,0);
 
G

Gary Walter

I may misunderstand your data,
but my best guess is you wanted:

SELECT
t.Run_No,
t.Point_Quiz_ID,
t.Run_point_Venue_A,
t.Run_point_Address_A,
t.answer_box,
Sum(t.Tempscore) AS SumOfTempscore,
DSum("Tempscore","tbl_FinalPointsTest_A","[Run_No]<=" & t.RunNo) AS RunTot
FROM
tbl_FinalPointsTest_A As t
GROUP BY
t.Run_No,
t.Point_Quiz_ID,
t.Run_point_Venue_A,
t.Run_point_Address_A,
t.answer_box,
DSum("Tempscore","tbl_FinalPointsTest_A","[Run_No]<=" & t.RunNo);
 
G

Guest

Gary,

Thanks for the feedback, Do I just use this as an SQL statement, or place
the various Sum & Dsum into my query designer grid. What do the various 't.'
mean, are they shorthand for the table, or Total?.

Also, If you look at my original table how can I seperate out the Yes & No's
into 2 new columns, so If it's a yes, then it gets a 1, if No, then it gets a
0.



Gary Walter said:
I may misunderstand your data,
but my best guess is you wanted:

SELECT
t.Run_No,
t.Point_Quiz_ID,
t.Run_point_Venue_A,
t.Run_point_Address_A,
t.answer_box,
Sum(t.Tempscore) AS SumOfTempscore,
DSum("Tempscore","tbl_FinalPointsTest_A","[Run_No]<=" & t.RunNo) AS RunTot
FROM
tbl_FinalPointsTest_A As t
GROUP BY
t.Run_No,
t.Point_Quiz_ID,
t.Run_point_Venue_A,
t.Run_point_Address_A,
t.answer_box,
DSum("Tempscore","tbl_FinalPointsTest_A","[Run_No]<=" & t.RunNo);

efandango said:
Can someone explain why I don't get a running total in my 'RunTot' Column?

Run_No Quiz_ID Venue_A Address_A answer SumOfTempscore RunTot

1 23367 Library Main St Yes 2 6
3 23404 Hospital South St Yes 1 6
7 23485 Bus Stn High St Yes 1 6


I thought I would get
2
3
4

instead of
6
6
6

Below is is is my SQL (I cut the names slightly above to make the query
format work)


SELECT tbl_FinalPointsTest_A.Run_No, tbl_FinalPointsTest_A.Point_Quiz_ID,
tbl_FinalPointsTest_A.Run_point_Venue_A,
tbl_FinalPointsTest_A.Run_point_Address_A,
tbl_FinalPointsTest_A.answer_box,
Sum(tbl_FinalPointsTest_A.Tempscore) AS SumOfTempscore,
Format(DSum("Tempscore","tbl_FinalPointsTest_A"),0,0) AS RunTot
FROM tbl_FinalPointsTest_A
GROUP BY tbl_FinalPointsTest_A.Run_No,
tbl_FinalPointsTest_A.Point_Quiz_ID,
tbl_FinalPointsTest_A.Run_point_Venue_A,
tbl_FinalPointsTest_A.Run_point_Address_A,
tbl_FinalPointsTest_A.answer_box,
Format(DSum("Tempscore","tbl_FinalPointsTest_A"),0,0);
Click to expand...
Click to expand...
 
G

Gary Walter

comments inline:

efandango said:
Do I just use this as an SQL statement, or place
the various Sum & Dsum into my query designer grid.
Click to expand...

I would start a new query,
go to "SQL View,"
copy and paste the (guess) SQL there,
then see it works for you...
What do the various 't.'
mean, are they shorthand for the table, or Total?.
Click to expand...

"t" is just an alias for your table

FROM
tbl_FinalPointsTest_A As t

it makes it easier for me to type out...
Also, If you look at my original table how can I seperate out the Yes &
No's
into 2 new columns, so If it's a yes, then it gets a 1, if No, then it
gets a 0.
Click to expand...

I'm a little confused here....
is answer_box
1) type Text
contains either text "Yes" or "No"
(and no other posibilities)

or

2) type Yes/No
now formatted as "Yes/No"

if there are some Yes and some No
for a "Run_No group", then you should be
getting a row for "Yes" and a row for "No"
for each Run_No/Quiz/Venue/Address group.

I think what you are suggesting will still
provide 2 rows for each "group."

I wonder if you don't want the count of
"Yes" and count of "No" in 2 separate columns?

If (1) is true above, then maybe you want:

SELECT
t.Run_No,
t.Point_Quiz_ID,
t.Run_point_Venue_A,
t.Run_point_Address_A,
Abs(Count(t.answer_box="Yes")) As YesCnt,
Abs(Count(t.answer_box="No")) As NoCnt,
Sum(t.Tempscore) AS SumOfTempscore,
DSum("Tempscore","tbl_FinalPointsTest_A","[Run_No]<=" & t.RunNo) AS RunTot
FROM
tbl_FinalPointsTest_A As t
GROUP BY
t.Run_No,
t.Point_Quiz_ID,
t.Run_point_Venue_A,
t.Run_point_Address_A;

I above query, every row will be a distinct
Run_No/Quiz/Venue/Address group
(you won't get 2 rows for each group).
But....[SumOfTempscore] will be for this
full group -- you will no longer get seperate sums
for "Yes" groups and "No" groups (in 2 rows).
Gary Walter said:
I may misunderstand your data,
but my best guess is you wanted:

SELECT
t.Run_No,
t.Point_Quiz_ID,
t.Run_point_Venue_A,
t.Run_point_Address_A,
t.answer_box,
Sum(t.Tempscore) AS SumOfTempscore,
DSum("Tempscore","tbl_FinalPointsTest_A","[Run_No]<=" & t.RunNo) AS
RunTot
FROM
tbl_FinalPointsTest_A As t
GROUP BY
t.Run_No,
t.Point_Quiz_ID,
t.Run_point_Venue_A,
t.Run_point_Address_A,
t.answer_box,
DSum("Tempscore","tbl_FinalPointsTest_A","[Run_No]<=" & t.RunNo);

efandango said:
Can someone explain why I don't get a running total in my 'RunTot'
Column?

Run_No Quiz_ID Venue_A Address_A answer SumOfTempscore RunTot

1 23367 Library Main St Yes 2 6
3 23404 Hospital South St Yes 1 6
7 23485 Bus Stn High St Yes 1 6


I thought I would get
2
3
4

instead of
6
6
6

Below is is is my SQL (I cut the names slightly above to make the query
format work)


SELECT tbl_FinalPointsTest_A.Run_No,
tbl_FinalPointsTest_A.Point_Quiz_ID,
tbl_FinalPointsTest_A.Run_point_Venue_A,
tbl_FinalPointsTest_A.Run_point_Address_A,
tbl_FinalPointsTest_A.answer_box,
Sum(tbl_FinalPointsTest_A.Tempscore) AS SumOfTempscore,
Format(DSum("Tempscore","tbl_FinalPointsTest_A"),0,0) AS RunTot
FROM tbl_FinalPointsTest_A
GROUP BY tbl_FinalPointsTest_A.Run_No,
tbl_FinalPointsTest_A.Point_Quiz_ID,
tbl_FinalPointsTest_A.Run_point_Venue_A,
tbl_FinalPointsTest_A.Run_point_Address_A,
tbl_FinalPointsTest_A.answer_box,
Format(DSum("Tempscore","tbl_FinalPointsTest_A"),0,0);
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

gary thanks for your help on this, it's not quite solving my problem, but i'm
getting on the right track with your direction.

To answer the Yes/No question. The field is a 'Text' field and not a formal
'Yes/No'
-1/0 field. and no there are no other posibilities because the field is set
by a SetValue macro that responds to if the user is correct or not on the
quiz line. The user selects an address, if it matches the venue, then the
[answer_box] field gets a "Yes", if they're wrong, it gets a "No". That's the
background to it. I am trying to build a scoring system where I can get the
results for all the times and references to the addresses that they got right.


Gary Walter said:
comments inline:

efandango said:
Do I just use this as an SQL statement, or place
the various Sum & Dsum into my query designer grid.
Click to expand...

I would start a new query,
go to "SQL View,"
copy and paste the (guess) SQL there,
then see it works for you...
What do the various 't.'
mean, are they shorthand for the table, or Total?.
Click to expand...

"t" is just an alias for your table

FROM
tbl_FinalPointsTest_A As t

it makes it easier for me to type out...
Also, If you look at my original table how can I seperate out the Yes &
No's
into 2 new columns, so If it's a yes, then it gets a 1, if No, then it
gets a 0.
Click to expand...

I'm a little confused here....
is answer_box
1) type Text
contains either text "Yes" or "No"
(and no other posibilities)

or

2) type Yes/No
now formatted as "Yes/No"

if there are some Yes and some No
for a "Run_No group", then you should be
getting a row for "Yes" and a row for "No"
for each Run_No/Quiz/Venue/Address group.

I think what you are suggesting will still
provide 2 rows for each "group."

I wonder if you don't want the count of
"Yes" and count of "No" in 2 separate columns?

If (1) is true above, then maybe you want:

SELECT
t.Run_No,
t.Point_Quiz_ID,
t.Run_point_Venue_A,
t.Run_point_Address_A,
Abs(Count(t.answer_box="Yes")) As YesCnt,
Abs(Count(t.answer_box="No")) As NoCnt,
Sum(t.Tempscore) AS SumOfTempscore,
DSum("Tempscore","tbl_FinalPointsTest_A","[Run_No]<=" & t.RunNo) AS RunTot
FROM
tbl_FinalPointsTest_A As t
GROUP BY
t.Run_No,
t.Point_Quiz_ID,
t.Run_point_Venue_A,
t.Run_point_Address_A;

I above query, every row will be a distinct
Run_No/Quiz/Venue/Address group
(you won't get 2 rows for each group).
But....[SumOfTempscore] will be for this
full group -- you will no longer get seperate sums
for "Yes" groups and "No" groups (in 2 rows).
Gary Walter said:
I may misunderstand your data,
but my best guess is you wanted:

SELECT
t.Run_No,
t.Point_Quiz_ID,
t.Run_point_Venue_A,
t.Run_point_Address_A,
t.answer_box,
Sum(t.Tempscore) AS SumOfTempscore,
DSum("Tempscore","tbl_FinalPointsTest_A","[Run_No]<=" & t.RunNo) AS
RunTot
FROM
tbl_FinalPointsTest_A As t
GROUP BY
t.Run_No,
t.Point_Quiz_ID,
t.Run_point_Venue_A,
t.Run_point_Address_A,
t.answer_box,
DSum("Tempscore","tbl_FinalPointsTest_A","[Run_No]<=" & t.RunNo);

:
Can someone explain why I don't get a running total in my 'RunTot'
Column?

Run_No Quiz_ID Venue_A Address_A answer SumOfTempscore RunTot

1 23367 Library Main St Yes 2 6
3 23404 Hospital South St Yes 1 6
7 23485 Bus Stn High St Yes 1 6


I thought I would get
2
3
4

instead of
6
6
6

Below is is is my SQL (I cut the names slightly above to make the query
format work)


SELECT tbl_FinalPointsTest_A.Run_No,
tbl_FinalPointsTest_A.Point_Quiz_ID,
tbl_FinalPointsTest_A.Run_point_Venue_A,
tbl_FinalPointsTest_A.Run_point_Address_A,
tbl_FinalPointsTest_A.answer_box,
Sum(tbl_FinalPointsTest_A.Tempscore) AS SumOfTempscore,
Format(DSum("Tempscore","tbl_FinalPointsTest_A"),0,0) AS RunTot
FROM tbl_FinalPointsTest_A
GROUP BY tbl_FinalPointsTest_A.Run_No,
tbl_FinalPointsTest_A.Point_Quiz_ID,
tbl_FinalPointsTest_A.Run_point_Venue_A,
tbl_FinalPointsTest_A.Run_point_Address_A,
tbl_FinalPointsTest_A.answer_box,
Format(DSum("Tempscore","tbl_FinalPointsTest_A"),0,0);
Click to expand...
Click to expand...
Click to expand...
 
Top