running sum question ?

[Guest]

I have a running sum query. It works, but I'd like to limit the average to
the last 7 days.

For instance, the last average should only include the past 7 days. The 159
is an average including all the lines, not just the 7 last ...

I tried to add "limit 7", but it didn't work ?!

===============
SELECT TIT.weblog_id, TIT.entry_date as ted, DAT.field_id_45 as pd,
TIT.entry_id as tid, (

SELECT AVG(conso_d.field_id_45) AS tempp
FROM exp_weblog_titles AS conso_t INNER JOIN exp_weblog_data AS conso_d ON
conso_t.entry_id = conso_d.entry_id
where conso_t.weblog_id = 3 and conso_t.entry_date <= TIT.entry_date
order by conso_t.entry_date asc
limit 7
) as RA

FROM exp_weblog_titles AS TIT INNER JOIN exp_weblog_data DAT ON TIT.entry_id
= DAT.entry_id
where TIT.weblog_id = 3
order by ted asc;

================

152—152
157—154.5
157—155.33333333333
158.1—156.025
158.1—156.44
156.5—156.45
159—156.81428571429
160—157.2125
158.4—157.34444444444
157—157.31
158.5—157.41818181818
158.5—157.50833333333
157.4—157.5
161.5—157.78571428571
158.4—157.82666666667
159.3—157.91875
160.6—158.07647058824
158.5—158.1
158.5—158.12105263158
157.5—158.09
159.1—158.1380952381
159.8—158.21363636364
161.4—158.35217391304
160.5—158.44166666667
160.2—158.512
162.2—158.65384615385
162—158.77777777778
162.7—158.91785714286
162.5—159.04137931034
163.8—159.2
166—159.41935483871

 

[Guest]

have alook at this

http://allenbrowne.com/subquery-01.html

 

[Guest]

If you are not storing the time of day in your date/time field (ie. it
defaults to zero, which is midnight), then the following criteria added to
the appropriate field should work:

Between Date() And Date()-7


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

If you are not storing the time of day in your date/time field (ie. it
defaults to zero, which is midnight), then the following criteria added to
the appropriate field should work:

Between Date() And Date()-7


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________