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,

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





"Marshall Barton" wrote:

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]


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.


"Marshall Barton" wrote:
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



.

Relevant Pages

  • Re: Getting a total control to find totals for last 7 days via a q
    ... I understand the TOP concept and its intrinsic requirement now. ... they require seperate queries. ... I can claim to find that 'type' of best week from the same query that will ... calculating aggregates of aggregates is tricky. ...
    (microsoft.public.access.queries)
  • Re: Getting a total control to find totals for last 7 days via a q
    ... because the sort order determines how to select the top ... work from essentially identical queries, ... to filter query?s. ... calculating aggregates of aggregates is tricky. ...
    (microsoft.public.access.queries)
  • Re: Getting a total control to find totals for last 7 days via a q
    ... Going back to your SQL though, the funny thing is, your SQL is exactly what ... Anyway, I now have your clear instructions, which is 4 seperate queries, ... calculating aggregates of aggregates is tricky. ... CountAS AllAnswers, ...
    (microsoft.public.access.queries)
  • Re: Opinion about aggregates
    ... My question is about the use of aggregates ... Is it best to not build queries with aggregates if you can ... If you mean the "domain aggregate functions" e.g. DLookup, DSum, ... The reason is that these functions all have a bit of resource overhead that ...
    (microsoft.public.access.queries)
  • RE: Opinion about aggregates
    ... If you are talking about things like DCount, DMax, and such, they can be ... Totals queries are very useful to summerize data. ... I’m noticing that designing queries with aggregates might lead to ... Is it best to not build queries with aggregates if you can avoid ...
    (microsoft.public.access.queries)