So here's how far I got with yours:
Rewritten:
SELECT tbl10daycrosstab.shortcoldate, tbl10daycrosstab.acceptedtds, (SELECT
Avg(acceptedtds) FROM tbl10daycrosstab As X WHERE X.shortcoldate Between
DateAdd("d", -9, tbl10daycrosstab.shortcoldate) And
tbl10daycrosstab.shortcoldate) AS 10dayavg
FROM tbl10daycrosstab AS T;
Which when run says enter parameter value for: tbl10daycrosstab.shortcoldate
Here's the sample data from the table:
shortcoldate LAB_TDS PMSC_TDS TDS
AcceptedTDSboollab boolpmsc booltds
12/15/2003 318 318 0 0 1
12/18/2003 312 312 0 0 1
12/22/2003 322 322 0 0 1
12/29/2003 300 300 0 0 1
1/2/2004 293 293 0 0 1
1/5/2004 287 287 0 0 1
1/8/2004 298 298 0 0 1
1/12/2004 295 295 0 0 1
Now, here's something else that I tried......
Here's the statement I did for the davg:
SELECT qryrunning10daycrosstab_Crosstab.coldate,
IIf(IsNumeric([LAB_TDS])=False,"0","1") AS boollab2,
qryrunning10daycrosstab_Crosstab.LAB_TDS,
IIf(IsNumeric([pmsc_tds])=False,"0","1") AS boolpmsc2,
qryrunning10daycrosstab_Crosstab.PMSC_TDS,
IIf(IsNumeric([tds])=False,"0","1") AS booltds2,
qryrunning10daycrosstab_Crosstab.TDS,
Round(IIf([booltds2]="1",[tds],IIf([boollab2]="1",[lab_tds],IIf([boolpmsc2]="1",[pmsc_tds],"0"))),0)
AS AcceptedTDS2,
Round(DAvg("[acceptedtds2]","qryrunning10daycrosstab_select2",[coldate]
Between DateAdd("d",-10,[coldate]) And [coldate]),0) AS 10dayavg
FROM qryrunning10daycrosstab_Crosstab
ORDER BY qryrunning10daycrosstab_Crosstab.coldate;
And you're right it does move really slow, however, it still puts in one
number for the 10dayavg field:281 which is the average result for the whole
table.
You'll need to ignore the big iif expression, it's used to select different
values for the same date.
Here's the sample data for this query:
coldate boollab2 LAB_TDS boolpmsc2 PMSC_TDSbooltds2 TDSAcceptedTDS2 10dayavg
18-Dec-03 0 0 1 312 312 281
22-Dec-03 0 0 1 322 322 281
29-Dec-03 0 0 1 300 300 281
02-Jan-04 0 0 1 293 293 281
05-Jan-04 0 0 1 287 287 281
08-Jan-04 0 0 1 298 298 281
Thanks for all your time on this, it's appreciated!
Jesse
Marshall Barton said:
If you wanted to ignore the time part of a date/time value,
then the crosstab query should have used the DataValue
function instead of the Format function. But I guess that's
neither here nor there at this point.
Yes, I guess you could use a DAvg function instead of the
subquery I suggested, but it would be much slower and
produce the same results.
You never said what happened when you tried my query on your
test table??
I think using a pivot table would just be thrashing around
and only confuse things even more.
You forgot to post the query that "gets close" along with
the sample values and the results it produced.
--
Marsh
MVP [MS Access]
cutthroatjess wrote:
I didn't want the full date/time instance. I only wanted the day, and I
wanted to use a crosstab to combine multiple results for the same day that
were at different times into different fields for the same day as one record.
At any rate, your cdate suggestion fixed the problem that I was having there.
However, I'm still unable to get an average to work. I get close, but it
keeps putting some other number in over and over again. Let me ask you this:
If I put in a DAVG function in as a field as 10dayavg, would that work? If
so, how would I set that up? Keep in mind that these are all queries, so
they're not coming directly from tables. But just for the fun of it, I tried
your sql on a new table of data made from this query in question, making sure
that the formating of the date was correct, and removing all the group by
stuff. Here's another thought: would some kind of pivot table work?
:
Why did you format the date field in the crosstab query? It
sounds like you should have left it as a date instead of
formatting it to something pretty.
On the other hand, if this mangled date field is still a
text string that represents a date, then maybe my suggested
query can convert it back to a real date value by using
CDate(coldate) instead of just coldate. But this is
definitely not the best way to do it.
cutthroatjess wrote:
Sorry, now that I look again at what I sent, it really doesn't make much sense.
All the group by stuff was added later, because the crosstab query screwed
up the date. A date field called coldate was formated as a long date with
long and short date entries. I wanted to crosstab only by day so I created a
field to do that. The crosstab works, but the date is converted to some weird
text format. Therefore, a subsequent query on the crosstab took the pieces of
the date and sorted them. Which is weird because it takes the pieces of the
date like a date, but sorting treats it as a text.
Anyways, shortcoldate is the original date/time field turned into just the
date, acceptedtds is the result field mentioned in the beginning. The data
still looks the same as in the first example. Perhaps if we could overcome
the crosstab manipulation of the date this would work?
:
Whoa! "This is a query off of a query that queries a
crosstab query" means there is a heck of lot going on that I
really don't have time to unravel. So, I will stick to the
probelem presented in your sample data.
I do not see how the query below relates to that data, nor
does it attempt to do anything like what I suggested before
so I have no idea where to go from here.
I will take a guess that this new query doesn't do anything
useful because the are too many items in the Group By
clause, but I don't think it can possibly calculate a 10 day
moving average so I am bewildered as to how it relates to
your original question.
cutthroatjess wrote:
Here's the sql that I came up with:
SELECT Avg(qryrunning10daycrosstab_select.AcceptedTDS) AS AvgOfAcceptedTDS,
qryrunning10daycrosstab_select.AcceptedTDS
FROM qryrunning10daycrosstab_select
WHERE (((qryrunning10daycrosstab_select.shortcoldate) Between
DateAdd("d",-9,[qryrunning10daycrosstab_select].[shortcoldate]) And
[qryrunning10daycrosstab_select].[shortcoldate]))
GROUP BY Year([shortcoldate]), Month([shortcoldate]), Day([shortcoldate]),
qryrunning10daycrosstab_select.AcceptedTDS
ORDER BY Year([shortcoldate]), Month([shortcoldate]), Day([shortcoldate]);
This is a query off of a query that queries a crosstab query (say that 5x
fast). The crosstab query screws up the date which is why you see all of the
group/order by stuff.
This doesn't work. It queries just fine, however it doesn't average anything
as it's supposed to. It just gives the result that is already there instead
of an average for the previous 10 days.
:
I think I need more of a clue than no luck"
Are you sure the field names are correct?
Post a Copy/Paste of the your version of the query and what
the result was or which error message you got.
cutthroatjess wrote:
I tried making that work, but to no luck. I see the logic behind it though.
How would something like that work in a query to give me an average for each
record?
cutthroatjess wrote:
I would like to create a running average in a field that takes dated results
(10 days from the current field date) and averages them for the current field.
For example:
coldate result 10dayavg
1/1/2000 245 ?
1/2/2000 246 ?
1/3/2000 247 ?
1/4/2000 248 ?
1/5/2000 249 ?
1/6/2000 250 ?
1/7/2000 800 ?
1/8/2000 1 ?
1/9/2000 2 ?
1/10/2000 3 ?
Now the results are sequental on the date. What I would like to see happen
in the "10dayavg" field is:
245
245.5
246
246.5
247
247.5
326
285.75
254.22
229.1
:
I think this should do that:
SELECT T.coldate, T.result,
(SELECT Avg(X.result)
FROM table As X
WHERE X.coldate Between
DateAdd("d', -9, T.coldate) And T.coldate
) As [10dayavg]
FROM table As T
