|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 12th, 2007, 11:22 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Sum on totals by date with 1's and 0's
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(I If([Tempscore],1,0)))); 
|
|
#2
February 13th, 2007, 12:03 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Sum on totals by date with 1's and 0's
On Mon, 12 Feb 2007 15:22:00 -0800, efandango
wrote: 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]
|
|
#3
February 13th, 2007, 12:31 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Sum on totals by date with 1's and 0's
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" wrote: On Mon, 12 Feb 2007 15:22:00 -0800, efandango wrote: 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]
|
|
#4
February 13th, 2007, 12:42 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Sum on totals by date with 1's and 0's
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 efandango wrote: 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" wrote: On Mon, 12 Feb 2007 15:22:00 -0800, efandango wrote: 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(I If([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]
|
|
#5
February 13th, 2007, 01:18 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Sum on totals by date with 1's and 0's
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" wrote: 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 efandango wrote: 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" wrote: On Mon, 12 Feb 2007 15:22:00 -0800, efandango wrote: 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(I If([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]
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
