Re: Getting a total control to find totals for last 7 days via a q

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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)



.

Relevant Pages

  • Re: Calculate a percentage using 2 different query totals
    ... (The Totals) ... combine the 2 Queries, the solution was obvious. ... Enter the appropriate expression from your field headings in the Query ... Well, Duane, this little exercise has been quite and experience. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: save table records from select tempquery
    ... Append query help is right choice for my case. ... my accountant's room) and trying understand best design solution ... sum totals or recall selected aggregated totals as necessary for later ... No, "MakeTable" Queries, just as the name suggests, make an entirely new ...
    (comp.databases.ms-access)
  • Re: Getting a total control to find totals for last 7 days via a q
    ... A different query will be needed that groups on ... these other two queries. ... how would i translate that into an unbound control on my form? ... But this returns the totals for All dates, and not the last 7 days, but if i ...
    (microsoft.public.access.queries)
  • Re: Calculations in Forms
    ... You can use queries ... to extract whatever information you need from the database and then you base ... Open a new query in design view and add your customertable to the design ... you need a totals query to give you daily totals and a ...
    (microsoft.public.access.gettingstarted)
  • RE: Automatic Data Transfer
    ... Otherwise you'd need to do a union query to ... You may have a chains of queries and tables all combining to make your ... The totals probably dont need to stored bec. ... > found that when i try to link multiple summary queries in to a single form ...
    (microsoft.public.access.modulesdaovba)