Getting a total control to find totals for last 7 days via a query

[Guest]

I have an unbound control on a form that is doing a total on a query field
called [correct], I want to set the criteria from the control box and not the
query.

my control form looks like this:

Bound Control:
[Correct]

Unbound Control:
=Sum([Correct])

Validation Rule:
=DLookUp([TestDate],[Qry_Weekly_Points Stats_
percents]![TestDate],[TestDate]>=Date()-7)

But this returns the totals for All dates, and not the last 7 days, but if i
put the: >=Date()-7 in the underlying query's criteria for [TestDate], it
works fine.

What am I doing wrong?

 

[Marshall Barton]

I have an unbound control on a form that is doing a total on a query field
called [correct], I want to set the criteria from the control box and not the
query.

my control form looks like this:

Bound Control:
[Correct]

Unbound Control:
=Sum([Correct])

Validation Rule:
=DLookUp([TestDate],[Qry_Weekly_Points Stats_
percents]![TestDate],[TestDate]>=Date()-7)

But this returns the totals for All dates, and not the last 7 days, but if i
put the: >=Date()-7 in the underlying query's criteria for [TestDate], it
works fine.

What am I doing wrong?

A Validation Rule limits the values that users can enter
into a cantrol. It has nothing to do with calculations.

If the form's record source query is
Qry_Weekly_Points Stats_percents, then I think maybe this
might be what you're looking for:
=Sum(IIf(TestDate >= Date()-7, Correct, 0)

 

[Guest]

Marshall,

Thanks for that, it worked a treat!, this means I can use other (copies)
unbound controls on the same form to query for different date ranges, right?

this is ultimatelt what I want to do: (its pasted from another post that I
made earlier, but as you seemed to have answered my fundamental question,
perhaps its appropriate to re-paste here.


********************
I want to create an all-purpose (dashboard) form that will give me the
information in
the Form example below. Can someone give me some ideas/pointers as to the
best way of achieving this, what would be the best approach. Should I use:

Single form (can it have multiple queries?)
Various subforms (with separate queries?)

Can I use one master query to serve these various requirements, if so, would
I put the criteria, etc on the form itself? If so, where would that go?

I think I can deal with the criteria for A, B C. but what would I use for D,
E, F.?



The Form: (the A,B,C’s are just for reference)

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

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

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

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

E. Best Days: Tuesdays

F. Worst Days: Fridays




Sample Data (it extends back to 12 months)

TestDate AllAnswers Correct Wrong P% Correct P%Wrong
01/10/2006 13 7 6 53.85% 46.15%
02/10/2006 13 7 6 53.85% 46.15%
03/10/2006 16 6 10 37.50% 62.50%
04/10/2006 16 14 2 87.50% 12.50%


This is my SQL:

SELECT tbl_Scores_Running_Totals.TestDate, Count(*) AS AllAnswers,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct, Abs(Sum([TempScore]=0))
AS Wrong, [Correct]/[AllAnswers] AS PercentCorrect, [Wrong]/[AllAnswers] AS
PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate
ORDER BY tbl_Scores_Running_Totals.TestDate DESC , Count(*) DESC ,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC , Abs(Sum([TempScore]=0)) DESC;


I want to ideally cut down on the number of queries/forms (my db is
suffering from objects density, and its getting messier by the day…)

I have an unbound control on a form that is doing a total on a query field
called [correct], I want to set the criteria from the control box and not the
query.

my control form looks like this:

Bound Control:
[Correct]

Unbound Control:
=Sum([Correct])

Validation Rule:
=DLookUp([TestDate],[Qry_Weekly_Points Stats_
percents]![TestDate],[TestDate]>=Date()-7)

But this returns the totals for All dates, and not the last 7 days, but if i
put the: >=Date()-7 in the underlying query's criteria for [TestDate], it
works fine.

What am I doing wrong?

A Validation Rule limits the values that users can enter
into a cantrol. It has nothing to do with calculations.

If the form's record source query is
Qry_Weekly_Points Stats_percents, then I think maybe this
might be what you're looking for:
=Sum(IIf(TestDate >= Date()-7, Correct, 0)

 

[Marshall Barton]

Yes. A, B and C can be done with the same kind of
expression.

For D, E and F type of calculations, I think you probably
will need other queries that group on the week, month or day
of week.
--
Marsh
MVP [MS Access]

Thanks for that, it worked a treat!, this means I can use other (copies)
unbound controls on the same form to query for different date ranges, right?

this is ultimatelt what I want to do: (its pasted from another post that I
made earlier, but as you seemed to have answered my fundamental question,
perhaps its appropriate to re-paste here.
********************
I want to create an all-purpose (dashboard) form that will give me the
information in
the Form example below. Can someone give me some ideas/pointers as to the
best way of achieving this, what would be the best approach. Should I use:

Single form (can it have multiple queries?)
Various subforms (with separate queries?)

Can I use one master query to serve these various requirements, if so, would
I put the criteria, etc on the form itself? If so, where would that go?

I think I can deal with the criteria for A, B C. but what would I use for D,
E, F.?

The Form: (the A,B,C’s are just for reference)

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

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

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

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

E. Best Days: Tuesdays

F. Worst Days: Fridays

Sample Data (it extends back to 12 months)

TestDate AllAnswers Correct Wrong P% Correct P%Wrong
01/10/2006 13 7 6 53.85% 46.15%
02/10/2006 13 7 6 53.85% 46.15%
03/10/2006 16 6 10 37.50% 62.50%
04/10/2006 16 14 2 87.50% 12.50%

This is my SQL:

SELECT tbl_Scores_Running_Totals.TestDate,
Count(*) AS AllAnswers,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate
ORDER BY tbl_Scores_Running_Totals.TestDate DESC,
Count(*) DESC,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC,
Abs(Sum([TempScore]=0)) DESC

I want to ideally cut down on the number of queries/forms (my db is
suffering from objects density, and its getting messier by the day…)

I have an unbound control on a form that is doing a total on a query field
called [correct], I want to set the criteria from the control box and not the
query.

my control form looks like this:

Bound Control:
[Correct]

Unbound Control:
=Sum([Correct])

Validation Rule:
=DLookUp([TestDate],[Qry_Weekly_Points Stats_
percents]![TestDate],[TestDate]>=Date()-7)

But this returns the totals for All dates, and not the last 7 days, but if i
put the: >=Date()-7 in the underlying query's criteria for [TestDate], it
works fine.

A Validation Rule limits the values that users can enter
into a cantrol. It has nothing to do with calculations.

If the form's record source query is
Qry_Weekly_Points Stats_percents, then I think maybe this
might be what you're looking for:
=Sum(IIf(TestDate >= Date()-7, Correct, 0)

 

[Guest]

Hi Marshall,

I (think) I have mananged to get 'D' out of the same query, I used this:
WeeksPassed: DateDiff("ww",[TestDate],Date()) to get the weeks from 'today'
which seems to (my astonishment!) work

would you kindly take a look at my SQL, and tell me if i'm right?, and if
so, how would i translate that into an unbound control on my form?


The SQL is:


SELECT tbl_Scores_Running_Totals.TestDate, Count(*) AS AllAnswers,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct, Abs(Sum([TempScore]=0))
AS Wrong, [Correct]/[AllAnswers] AS PercentCorrect, [Wrong]/[AllAnswers] AS
PercentWrong, Format([TestDate],"dddd") AS Weekday,
DateDiff("ww",[TestDate],Date()) AS WeeksPassed
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate, Format([TestDate],"dddd"),
DateDiff("ww",[TestDate],Date())
ORDER BY tbl_Scores_Running_Totals.TestDate DESC , Count(*) DESC ,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC , Abs(Sum([TempScore]=0)) DESC;

Yes. A, B and C can be done with the same kind of
expression.

For D, E and F type of calculations, I think you probably
will need other queries that group on the week, month or day
of week.
--
Marsh
MVP [MS Access]

Thanks for that, it worked a treat!, this means I can use other (copies)
unbound controls on the same form to query for different date ranges, right?

this is ultimatelt what I want to do: (its pasted from another post that I
made earlier, but as you seemed to have answered my fundamental question,
perhaps its appropriate to re-paste here.
********************
I want to create an all-purpose (dashboard) form that will give me the
information in
the Form example below. Can someone give me some ideas/pointers as to the
best way of achieving this, what would be the best approach. Should I use:

Single form (can it have multiple queries?)
Various subforms (with separate queries?)

Can I use one master query to serve these various requirements, if so, would
I put the criteria, etc on the form itself? If so, where would that go?

I think I can deal with the criteria for A, B C. but what would I use for D,
E, F.?

The Form: (the A,B,C’s are just for reference)

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

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

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

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

E. Best Days: Tuesdays

F. Worst Days: Fridays

Sample Data (it extends back to 12 months)

TestDate AllAnswers Correct Wrong P% Correct P%Wrong
01/10/2006 13 7 6 53.85% 46.15%
02/10/2006 13 7 6 53.85% 46.15%
03/10/2006 16 6 10 37.50% 62.50%
04/10/2006 16 14 2 87.50% 12.50%

This is my SQL:

SELECT tbl_Scores_Running_Totals.TestDate,
Count(*) AS AllAnswers,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate
ORDER BY tbl_Scores_Running_Totals.TestDate DESC,
Count(*) DESC,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC,
Abs(Sum([TempScore]=0)) DESC

I want to ideally cut down on the number of queries/forms (my db is
suffering from objects density, and its getting messier by the day…)

efandango wrote:
I have an unbound control on a form that is doing a total on a query field
called [correct], I want to set the criteria from the control box and not the
query.

my control form looks like this:

Bound Control:
[Correct]

Unbound Control:
=Sum([Correct])

Validation Rule:
=DLookUp([TestDate],[Qry_Weekly_Points Stats_
percents]![TestDate],[TestDate]>=Date()-7)

But this returns the totals for All dates, and not the last 7 days, but if i
put the: >=Date()-7 in the underlying query's criteria for [TestDate], it
works fine.

A Validation Rule limits the values that users can enter
into a cantrol. It has nothing to do with calculations.

If the form's record source query is
Qry_Weekly_Points Stats_percents, then I think maybe this
might be what you're looking for:
=Sum(IIf(TestDate >= Date()-7, Correct, 0)

 

[Guest]

Hi Marshall,

Working on the basis of your formula for getting the last 7 days, I also
wanted to get the previous week (inclusive), so I tried:

=Sum(IIf([TestDate] Between >=Date()-14 And >=Date()-7,[Correct],0))

but that threw an error message saying:

"you may have entered a comma without a preceding value or identifier"

I tried putting more commas in, i tried removing them, but still get the
error; do yuo know what's wrong with this formula?


Meanwhile... (my penultimate post below, good news!)

Hi Marshall,

I (think) I have mananged to get 'D' out of the same query, I used this:
WeeksPassed: DateDiff("ww",[TestDate],Date()) to get the weeks from 'today'
which seems to (my astonishment!) work

would you kindly take a look at my SQL, and tell me if i'm right?, and if
so, how would i translate that into an unbound control on my form?


The SQL is:


SELECT tbl_Scores_Running_Totals.TestDate, Count(*) AS AllAnswers,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct, Abs(Sum([TempScore]=0))
AS Wrong, [Correct]/[AllAnswers] AS PercentCorrect, [Wrong]/[AllAnswers] AS
PercentWrong, Format([TestDate],"dddd") AS Weekday,
DateDiff("ww",[TestDate],Date()) AS WeeksPassed
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate, Format([TestDate],"dddd"),
DateDiff("ww",[TestDate],Date())
ORDER BY tbl_Scores_Running_Totals.TestDate DESC , Count(*) DESC ,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC , Abs(Sum([TempScore]=0)) DESC;

Yes. A, B and C can be done with the same kind of
expression.

For D, E and F type of calculations, I think you probably
will need other queries that group on the week, month or day
of week.
--
Marsh
MVP [MS Access]

Thanks for that, it worked a treat!, this means I can use other (copies)
unbound controls on the same form to query for different date ranges, right?

this is ultimatelt what I want to do: (its pasted from another post that I
made earlier, but as you seemed to have answered my fundamental question,
perhaps its appropriate to re-paste here.
********************
I want to create an all-purpose (dashboard) form that will give me the
information in
the Form example below. Can someone give me some ideas/pointers as to the
best way of achieving this, what would be the best approach. Should I use:

Single form (can it have multiple queries?)
Various subforms (with separate queries?)

Can I use one master query to serve these various requirements, if so, would
I put the criteria, etc on the form itself? If so, where would that go?

I think I can deal with the criteria for A, B C. but what would I use for D,
E, F.?

The Form: (the A,B,C’s are just for reference)

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

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

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

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

E. Best Days: Tuesdays

F. Worst Days: Fridays

Sample Data (it extends back to 12 months)

TestDate AllAnswers Correct Wrong P% Correct P%Wrong
01/10/2006 13 7 6 53.85% 46.15%
02/10/2006 13 7 6 53.85% 46.15%
03/10/2006 16 6 10 37.50% 62.50%
04/10/2006 16 14 2 87.50% 12.50%

This is my SQL:

SELECT tbl_Scores_Running_Totals.TestDate,
Count(*) AS AllAnswers,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate
ORDER BY tbl_Scores_Running_Totals.TestDate DESC,
Count(*) DESC,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC,
Abs(Sum([TempScore]=0)) DESC

I want to ideally cut down on the number of queries/forms (my db is
suffering from objects density, and its getting messier by the day…)


efandango wrote:
I have an unbound control on a form that is doing a total on a query field
called [correct], I want to set the criteria from the control box and not the
query.

my control form looks like this:

Bound Control:
[Correct]

Unbound Control:
=Sum([Correct])

Validation Rule:
=DLookUp([TestDate],[Qry_Weekly_Points Stats_
percents]![TestDate],[TestDate]>=Date()-7)

But this returns the totals for All dates, and not the last 7 days, but if i
put the: >=Date()-7 in the underlying query's criteria for [TestDate], it
works fine.


:
A Validation Rule limits the values that users can enter
into a cantrol. It has nothing to do with calculations.

If the form's record source query is
Qry_Weekly_Points Stats_percents, then I think maybe this
might be what you're looking for:
=Sum(IIf(TestDate >= Date()-7, Correct, 0)

 

[Marshall Barton]

I don't think that will do what you want. Grouping on each
date and on a week doesn't make sense to me. Using DateDiff
is probably sufficient, but you need to drop the testdate
field to get a total for a week. Then the report text box
can use the Max function to display the best week.

A different query will be needed that groups on
Format(TestDate, "dddd").

I suggest that you use subreports to display the results of
these other two queries.
--
Marsh
MVP [MS Access]

I (think) I have mananged to get 'D' out of the same query, I used this:
WeeksPassed: DateDiff("ww",[TestDate],Date()) to get the weeks from 'today'
which seems to (my astonishment!) work

would you kindly take a look at my SQL, and tell me if i'm right?, and if
so, how would i translate that into an unbound control on my form?

The SQL is:

SELECT tbl_Scores_Running_Totals.TestDate, Count(*) AS AllAnswers,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct, Abs(Sum([TempScore]=0))
AS Wrong, [Correct]/[AllAnswers] AS PercentCorrect, [Wrong]/[AllAnswers] AS
PercentWrong, Format([TestDate],"dddd") AS Weekday,
DateDiff("ww",[TestDate],Date()) AS WeeksPassed
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate, Format([TestDate],"dddd"),
DateDiff("ww",[TestDate],Date())
ORDER BY tbl_Scores_Running_Totals.TestDate DESC , Count(*) DESC ,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC , Abs(Sum([TempScore]=0)) DESC;

Yes. A, B and C can be done with the same kind of
expression.

For D, E and F type of calculations, I think you probably
will need other queries that group on the week, month or day
of week.

Thanks for that, it worked a treat!, this means I can use other (copies)
unbound controls on the same form to query for different date ranges, right?

this is ultimatelt what I want to do: (its pasted from another post that I
made earlier, but as you seemed to have answered my fundamental question,
perhaps its appropriate to re-paste here.
********************
I want to create an all-purpose (dashboard) form that will give me the
information in
the Form example below. Can someone give me some ideas/pointers as to the
best way of achieving this, what would be the best approach. Should I use:

Single form (can it have multiple queries?)
Various subforms (with separate queries?)

Can I use one master query to serve these various requirements, if so, would
I put the criteria, etc on the form itself? If so, where would that go?

I think I can deal with the criteria for A, B C. but what would I use for D,
E, F.?

The Form: (the A,B,C’s are just for reference)

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

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

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

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

E. Best Days: Tuesdays

F. Worst Days: Fridays

Sample Data (it extends back to 12 months)

TestDate AllAnswers Correct Wrong P% Correct P%Wrong
01/10/2006 13 7 6 53.85% 46.15%
02/10/2006 13 7 6 53.85% 46.15%
03/10/2006 16 6 10 37.50% 62.50%
04/10/2006 16 14 2 87.50% 12.50%

This is my SQL:

SELECT tbl_Scores_Running_Totals.TestDate,
Count(*) AS AllAnswers,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate
ORDER BY tbl_Scores_Running_Totals.TestDate DESC,
Count(*) DESC,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC,
Abs(Sum([TempScore]=0)) DESC

I want to ideally cut down on the number of queries/forms (my db is
suffering from objects density, and its getting messier by the day…)


efandango wrote:
I have an unbound control on a form that is doing a total on a query field
called [correct], I want to set the criteria from the control box and not the
query.

my control form looks like this:

Bound Control:
[Correct]

Unbound Control:
=Sum([Correct])

Validation Rule:
=DLookUp([TestDate],[Qry_Weekly_Points Stats_
percents]![TestDate],[TestDate]>=Date()-7)

But this returns the totals for All dates, and not the last 7 days, but if i
put the: >=Date()-7 in the underlying query's criteria for [TestDate], it
works fine.


:
A Validation Rule limits the values that users can enter
into a cantrol. It has nothing to do with calculations.

If the form's record source query is
Qry_Weekly_Points Stats_percents, then I think maybe this
might be what you're looking for:
=Sum(IIf(TestDate >= Date()-7, Correct, 0)

 

[Guest]

Hi Marshall,

It really does seem to have worked. Here's what i did.

I actually based the form on that particular SQL, (so none of this aspect is
in the footer, unlike our previous controls) and instead of sorting on the
Date field in the QBE, I Sorted (Descend) on the [Correct: Tempscore]

This is what I have in my Order By Property on the Form: [Qry_Weekly_Points
Stats_ percents].Correct DESC

I will recheck the figures, but going through the raw data seems to throw up
the same 'best week' as my figures in excel suggest.

I will have to re-read your comments tomorrow, to be honest, i'm struggling
to get my head round your comments right now, I'm in the UK and it's late, i
think i'm having a brain freeze. I really value your help on this and would
appreciate if you could keep an eye out for my next response on this ever
expanding user group.

regards

Eric

I don't think that will do what you want. Grouping on each
date and on a week doesn't make sense to me. Using DateDiff
is probably sufficient, but you need to drop the testdate
field to get a total for a week. Then the report text box
can use the Max function to display the best week.

A different query will be needed that groups on
Format(TestDate, "dddd").

I suggest that you use subreports to display the results of
these other two queries.
--
Marsh
MVP [MS Access]

I (think) I have mananged to get 'D' out of the same query, I used this:
WeeksPassed: DateDiff("ww",[TestDate],Date()) to get the weeks from 'today'
which seems to (my astonishment!) work

would you kindly take a look at my SQL, and tell me if i'm right?, and if
so, how would i translate that into an unbound control on my form?

The SQL is:

SELECT tbl_Scores_Running_Totals.TestDate, Count(*) AS AllAnswers,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct, Abs(Sum([TempScore]=0))
AS Wrong, [Correct]/[AllAnswers] AS PercentCorrect, [Wrong]/[AllAnswers] AS
PercentWrong, Format([TestDate],"dddd") AS Weekday,
DateDiff("ww",[TestDate],Date()) AS WeeksPassed
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate, Format([TestDate],"dddd"),
DateDiff("ww",[TestDate],Date())
ORDER BY tbl_Scores_Running_Totals.TestDate DESC , Count(*) DESC ,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC , Abs(Sum([TempScore]=0)) DESC;

Yes. A, B and C can be done with the same kind of
expression.

For D, E and F type of calculations, I think you probably
will need other queries that group on the week, month or day
of week.


efandango wrote:
Thanks for that, it worked a treat!, this means I can use other (copies)
unbound controls on the same form to query for different date ranges, right?

this is ultimatelt what I want to do: (its pasted from another post that I
made earlier, but as you seemed to have answered my fundamental question,
perhaps its appropriate to re-paste here.
********************
I want to create an all-purpose (dashboard) form that will give me the
information in
the Form example below. Can someone give me some ideas/pointers as to the
best way of achieving this, what would be the best approach. Should I use:

Single form (can it have multiple queries?)
Various subforms (with separate queries?)

Can I use one master query to serve these various requirements, if so, would
I put the criteria, etc on the form itself? If so, where would that go?

I think I can deal with the criteria for A, B C. but what would I use for D,
E, F.?

The Form: (the A,B,C’s are just for reference)

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

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

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

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

E. Best Days: Tuesdays

F. Worst Days: Fridays

Sample Data (it extends back to 12 months)

TestDate AllAnswers Correct Wrong P% Correct P%Wrong
01/10/2006 13 7 6 53.85% 46.15%
02/10/2006 13 7 6 53.85% 46.15%
03/10/2006 16 6 10 37.50% 62.50%
04/10/2006 16 14 2 87.50% 12.50%

This is my SQL:

SELECT tbl_Scores_Running_Totals.TestDate,
Count(*) AS AllAnswers,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate
ORDER BY tbl_Scores_Running_Totals.TestDate DESC,
Count(*) DESC,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC,
Abs(Sum([TempScore]=0)) DESC

I want to ideally cut down on the number of queries/forms (my db is
suffering from objects density, and its getting messier by the day…)


efandango wrote:
I have an unbound control on a form that is doing a total on a query field
called [correct], I want to set the criteria from the control box and not the
query.

my control form looks like this:

Bound Control:
[Correct]

Unbound Control:
=Sum([Correct])

Validation Rule:
=DLookUp([TestDate],[Qry_Weekly_Points Stats_
percents]![TestDate],[TestDate]>=Date()-7)

But this returns the totals for All dates, and not the last 7 days, but if i
put the: >=Date()-7 in the underlying query's criteria for [TestDate], it
works fine.


:
A Validation Rule limits the values that users can enter
into a cantrol. It has nothing to do with calculations.

If the form's record source query is
Qry_Weekly_Points Stats_percents, then I think maybe this
might be what you're looking for:
=Sum(IIf(TestDate >= Date()-7, Correct, 0)

 

[Guest]

Hi Marshall,

Another day, another revelation...

yep, you're right; it doesn't work. (hubris, eh...)

Can you explain to me some more about what you had in mind, when you say
"Using DateDiff is probably sufficient, but you need to drop the testdate
field to get a total for a week. Then the report text box can use the Max
function to display the best week".

I'm not familiar with the syntax for date diff (though i understand the
concept of it) or where to use it. Also, if i drop the testdate field, how do
reference it to the form, and how do also reference the (max) best week if I
have no testdate?

regards, the last two criteria, when you say subreports, do you mean
Subforms? (i'm not planning on using reports, as they will potentially be out
of date, every day)

I don't think that will do what you want. Grouping on each
date and on a week doesn't make sense to me. Using DateDiff
is probably sufficient, but you need to drop the testdate
field to get a total for a week. Then the report text box
can use the Max function to display the best week.

A different query will be needed that groups on
Format(TestDate, "dddd").

I suggest that you use subreports to display the results of
these other two queries.
--
Marsh
MVP [MS Access]

I (think) I have mananged to get 'D' out of the same query, I used this:
WeeksPassed: DateDiff("ww",[TestDate],Date()) to get the weeks from 'today'
which seems to (my astonishment!) work

would you kindly take a look at my SQL, and tell me if i'm right?, and if
so, how would i translate that into an unbound control on my form?

The SQL is:

SELECT tbl_Scores_Running_Totals.TestDate, Count(*) AS AllAnswers,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct, Abs(Sum([TempScore]=0))
AS Wrong, [Correct]/[AllAnswers] AS PercentCorrect, [Wrong]/[AllAnswers] AS
PercentWrong, Format([TestDate],"dddd") AS Weekday,
DateDiff("ww",[TestDate],Date()) AS WeeksPassed
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate, Format([TestDate],"dddd"),
DateDiff("ww",[TestDate],Date())
ORDER BY tbl_Scores_Running_Totals.TestDate DESC , Count(*) DESC ,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC , Abs(Sum([TempScore]=0)) DESC;

Yes. A, B and C can be done with the same kind of
expression.

For D, E and F type of calculations, I think you probably
will need other queries that group on the week, month or day
of week.


efandango wrote:
Thanks for that, it worked a treat!, this means I can use other (copies)
unbound controls on the same form to query for different date ranges, right?

this is ultimatelt what I want to do: (its pasted from another post that I
made earlier, but as you seemed to have answered my fundamental question,
perhaps its appropriate to re-paste here.
********************
I want to create an all-purpose (dashboard) form that will give me the
information in
the Form example below. Can someone give me some ideas/pointers as to the
best way of achieving this, what would be the best approach. Should I use:

Single form (can it have multiple queries?)
Various subforms (with separate queries?)

Can I use one master query to serve these various requirements, if so, would
I put the criteria, etc on the form itself? If so, where would that go?

I think I can deal with the criteria for A, B C. but what would I use for D,
E, F.?

The Form: (the A,B,C’s are just for reference)

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

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

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

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

E. Best Days: Tuesdays

F. Worst Days: Fridays

Sample Data (it extends back to 12 months)

TestDate AllAnswers Correct Wrong P% Correct P%Wrong
01/10/2006 13 7 6 53.85% 46.15%
02/10/2006 13 7 6 53.85% 46.15%
03/10/2006 16 6 10 37.50% 62.50%
04/10/2006 16 14 2 87.50% 12.50%

This is my SQL:

SELECT tbl_Scores_Running_Totals.TestDate,
Count(*) AS AllAnswers,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate
ORDER BY tbl_Scores_Running_Totals.TestDate DESC,
Count(*) DESC,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC,
Abs(Sum([TempScore]=0)) DESC

I want to ideally cut down on the number of queries/forms (my db is
suffering from objects density, and its getting messier by the day…)


efandango wrote:
I have an unbound control on a form that is doing a total on a query field
called [correct], I want to set the criteria from the control box and not the
query.

my control form looks like this:

Bound Control:
[Correct]

Unbound Control:
=Sum([Correct])

Validation Rule:
=DLookUp([TestDate],[Qry_Weekly_Points Stats_
percents]![TestDate],[TestDate]>=Date()-7)

But this returns the totals for All dates, and not the last 7 days, but if i
put the: >=Date()-7 in the underlying query's criteria for [TestDate], it
works fine.


:
A Validation Rule limits the values that users can enter
into a cantrol. It has nothing to do with calculations.

If the form's record source query is
Qry_Weekly_Points Stats_percents, then I think maybe this
might be what you're looking for:
=Sum(IIf(TestDate >= Date()-7, Correct, 0)

 

[Marshall Barton]

You are using both Between and >=
Choose one:

=Sum(IIf([TestDate] Between Date() - 14 And Date() - 7,
[Correct],0))

or

=Sum(IIf([TestDate] >= Date() - 14 And [TestDate] >= Date()
- 7, [Correct],0))
--
Marsh
MVP [MS Access]

Working on the basis of your formula for getting the last 7 days, I also
wanted to get the previous week (inclusive), so I tried:

=Sum(IIf([TestDate] Between >=Date()-14 And >=Date()-7,[Correct],0))

but that threw an error message saying:

"you may have entered a comma without a preceding value or identifier"

I tried putting more commas in, i tried removing them, but still get the
error; do yuo know what's wrong with this formula?

Hi Marshall,

I (think) I have mananged to get 'D' out of the same query, I used this:
WeeksPassed: DateDiff("ww",[TestDate],Date()) to get the weeks from 'today'
which seems to (my astonishment!) work

would you kindly take a look at my SQL, and tell me if i'm right?, and if
so, how would i translate that into an unbound control on my form?


The SQL is:


SELECT tbl_Scores_Running_Totals.TestDate, Count(*) AS AllAnswers,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct, Abs(Sum([TempScore]=0))
AS Wrong, [Correct]/[AllAnswers] AS PercentCorrect, [Wrong]/[AllAnswers] AS
PercentWrong, Format([TestDate],"dddd") AS Weekday,
DateDiff("ww",[TestDate],Date()) AS WeeksPassed
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate, Format([TestDate],"dddd"),
DateDiff("ww",[TestDate],Date())
ORDER BY tbl_Scores_Running_Totals.TestDate DESC , Count(*) DESC ,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC , Abs(Sum([TempScore]=0)) DESC;

Yes. A, B and C can be done with the same kind of
expression.

For D, E and F type of calculations, I think you probably
will need other queries that group on the week, month or day
of week.
--
Marsh
MVP [MS Access]


efandango wrote:
Thanks for that, it worked a treat!, this means I can use other (copies)
unbound controls on the same form to query for different date ranges, right?

this is ultimatelt what I want to do: (its pasted from another post that I
made earlier, but as you seemed to have answered my fundamental question,
perhaps its appropriate to re-paste here.
********************
I want to create an all-purpose (dashboard) form that will give me the
information in
the Form example below. Can someone give me some ideas/pointers as to the
best way of achieving this, what would be the best approach. Should I use:

Single form (can it have multiple queries?)
Various subforms (with separate queries?)

Can I use one master query to serve these various requirements, if so, would
I put the criteria, etc on the form itself? If so, where would that go?

I think I can deal with the criteria for A, B C. but what would I use for D,
E, F.?

The Form: (the A,B,C’s are just for reference)

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

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

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

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

E. Best Days: Tuesdays

F. Worst Days: Fridays

Sample Data (it extends back to 12 months)

TestDate AllAnswers Correct Wrong P% Correct P%Wrong
01/10/2006 13 7 6 53.85% 46.15%
02/10/2006 13 7 6 53.85% 46.15%
03/10/2006 16 6 10 37.50% 62.50%
04/10/2006 16 14 2 87.50% 12.50%

This is my SQL:

SELECT tbl_Scores_Running_Totals.TestDate,
Count(*) AS AllAnswers,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate
ORDER BY tbl_Scores_Running_Totals.TestDate DESC,
Count(*) DESC,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC,
Abs(Sum([TempScore]=0)) DESC

I want to ideally cut down on the number of queries/forms (my db is
suffering from objects density, and its getting messier by the day…)


efandango wrote:
I have an unbound control on a form that is doing a total on a query field
called [correct], I want to set the criteria from the control box and not the
query.

my control form looks like this:

Bound Control:
[Correct]

Unbound Control:
=Sum([Correct])

Validation Rule:
=DLookUp([TestDate],[Qry_Weekly_Points Stats_
percents]![TestDate],[TestDate]>=Date()-7)

But this returns the totals for All dates, and not the last 7 days, but if i
put the: >=Date()-7 in the underlying query's criteria for [TestDate], it
works fine.


:
A Validation Rule limits the values that users can enter
into a cantrol. It has nothing to do with calculations.

If the form's record source query is
Qry_Weekly_Points Stats_percents, then I think maybe this
might be what you're looking for:
=Sum(IIf(TestDate >= Date()-7, Correct, 0)

 

[Marshall Barton]

My simple idea of using a Mac text box won't work in a form.
It's tricky to calculate an aggregate of aggregate values so
for the best/worst week, I am now thinking of queries like:

SELECT TOP 1
DateDiff("ww",[TestDate],Date()) As WeeksPassed,
Count(*) AS AllAnswers,
Sum(Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY DateDiff("ww",[TestDate],Date())
ORDER BY Sum(Tempscore) DESC

Then a simple subform bound to that query can be used to
display all of the best weeks data.

A similar query/subform can be used for the best/worst days
of the week:

SELECT TOP 1
Format([TestDate], "dddd") As WeekDays,
Count(*) AS AllAnswers,
Sum(Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY Format([TestDate], "dddd")
ORDER BY Sum(Tempscore) DESC
--
Marsh
MVP [MS Access]

Another day, another revelation...

yep, you're right; it doesn't work. (hubris, eh...)

Can you explain to me some more about what you had in mind, when you say
"Using DateDiff is probably sufficient, but you need to drop the testdate
field to get a total for a week. Then the report text box can use the Max
function to display the best week".

I'm not familiar with the syntax for date diff (though i understand the
concept of it) or where to use it. Also, if i drop the testdate field, how do
reference it to the form, and how do also reference the (max) best week if I
have no testdate?

regards, the last two criteria, when you say subreports, do you mean
Subforms? (i'm not planning on using reports, as they will potentially be out
of date, every day)

I don't think that will do what you want. Grouping on each
date and on a week doesn't make sense to me. Using DateDiff
is probably sufficient, but you need to drop the testdate
field to get a total for a week. Then the report text box
can use the Max function to display the best week.

A different query will be needed that groups on
Format(TestDate, "dddd").

I suggest that you use subreports to display the results of
these other two queries.

I (think) I have mananged to get 'D' out of the same query, I used this:
WeeksPassed: DateDiff("ww",[TestDate],Date()) to get the weeks from 'today'
which seems to (my astonishment!) work

would you kindly take a look at my SQL, and tell me if i'm right?, and if
so, how would i translate that into an unbound control on my form? [snip bad SQL]


:
Yes. A, B and C can be done with the same kind of
expression.

For D, E and F type of calculations, I think you probably
will need other queries that group on the week, month or day
of week.


efandango wrote:
Thanks for that, it worked a treat!, this means I can use other (copies)
unbound controls on the same form to query for different date ranges, right?

this is ultimatelt what I want to do: (its pasted from another post that I
made earlier, but as you seemed to have answered my fundamental question,
perhaps its appropriate to re-paste here.
********************
I want to create an all-purpose (dashboard) form that will give me the
information in
the Form example below. Can someone give me some ideas/pointers as to the
best way of achieving this, what would be the best approach. Should I use:

Single form (can it have multiple queries?)
Various subforms (with separate queries?)

Can I use one master query to serve these various requirements, if so, would
I put the criteria, etc on the form itself? If so, where would that go?

I think I can deal with the criteria for A, B C. but what would I use for D,
E, F.?

The Form: (the A,B,C’s are just for reference)

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

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

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

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

E. Best Days: Tuesdays

F. Worst Days: Fridays

Sample Data (it extends back to 12 months)

TestDate AllAnswers Correct Wrong P% Correct P%Wrong
01/10/2006 13 7 6 53.85% 46.15%
02/10/2006 13 7 6 53.85% 46.15%
03/10/2006 16 6 10 37.50% 62.50%
04/10/2006 16 14 2 87.50% 12.50%

This is my SQL:

SELECT tbl_Scores_Running_Totals.TestDate,
Count(*) AS AllAnswers,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate
ORDER BY tbl_Scores_Running_Totals.TestDate DESC,
Count(*) DESC,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC,
Abs(Sum([TempScore]=0)) DESC

I want to ideally cut down on the number of queries/forms (my db is
suffering from objects density, and its getting messier by the day…)

 

[Guest]

Hi Marshall,

Is there a certain way i should use the SQL you supplied, it seems to be
offset in two halfs on your post, i tried to paste it in 'as is', but it just
throws errors.

regards

Eric

My simple idea of using a Mac text box won't work in a form.
It's tricky to calculate an aggregate of aggregate values so
for the best/worst week, I am now thinking of queries like:

SELECT TOP 1
DateDiff("ww",[TestDate],Date()) As WeeksPassed,
Count(*) AS AllAnswers,
Sum(Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY DateDiff("ww",[TestDate],Date())
ORDER BY Sum(Tempscore) DESC

Then a simple subform bound to that query can be used to
display all of the best weeks data.

A similar query/subform can be used for the best/worst days
of the week:

SELECT TOP 1
Format([TestDate], "dddd") As WeekDays,
Count(*) AS AllAnswers,
Sum(Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY Format([TestDate], "dddd")
ORDER BY Sum(Tempscore) DESC
--
Marsh
MVP [MS Access]

Another day, another revelation...

yep, you're right; it doesn't work. (hubris, eh...)

Can you explain to me some more about what you had in mind, when you say
"Using DateDiff is probably sufficient, but you need to drop the testdate
field to get a total for a week. Then the report text box can use the Max
function to display the best week".

I'm not familiar with the syntax for date diff (though i understand the
concept of it) or where to use it. Also, if i drop the testdate field, how do
reference it to the form, and how do also reference the (max) best week if I
have no testdate?

regards, the last two criteria, when you say subreports, do you mean
Subforms? (i'm not planning on using reports, as they will potentially be out
of date, every day)

I don't think that will do what you want. Grouping on each
date and on a week doesn't make sense to me. Using DateDiff
is probably sufficient, but you need to drop the testdate
field to get a total for a week. Then the report text box
can use the Max function to display the best week.

A different query will be needed that groups on
Format(TestDate, "dddd").

I suggest that you use subreports to display the results of
these other two queries.


efandango wrote:
I (think) I have mananged to get 'D' out of the same query, I used this:
WeeksPassed: DateDiff("ww",[TestDate],Date()) to get the weeks from 'today'
which seems to (my astonishment!) work

would you kindly take a look at my SQL, and tell me if i'm right?, and if
so, how would i translate that into an unbound control on my form? [snip bad SQL]


:
Yes. A, B and C can be done with the same kind of
expression.

For D, E and F type of calculations, I think you probably
will need other queries that group on the week, month or day
of week.


efandango wrote:
Thanks for that, it worked a treat!, this means I can use other (copies)
unbound controls on the same form to query for different date ranges, right?

this is ultimatelt what I want to do: (its pasted from another post that I
made earlier, but as you seemed to have answered my fundamental question,
perhaps its appropriate to re-paste here.
********************
I want to create an all-purpose (dashboard) form that will give me the
information in
the Form example below. Can someone give me some ideas/pointers as to the
best way of achieving this, what would be the best approach. Should I use:

Single form (can it have multiple queries?)
Various subforms (with separate queries?)

Can I use one master query to serve these various requirements, if so, would
I put the criteria, etc on the form itself? If so, where would that go?

I think I can deal with the criteria for A, B C. but what would I use for D,
E, F.?

The Form: (the A,B,C’s are just for reference)

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

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

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

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

E. Best Days: Tuesdays

F. Worst Days: Fridays

Sample Data (it extends back to 12 months)

TestDate AllAnswers Correct Wrong P% Correct P%Wrong
01/10/2006 13 7 6 53.85% 46.15%
02/10/2006 13 7 6 53.85% 46.15%
03/10/2006 16 6 10 37.50% 62.50%
04/10/2006 16 14 2 87.50% 12.50%

This is my SQL:

SELECT tbl_Scores_Running_Totals.TestDate,
Count(*) AS AllAnswers,
Sum(tbl_Scores_Running_Totals.Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY tbl_Scores_Running_Totals.TestDate
ORDER BY tbl_Scores_Running_Totals.TestDate DESC,
Count(*) DESC,
Sum(tbl_Scores_Running_Totals.Tempscore) DESC,
Abs(Sum([TempScore]=0)) DESC

I want to ideally cut down on the number of queries/forms (my db is
suffering from objects density, and its getting messier by the day…)

 

[Marshall Barton]

I'm not sure what you mean by "offset in two halfs" no what
kind of errors you saw, but there are two separate queries
one for the best week and the other for the best day of
week. You will want another two very similarqueries for the
worst week and one for the worst day of week (just change
the DESC to ASC).

This is adding up to a lot of queries, but as I said before,
calculating aggregates of aggregates is tricky.

--
Marsh
MVP [MS Access]

Is there a certain way i should use the SQL you supplied, it seems to be
offset in two halfs on your post, i tried to paste it in 'as is', but it just
throws errors.

My simple idea of using a Mac text box won't work in a form.
It's tricky to calculate an aggregate of aggregate values so
for the best/worst week, I am now thinking of queries like:

SELECT TOP 1
DateDiff("ww",[TestDate],Date()) As WeeksPassed,
Count(*) AS AllAnswers,
Sum(Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY DateDiff("ww",[TestDate],Date())
ORDER BY Sum(Tempscore) DESC

Then a simple subform bound to that query can be used to
display all of the best weeks data.

A similar query/subform can be used for the best/worst days
of the week:

SELECT TOP 1
Format([TestDate], "dddd") As WeekDays,
Count(*) AS AllAnswers,
Sum(Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY Format([TestDate], "dddd")
ORDER BY Sum(Tempscore) DESC

 

[Guest]

Thanks Marshall,

I was referring to the layout in your post.

It looks like this:

SELECT TOP 1 Text, Text, Text, Text, Text,
Text, Text, Text,
Text, Text, Text, Text,
Text, Text, Text, Text,
Text, Text, Text, Text,
Text, Text, Text, Text,
Text, Text, Text, Text,
Text, Text, Text, Text,

Text, Text, Text, Text, Text, Text, Text, Text,
Text, Text, Text, Text, Text, Text, Text, Text,
Text, Text, Text, Text, Text, Text, Text, Text,

I wasn't sure if it is just an interface error, but if i try an cut n paste
it, i just get errors in the SQL. not a major issue though, as i will put it
into the QBE manually.

Going back to your SQL though, the funny thing is, your SQL is exactly what
I have, at the moment, but without the need to Specify Top 1. which means
that I am going back to saying that I can have that on the same form as the
original A,B,C...

Anyway, I now have your clear instructions, which is 4 seperate queries,
right?.

I would love to email you a small cutdown version of the db with just the
tables and queries in question for you to see what i mean, would you be
interested?

I'm not sure what you mean by "offset in two halfs" no what
kind of errors you saw, but there are two separate queries
one for the best week and the other for the best day of
week. You will want another two very similarqueries for the
worst week and one for the worst day of week (just change
the DESC to ASC).

This is adding up to a lot of queries, but as I said before,
calculating aggregates of aggregates is tricky.

--
Marsh
MVP [MS Access]

Is there a certain way i should use the SQL you supplied, it seems to be
offset in two halfs on your post, i tried to paste it in 'as is', but it just
throws errors.

My simple idea of using a Mac text box won't work in a form.
It's tricky to calculate an aggregate of aggregate values so
for the best/worst week, I am now thinking of queries like:

SELECT TOP 1
DateDiff("ww",[TestDate],Date()) As WeeksPassed,
Count(*) AS AllAnswers,
Sum(Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY DateDiff("ww",[TestDate],Date())
ORDER BY Sum(Tempscore) DESC

Then a simple subform bound to that query can be used to
display all of the best weeks data.

A similar query/subform can be used for the best/worst days
of the week:

SELECT TOP 1
Format([TestDate], "dddd") As WeekDays,
Count(*) AS AllAnswers,
Sum(Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY Format([TestDate], "dddd")
ORDER BY Sum(Tempscore) DESC

 

[Guest]

Hi Marshall,

Well…

The good news is that both those queries do the job as you said they would,
I have been playing around with the ‘weekday’ version and found that If I use
the ‘Top #’ value, then it works ok. But if I say ‘ALL’ instead, it places
‘Tbl_Scores_Running_Totals.TestDate DESC’ into the ‘Order By’ property in the
Form’s properties section., which will then make the query sort by the
Testdate, and throw up the incorrect day; namely ‘today’s date’. No great
shakes, but thought you may be interested to know that it doesn’t seem
dependent on using the ‘Top # value’ s so long as the ‘Order By’ Property is
blank. This is essentially taking me back to my medium term posts where I was
declaring it working, and then deciding it wasn’t. I’m beginning to
understand why that was a moving target.


Here’s a perhaps silly question, but given that both forms ‘Best Week’ and
‘Best Days’ work from essentially identical queries, and both forms are able
to display my A,B,C requirements also; why can’t I just use some kind of
‘filter’ specific to each dialogue box on one form?. It would be nice to be
able to cut down on so many queries, and learn some more about using controls
to filter query’s. Or am I simply driving the wrong way?

One final thing, you kindly provided the syntax for getting the last week’s
figures. Which was:

=Sum(IIf(TestDate >= Date()-7, Correct, 0)

But if I wanted to get inclusive dates between one range and another, what
code would I use?

I tried: =Sum(IIf([TestDate] Between >=Date()-7 And <=Date()-14,[Correct],0))

But it kept telling me I had maybe entered a comma without a preceding value
or identifier. I tried removing and moving around various commas, but to no
avail.

Regards

Eric

I'm not sure what you mean by "offset in two halfs" no what
kind of errors you saw, but there are two separate queries
one for the best week and the other for the best day of
week. You will want another two very similarqueries for the
worst week and one for the worst day of week (just change
the DESC to ASC).

This is adding up to a lot of queries, but as I said before,
calculating aggregates of aggregates is tricky.

--
Marsh
MVP [MS Access]

Is there a certain way i should use the SQL you supplied, it seems to be
offset in two halfs on your post, i tried to paste it in 'as is', but it just
throws errors.

My simple idea of using a Mac text box won't work in a form.
It's tricky to calculate an aggregate of aggregate values so
for the best/worst week, I am now thinking of queries like:

SELECT TOP 1
DateDiff("ww",[TestDate],Date()) As WeeksPassed,
Count(*) AS AllAnswers,
Sum(Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY DateDiff("ww",[TestDate],Date())
ORDER BY Sum(Tempscore) DESC

Then a simple subform bound to that query can be used to
display all of the best weeks data.

A similar query/subform can be used for the best/worst days
of the week:

SELECT TOP 1
Format([TestDate], "dddd") As WeekDays,
Count(*) AS AllAnswers,
Sum(Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY Format([TestDate], "dddd")
ORDER BY Sum(Tempscore) DESC

 

[Marshall Barton]

You can not use TOP without a specific Order By clause
because the sort order determines how to select the top
value.

What is this "ALL" thing you tossed into the fray???

The ‘Best Week’ and ‘Best Days’ queries have the same
structure, BUT they are definitely not identical in any way.
They calculate different results based on a different
grouping. This is way beyond any kind of filtering.

I tried to explain using Between before and that you can not
mix comparison operators link that. Review my first reply
yesterday.
--
Marsh
MVP [MS Access]

The good news is that both those queries do the job as you said they would,
I have been playing around with the ‘weekday’ version and found that If I use
the ‘Top #’ value, then it works ok. But if I say ‘ALL’ instead, it places
‘Tbl_Scores_Running_Totals.TestDate DESC’ into the ‘Order By’ property in the
Form’s properties section., which will then make the query sort by the
Testdate, and throw up the incorrect day; namely ‘today’s date’. No great
shakes, but thought you may be interested to know that it doesn’t seem
dependent on using the ‘Top # value’ s so long as the ‘Order By’ Property is
blank. This is essentially taking me back to my medium term posts where I was
declaring it working, and then deciding it wasn’t. I’m beginning to
understand why that was a moving target.


Here’s a perhaps silly question, but given that both forms ‘Best Week’ and
‘Best Days’ work from essentially identical queries, and both forms are able
to display my A,B,C requirements also; why can’t I just use some kind of
‘filter’ specific to each dialogue box on one form?. It would be nice to be
able to cut down on so many queries, and learn some more about using controls
to filter query’s. Or am I simply driving the wrong way?

One final thing, you kindly provided the syntax for getting the last week’s
figures. Which was:

=Sum(IIf(TestDate >= Date()-7, Correct, 0)

But if I wanted to get inclusive dates between one range and another, what
code would I use?

I tried: =Sum(IIf([TestDate] Between >=Date()-7 And <=Date()-14,[Correct],0))

But it kept telling me I had maybe entered a comma without a preceding value
or identifier. I tried removing and moving around various commas, but to no
avail.

I'm not sure what you mean by "offset in two halfs" no what
kind of errors you saw, but there are two separate queries
one for the best week and the other for the best day of
week. You will want another two very similarqueries for the
worst week and one for the worst day of week (just change
the DESC to ASC).

This is adding up to a lot of queries, but as I said before,
calculating aggregates of aggregates is tricky.

Is there a certain way i should use the SQL you supplied, it seems to be
offset in two halfs on your post, i tried to paste it in 'as is', but it just
throws errors.


:
My simple idea of using a Mac text box won't work in a form.
It's tricky to calculate an aggregate of aggregate values so
for the best/worst week, I am now thinking of queries like:

SELECT TOP 1
DateDiff("ww",[TestDate],Date()) As WeeksPassed,
Count(*) AS AllAnswers,
Sum(Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY DateDiff("ww",[TestDate],Date())
ORDER BY Sum(Tempscore) DESC

Then a simple subform bound to that query can be used to
display all of the best weeks data.

A similar query/subform can be used for the best/worst days
of the week:

SELECT TOP 1
Format([TestDate], "dddd") As WeekDays,
Count(*) AS AllAnswers,
Sum(Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY Format([TestDate], "dddd")
ORDER BY Sum(Tempscore) DESC

 

[Marshall Barton]

I was referring to the layout in your post.

It looks like this:

SELECT TOP 1 Text, Text, Text, Text, Text,
Text, Text, Text,
Text, Text, Text, Text,
Text, Text, Text, Text,
Text, Text, Text, Text,
Text, Text, Text, Text,
Text, Text, Text, Text,
Text, Text, Text, Text,

Text, Text, Text, Text, Text, Text, Text, Text,
Text, Text, Text, Text, Text, Text, Text, Text,
Text, Text, Text, Text, Text, Text, Text, Text,

I wasn't sure if it is just an interface error, but if i try an cut n paste
it, i just get errors in the SQL. not a major issue though, as i will put it
into the QBE manually.

Going back to your SQL though, the funny thing is, your SQL is exactly what
I have, at the moment, but without the need to Specify Top 1. which means
that I am going back to saying that I can have that on the same form as the
original A,B,C...

That does look awful. I use the tab key to indent lines,
but if you email program's tab key setting is much larger
than mine, you'll see that kind of thing. If you're viewing
posts on a web interface instead of a mail reader, you
probably can't do anything about it.

 

[Guest]

Hi Marshall,

OK, I understand the TOP concept and its intrinsic requirement now.

I also, understand the difference between 'Best days' and Best Week' and why
they require seperate queries.

The 'All' thing was to some extent, aberational on my part, and partly
brought on by my 'tunnel vision & Query fatigue', i need to go a few steps
back and re-evelaute what I am trying to achieve here, because I have taken
my eye off the ball, I have overlooked the fact that I have been
misintepreting what I see in my form as 'Best week', which is probably why i
have been confusing (and likely frustrating) you. I am seeing the best week
form coming back and denoting a 'best week' using just one date which is why
I can claim to find that 'type' of best week from the same query that will
give the 'best day'; as they are essentially the same thing.

Whereas, you have likely not been hearing sense from me against your own
reasoning in that regard, as you have quite rightly had the correct notion of
what constitutes a best week, and specifically how to query for it.

Does that make sense?

My Apologies for requesting help on the <7 >14 days control, i simply didn't
notice that reply previously, as i tend to focus on the bottom of the tree
for your latest post.

Kind regards

Eric

You can not use TOP without a specific Order By clause
because the sort order determines how to select the top
value.

What is this "ALL" thing you tossed into the fray???

The ‘Best Week’ and ‘Best Days’ queries have the same
structure, BUT they are definitely not identical in any way.
They calculate different results based on a different
grouping. This is way beyond any kind of filtering.

I tried to explain using Between before and that you can not
mix comparison operators link that. Review my first reply
yesterday.
--
Marsh
MVP [MS Access]

The good news is that both those queries do the job as you said they would,
I have been playing around with the ‘weekday’ version and found that If I use
the ‘Top #’ value, then it works ok. But if I say ‘ALL’ instead, it places
‘Tbl_Scores_Running_Totals.TestDate DESC’ into the ‘Order By’ property in the
Form’s properties section., which will then make the query sort by the
Testdate, and throw up the incorrect day; namely ‘today’s date’. No great
shakes, but thought you may be interested to know that it doesn’t seem
dependent on using the ‘Top # value’ s so long as the ‘Order By’ Property is
blank. This is essentially taking me back to my medium term posts where I was
declaring it working, and then deciding it wasn’t. I’m beginning to
understand why that was a moving target.


Here’s a perhaps silly question, but given that both forms ‘Best Week’ and
‘Best Days’ work from essentially identical queries, and both forms are able
to display my A,B,C requirements also; why can’t I just use some kind of
‘filter’ specific to each dialogue box on one form?. It would be nice to be
able to cut down on so many queries, and learn some more about using controls
to filter query’s. Or am I simply driving the wrong way?

One final thing, you kindly provided the syntax for getting the last week’s
figures. Which was:

=Sum(IIf(TestDate >= Date()-7, Correct, 0)

But if I wanted to get inclusive dates between one range and another, what
code would I use?

I tried: =Sum(IIf([TestDate] Between >=Date()-7 And <=Date()-14,[Correct],0))

But it kept telling me I had maybe entered a comma without a preceding value
or identifier. I tried removing and moving around various commas, but to no
avail.

I'm not sure what you mean by "offset in two halfs" no what
kind of errors you saw, but there are two separate queries
one for the best week and the other for the best day of
week. You will want another two very similarqueries for the
worst week and one for the worst day of week (just change
the DESC to ASC).

This is adding up to a lot of queries, but as I said before,
calculating aggregates of aggregates is tricky.


efandango wrote:
Is there a certain way i should use the SQL you supplied, it seems to be
offset in two halfs on your post, i tried to paste it in 'as is', but it just
throws errors.


:
My simple idea of using a Mac text box won't work in a form.
It's tricky to calculate an aggregate of aggregate values so
for the best/worst week, I am now thinking of queries like:

SELECT TOP 1
DateDiff("ww",[TestDate],Date()) As WeeksPassed,
Count(*) AS AllAnswers,
Sum(Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY DateDiff("ww",[TestDate],Date())
ORDER BY Sum(Tempscore) DESC

Then a simple subform bound to that query can be used to
display all of the best weeks data.

A similar query/subform can be used for the best/worst days
of the week:

SELECT TOP 1
Format([TestDate], "dddd") As WeekDays,
Count(*) AS AllAnswers,
Sum(Tempscore) AS Correct,
Abs(Sum([TempScore]=0)) AS Wrong,
[Correct]/[AllAnswers] AS PercentCorrect,
[Wrong]/[AllAnswers] AS PercentWrong
FROM tbl_Scores_Running_Totals
GROUP BY Format([TestDate], "dddd")
ORDER BY Sum(Tempscore) DESC

 

[Marshall Barton]

OK, I understand the TOP concept and its intrinsic requirement now.

I also, understand the difference between 'Best days' and Best Week' and why
they require seperate queries.

The 'All' thing was to some extent, aberational on my part, and partly
brought on by my 'tunnel vision & Query fatigue', i need to go a few steps
back and re-evelaute what I am trying to achieve here, because I have taken
my eye off the ball, I have overlooked the fact that I have been
misintepreting what I see in my form as 'Best week', which is probably why i
have been confusing (and likely frustrating) you. I am seeing the best week
form coming back and denoting a 'best week' using just one date which is why
I can claim to find that 'type' of best week from the same query that will
give the 'best day'; as they are essentially the same thing.

Whereas, you have likely not been hearing sense from me against your own
reasoning in that regard, as you have quite rightly had the correct notion of
what constitutes a best week, and specifically how to query for it.

Does that make sense?

My Apologies for requesting help on the <7 >14 days control, i simply didn't
notice that reply previously, as i tend to focus on the bottom of the tree
for your latest post.

I'm not sure what makes sense anymore. Whatever it is, ut
seems to depend on my mood and the phase of the moon ;-)

Taking a little time to regroup while you plan your next
attack is probably a good idea. When your ready to jump
back into the fray, we'll be here to back you up ;-)