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
|
|||
|
|||
‘dashboard’ Form/Query to provide different data from the same fie
(I was really not sure how to title this question, but here is the problem)
I want to create an all-purpose 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…) |
Thread Tools | |
Display Modes | |
|
|