Sum on totals by date with 1's and 0's

E

efandango

My table looks like this:

TestDate Tempscore
23/01/2007 1
23/01/2007 0
26/01/2007 1
26/01/2007 1
29/01/2007 1
29/01/2007 1
01/02/2007 0
01/02/2007 1
04/02/2007 1
04/02/2007 1
04/02/2007 0
04/02/2007 1
07/02/2007 0
07/02/2007 0

I want to be able to get two sets of results (totals) for each date.

All those that are correct (1's), but not listing those that are incorrect
(0's)

So that I get:

TestDate Tempscore
23/01/2007 1
26/01/2007 2
29/01/2007 2
01/02/2007 1
04/02/2007 3


I have tried: (But with this SQL I also get the '0' scores listed.)

SELECT Tbl_Scores_Running_Totals.TestDate,
Sum(Tbl_Scores_Running_Totals.Tempscore) AS SumOfTempscore
FROM Tbl_Scores_Running_Totals
GROUP BY Tbl_Scores_Running_Totals.TestDate
HAVING
(((Sum(Tbl_Scores_Running_Totals.Tempscore))=Sum(IIf([Tempscore],1,0))));
 
J

John W. Vinson

My table looks like this:

TestDate Tempscore
23/01/2007 1
23/01/2007 0
26/01/2007 1
26/01/2007 1
29/01/2007 1
29/01/2007 1
01/02/2007 0
01/02/2007 1
04/02/2007 1
04/02/2007 1
04/02/2007 0
04/02/2007 1
07/02/2007 0
07/02/2007 0

I want to be able to get two sets of results (totals) for each date.

All those that are correct (1's), but not listing those that are incorrect
(0's)

So that I get:

TestDate Tempscore
23/01/2007 1
26/01/2007 2
29/01/2007 2
01/02/2007 1
04/02/2007 3


I have tried: (But with this SQL I also get the '0' scores listed.)

SELECT Tbl_Scores_Running_Totals.TestDate,
Sum(Tbl_Scores_Running_Totals.Tempscore) AS SumOfTempscore
FROM Tbl_Scores_Running_Totals
GROUP BY Tbl_Scores_Running_Totals.TestDate
HAVING
(((Sum(Tbl_Scores_Running_Totals.Tempscore))=Sum(IIf([Tempscore],1,0))));
Click to expand...

Ummmmmm... adding a zero doesn't affect the sum. Why not just add all
the zeros?

SELECT TestDate, Sum(Tempscore) AS SumOfTempscore
FROM tbl_Scores_Running_Totals
GROUP BY Testdate;

If you want to COUNT all records - correct and incorrect - and also
count the correct results, try

SELECT TestDate, Count(*) AS AllAnswers, Sum(Tempscore) As Correct
FROM tbl_Scores_Running_Totals
GROUP BY Testdate;

John W. Vinson [MVP]
 
E

efandango

John,

Thanks for your help. I want the answers seperated becuase I want to use the
data for charts and stats, your last SQL:

SELECT TestDate, Count(*) AS AllAnswers, Sum(Tempscore) As Correct
FROM tbl_Scores_Running_Totals
GROUP BY Testdate;

Is closest to my ideal; which is to have a total, Correct and wrong figures.
can this be done in the same query, I get an aggregate error when i try and
formulate the totals for the 'wrongs' totals in the same QBE.



John W. Vinson said:
My table looks like this:

TestDate Tempscore
23/01/2007 1
23/01/2007 0
26/01/2007 1
26/01/2007 1
29/01/2007 1
29/01/2007 1
01/02/2007 0
01/02/2007 1
04/02/2007 1
04/02/2007 1
04/02/2007 0
04/02/2007 1
07/02/2007 0
07/02/2007 0

I want to be able to get two sets of results (totals) for each date.

All those that are correct (1's), but not listing those that are incorrect
(0's)

So that I get:

TestDate Tempscore
23/01/2007 1
26/01/2007 2
29/01/2007 2
01/02/2007 1
04/02/2007 3


I have tried: (But with this SQL I also get the '0' scores listed.)

SELECT Tbl_Scores_Running_Totals.TestDate,
Sum(Tbl_Scores_Running_Totals.Tempscore) AS SumOfTempscore
FROM Tbl_Scores_Running_Totals
GROUP BY Tbl_Scores_Running_Totals.TestDate
HAVING
(((Sum(Tbl_Scores_Running_Totals.Tempscore))=Sum(IIf([Tempscore],1,0))));
Click to expand...

Ummmmmm... adding a zero doesn't affect the sum. Why not just add all
the zeros?

SELECT TestDate, Sum(Tempscore) AS SumOfTempscore
FROM tbl_Scores_Running_Totals
GROUP BY Testdate;

If you want to COUNT all records - correct and incorrect - and also
count the correct results, try

SELECT TestDate, Count(*) AS AllAnswers, Sum(Tempscore) As Correct
FROM tbl_Scores_Running_Totals
GROUP BY Testdate;

John W. Vinson [MVP]
Click to expand...
 
J

John Spencer

One method

SELECT TestDate
, Count(*) AS AllAnswers
, Sum(Tempscore) As Correct
, Abs(Sum(TempScore=0)) as Incorrect
FROM tbl_Scores_Running_Totals
GROUP BY Testdate;


---
John Spencer
Access MVP 2001-2005, 2007

John,

Thanks for your help. I want the answers seperated becuase I want to use the
data for charts and stats, your last SQL:

SELECT TestDate, Count(*) AS AllAnswers, Sum(Tempscore) As Correct
FROM tbl_Scores_Running_Totals
GROUP BY Testdate;

Is closest to my ideal; which is to have a total, Correct and wrong figures.
can this be done in the same query, I get an aggregate error when i try and
formulate the totals for the 'wrongs' totals in the same QBE.



John W. Vinson said:
My table looks like this:

TestDate Tempscore
23/01/2007 1
23/01/2007 0
26/01/2007 1
26/01/2007 1
29/01/2007 1
29/01/2007 1
01/02/2007 0
01/02/2007 1
04/02/2007 1
04/02/2007 1
04/02/2007 0
04/02/2007 1
07/02/2007 0
07/02/2007 0

I want to be able to get two sets of results (totals) for each date.

All those that are correct (1's), but not listing those that are incorrect
(0's)

So that I get:

TestDate Tempscore
23/01/2007 1
26/01/2007 2
29/01/2007 2
01/02/2007 1
04/02/2007 3


I have tried: (But with this SQL I also get the '0' scores listed.)

SELECT Tbl_Scores_Running_Totals.TestDate,
Sum(Tbl_Scores_Running_Totals.Tempscore) AS SumOfTempscore
FROM Tbl_Scores_Running_Totals
GROUP BY Tbl_Scores_Running_Totals.TestDate
HAVING
(((Sum(Tbl_Scores_Running_Totals.Tempscore))=Sum(IIf([Tempscore],1,0))));
Click to expand...
Ummmmmm... adding a zero doesn't affect the sum. Why not just add all
the zeros?

SELECT TestDate, Sum(Tempscore) AS SumOfTempscore
FROM tbl_Scores_Running_Totals
GROUP BY Testdate;

If you want to COUNT all records - correct and incorrect - and also
count the correct results, try

SELECT TestDate, Count(*) AS AllAnswers, Sum(Tempscore) As Correct
FROM tbl_Scores_Running_Totals
GROUP BY Testdate;

John W. Vinson [MVP]
Click to expand...
Click to expand...
 
E

efandango

John,

Thanks for that, just the ticket!.

though i have spotted one anomoly in one of the totals for a certain date,
i'm convinced it's where i have been playing with data between Access and
Excel; all the other numbers seem to stack up ok; so i'll let you know if its
more than an artifact from the process.

in the meantime, much appreciation.

kind regards

Eric

John Spencer said:
One method

SELECT TestDate
, Count(*) AS AllAnswers
, Sum(Tempscore) As Correct
, Abs(Sum(TempScore=0)) as Incorrect
FROM tbl_Scores_Running_Totals
GROUP BY Testdate;


---
John Spencer
Access MVP 2001-2005, 2007

John,

Thanks for your help. I want the answers seperated becuase I want to use the
data for charts and stats, your last SQL:

SELECT TestDate, Count(*) AS AllAnswers, Sum(Tempscore) As Correct
FROM tbl_Scores_Running_Totals
GROUP BY Testdate;

Is closest to my ideal; which is to have a total, Correct and wrong figures.
can this be done in the same query, I get an aggregate error when i try and
formulate the totals for the 'wrongs' totals in the same QBE.



John W. Vinson said:
On Mon, 12 Feb 2007 15:22:00 -0800, efandango

My table looks like this:

TestDate Tempscore
23/01/2007 1
23/01/2007 0
26/01/2007 1
26/01/2007 1
29/01/2007 1
29/01/2007 1
01/02/2007 0
01/02/2007 1
04/02/2007 1
04/02/2007 1
04/02/2007 0
04/02/2007 1
07/02/2007 0
07/02/2007 0

I want to be able to get two sets of results (totals) for each date.

All those that are correct (1's), but not listing those that are incorrect
(0's)

So that I get:

TestDate Tempscore
23/01/2007 1
26/01/2007 2
29/01/2007 2
01/02/2007 1
04/02/2007 3


I have tried: (But with this SQL I also get the '0' scores listed.)

SELECT Tbl_Scores_Running_Totals.TestDate,
Sum(Tbl_Scores_Running_Totals.Tempscore) AS SumOfTempscore
FROM Tbl_Scores_Running_Totals
GROUP BY Tbl_Scores_Running_Totals.TestDate
HAVING
(((Sum(Tbl_Scores_Running_Totals.Tempscore))=Sum(IIf([Tempscore],1,0))));
Ummmmmm... adding a zero doesn't affect the sum. Why not just add all
the zeros?

SELECT TestDate, Sum(Tempscore) AS SumOfTempscore
FROM tbl_Scores_Running_Totals
GROUP BY Testdate;

If you want to COUNT all records - correct and incorrect - and also
count the correct results, try

SELECT TestDate, Count(*) AS AllAnswers, Sum(Tempscore) As Correct
FROM tbl_Scores_Running_Totals
GROUP BY Testdate;

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 

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

How can I measure comparative progress across a range of dates? 4
count not-overlapping days in a "Table" with Beginning and EndingsDates Specified 0
Find then highlight in yellow 6
Selecting whole records while using a MaxOf function. 5
Advanced problem using auto-number and datediff to find readmissio 2
Query Help 0
Reverse Index Match 2
Crosstab Query based on Ordinal Values 6

Top