How can I measure comparative progress across a range of dates?

[Guest]

I want to build a query that will give a user some kind of indicator on how
he is doing over a number of weeks (maybe 52?)

What would be the best approach if i want to use a form & report for
displaying resuts?

This is my data: (which is in a query)

TestDate Correct Incorrect

13/02/2006 26 24
20/02/2006 35 15
27/02/2006 24 26
06/03/2006 28 22
13/03/2006 21 29
20/03/2006 25 25
27/03/2006 27 22
06/04/2006 21 29

Can a query work for this to drive a form and report?

My SQL:

SELECT tbl_Scores_Running_Totals.TestDate,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct, Abs(Sum(TempScore=0)) AS
Incorrect
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate
ORDER BY tbl_Scores_Running_Totals.TestDate DESC ,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC , Abs(Sum(TempScore=0)) DESC;

 

[kingston via AccessMonster.com]

Is this what you're looking to do in a query (add two calculated fields)?

RunningCorrect: DSum("Correct","Table","[TestDate]<=#" & [TestDate] "#")
RunningIncorrect: DSum("Incorrect","Table","[TestDate]<=#" & [TestDate] "#")
Include [TestDate] in the query output.

If not, provide a sample of what you'd like the output to be.

 

[Guest]

Hello Monster,

I tried to plug your sql in, but Access returned an error, highlighting the
2nd '#' as the problem in each Dsum formular. The error was:

operand without an operator

Is this what you're looking to do in a query (add two calculated fields)?

RunningCorrect: DSum("Correct","Table","[TestDate]<=#" & [TestDate] "#")
RunningIncorrect: DSum("Incorrect","Table","[TestDate]<=#" & [TestDate] "#")
Include [TestDate] in the query output.

If not, provide a sample of what you'd like the output to be.

I want to build a query that will give a user some kind of indicator on how
he is doing over a number of weeks (maybe 52?)

What would be the best approach if i want to use a form & report for
displaying resuts?

This is my data: (which is in a query)

TestDate Correct Incorrect

13/02/2006 26 24
20/02/2006 35 15
27/02/2006 24 26
06/03/2006 28 22
13/03/2006 21 29
20/03/2006 25 25
27/03/2006 27 22
06/04/2006 21 29

Can a query work for this to drive a form and report?

My SQL:

SELECT tbl_Scores_Running_Totals.TestDate,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct, Abs(Sum(TempScore=0)) AS
Incorrect
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate
ORDER BY tbl_Scores_Running_Totals.TestDate DESC ,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC , Abs(Sum(TempScore=0)) DESC;

 

[John Spencer]

Looks as if there was a missing "&" in the sample
RunningCorrect: DSum("Correct","Table","[TestDate]<=#" & [TestDate] & "#")


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello Monster,

I tried to plug your sql in, but Access returned an error, highlighting
the
2nd '#' as the problem in each Dsum formular. The error was:

operand without an operator

Is this what you're looking to do in a query (add two calculated fields)?

RunningCorrect: DSum("Correct","Table","[TestDate]<=#" & [TestDate] "#")
RunningIncorrect: DSum("Incorrect","Table","[TestDate]<=#" & [TestDate]
"#")
Include [TestDate] in the query output.

If not, provide a sample of what you'd like the output to be.

I want to build a query that will give a user some kind of indicator on
how
he is doing over a number of weeks (maybe 52?)

What would be the best approach if i want to use a form & report for
displaying resuts?

This is my data: (which is in a query)

TestDate Correct Incorrect

13/02/2006 26 24
20/02/2006 35 15
27/02/2006 24 26
06/03/2006 28 22
13/03/2006 21 29
20/03/2006 25 25
27/03/2006 27 22
06/04/2006 21 29

Can a query work for this to drive a form and report?

My SQL:

SELECT tbl_Scores_Running_Totals.TestDate,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct,
Abs(Sum(TempScore=0)) AS
Incorrect
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate
ORDER BY tbl_Scores_Running_Totals.TestDate DESC ,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC , Abs(Sum(TempScore=0))
DESC;

 

[Guest]

Hello Monster,

I have given it some more thought, and summarised it as this:

This week (total 7 Days)
Points Correct Points Incorrect Total Answered


Previous Week (total 7 Days)
Points Correct Points Incorrect Total Answered


Month Ago (total 4 weeks)
Points Correct Points Incorrect Total Answered


Best week to date: 03/05/06
Points Correct Points Incorrect Total Answered


Best Days: Tuesdays

Worst Days: Fridays


what do you think, can it be done, or is it too big a monster?...

Is this what you're looking to do in a query (add two calculated fields)?

RunningCorrect: DSum("Correct","Table","[TestDate]<=#" & [TestDate] "#")
RunningIncorrect: DSum("Incorrect","Table","[TestDate]<=#" & [TestDate] "#")
Include [TestDate] in the query output.

If not, provide a sample of what you'd like the output to be.

I want to build a query that will give a user some kind of indicator on how
he is doing over a number of weeks (maybe 52?)

What would be the best approach if i want to use a form & report for
displaying resuts?

This is my data: (which is in a query)

TestDate Correct Incorrect

13/02/2006 26 24
20/02/2006 35 15
27/02/2006 24 26
06/03/2006 28 22
13/03/2006 21 29
20/03/2006 25 25
27/03/2006 27 22
06/04/2006 21 29

Can a query work for this to drive a form and report?

My SQL:

SELECT tbl_Scores_Running_Totals.TestDate,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct, Abs(Sum(TempScore=0)) AS
Incorrect
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate
ORDER BY tbl_Scores_Running_Totals.TestDate DESC ,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC , Abs(Sum(TempScore=0)) DESC;