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
|
|||
|
|||
Getting a total control to find totals for last 7 days via a query
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? |
#2
|
|||
|
|||
Getting a total control to find totals for last 7 days via a query
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. 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) -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Getting a total control to find totals for last 7 days via a q
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…) "Marshall Barton" wrote: 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. 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) -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Getting a total control to find totals for last 7 days via a q
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. "Marshall Barton" wrote: 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) |
#5
|
|||
|
|||
Getting a total control to find totals for last 7 days via a q
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; "Marshall Barton" wrote: 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. "Marshall Barton" wrote: 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) |
#6
|
|||
|
|||
Getting a total control to find totals for last 7 days via a q
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!) "efandango" wrote: 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; "Marshall Barton" wrote: 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. "Marshall Barton" wrote: 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) |
#7
|
|||
|
|||
Getting a total control to find totals for last 7 days via a q
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] 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? 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; "Marshall Barton" wrote: 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. "Marshall Barton" wrote: 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) |
#8
|
|||
|
|||
Getting a total control to find totals for last 7 days via a q
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 "Marshall Barton" wrote: 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] 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? 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; "Marshall Barton" wrote: 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. "Marshall Barton" wrote: 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) |
#9
|
|||
|
|||
Getting a total control to find totals for last 7 days via a q
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) "Marshall Barton" wrote: 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] 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? 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; "Marshall Barton" wrote: 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. "Marshall Barton" wrote: 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) |
#10
|
|||
|
|||
Getting a total control to find totals for last 7 days via a q
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] efandango wrote: 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? "efandango" wrote: 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; "Marshall Barton" wrote: 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. "Marshall Barton" wrote: 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) |
|
Thread Tools | |
Display Modes | |
|
|