Microsoft Access Expression Problem

[Gary Walter]

sorry...left off closing parenthesis...

SELECT
T3.SITE_NO,
T3.[DATE],
T3.Avg7Day,
MIN(T4.Avg7Day) AS AnnualMinimum
FROM
[7_Day_Avg] AS T3
INNER JOIN
[7_Day_Avg] AS T4
ON
T3.SITE_NO = T4.SITE_NO
WHERE
T4.[DATE]>=T3.[DATE]
And
T4.[DATE]<=DateAdd("yyyy",1,T3.[DATE])
GROUP BY
T3.SITE_NO,
T3.[DATE],
T3.Avg7Day;

it might be that you also have to "phase shift"
the WHERE clause to honor grouping
like you did with last one...

SELECT
T3.SITE_NO,
T3.[DATE],
DateAdd("yyyy",1,T3.[DATE]) As EndYr,
T3.Avg7Day,
MIN(T4.Avg7Day) AS AnnualMinimum
FROM
[7_Day_Avg] AS T3
INNER JOIN
[7_Day_Avg] AS T4
ON
T3.SITE_NO = T4.SITE_NO
WHERE
T3.[DATE]<=T4.[DATE]
And
DateAdd("yyyy",1,T3.[DATE])>=T4.[DATE]
GROUP BY
T3.SITE_NO,
T3.[DATE],
DateAdd("yyyy",1,T3.[DATE]),
T3.Avg7Day;

Okay...your previous query had a "symmetry"
to the group gathering, but this one I believe
you are going to have to work through explicitly...

for a T3.SITE_NO/T3.[DATE] group, you want
T4 to provide all the Avg7Day's over a year starting
with T3.[DATE]..

SELECT
T3.SITE_NO,
T3.[DATE],
T3.Avg7Day,
MIN(T4.Avg7Day) AS AnnualMinimum
FROM
[7_Day_Avg] AS T3
INNER JOIN
[7_Day_Avg] AS T4
ON
T3.SITE_NO = T4.SITE_NO
WHERE
T4.[DATE]>=T3.[DATE]
And
T4.[DATE]<=DateAdd("yyyy",1,T3.[DATE]
GROUP BY
T3.SITE_NO,
T3.[DATE],
T3.Avg7Day;

Here's where I stand. I have two queries. The first calculates a 7-
day moving average (Avg7Day) for every day. Here's how it works:

SELECT T1.SITE_NO, T1.DATE, T1.FLOW, Avg(T2.FLOW) AS Avg7Day,
Year(([T1].DATE)) AS FY
FROM [USGS RAW] AS T1 INNER JOIN [USGS RAW] AS T2 ON T1.SITE_NO =
T2.SITE_NO
WHERE (((T1.SITE_NO)="03345000") AND ((T1.DATE)>=([T2].[DATE]-3) And
(T1.DATE)<=([T2].[DATE]+3)))
GROUP BY T1.SITE_NO, T1.DATE, T1.FLOW, Year([T1].DATE);

The next query is supposed to calculate a minimum the minimum 7-day
average over the following year, for each day. For example, for
1/1/2001, it calculates the minimum 7-day average between 1/1/2001 and
12/31/2002. For 1/2/2001, it calculates the minimum 7-day average
between 1/2/2001 and 1/1/2002. The problem is that it is giving me
the 7-day average corresponding to the exact day. There is no
"minimum over a year" being calculated. Here's how it's written:

SELECT [T3].[SITE_NO], [T3].[DATE], Min(T4.Avg7Day) AS AnnualMinimum
FROM [7_Day_Avg] AS T3 INNER JOIN [7_Day_Avg] AS T4 ON ([T3].[DATE] =
[T4].[DATE]) AND (T3.SITE_NO = T4.SITE_NO)
WHERE (([T3].[DATE]>=[T4].[DATE]) And ([T3].[DATE]<=DateAdd("yyyy",1,
[T4].[DATE])))
GROUP BY [T3].[SITE_NO], [T3].[DATE];

Then I need to sort it on 10/1/XXXX, which will give me the minimum 7-
day average for 365 days beginning with 10/1 of each year. I'm all
out of ideas at this point.

 

[j.boswell]

That is what i was looking for. I am going to attempt to run it for
the entire dataset. I will let you all know how it turns out, in case
you ever have to calculate 7-day moving average minimums.

Jimmy

 

[John Spencer]

Then you need to use the first query that is calculating the average flow as
the source for a query to get the minimum average flow for your time frame
and site(s).

Since Gary Walter is responding to this thread also, I'm going to drop out
for now. If Gary and you cannot come up with a solution, I suggest you open
a new thread with a synopsis of what you have that is working and what you
need to accomplish.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Gary Walter]

I hope I wasn't butting in improperly...

It was just a problem that grabbed me
and would not let me rest...

Apologies if I was...

 

[John Spencer]

No problem. I wasn't complaining. I was just getting lost in the
"parallel" threads.

Your solution appeared to be good, so there was no reason to continue
participation and possibly confuse the issue with an alternate solution.
The important thing is that J.Boswell get a solution that he/she can use.

If your solution doesn't work, I would be glad to continue the dialog, but I
may not revisit this thread to see the outcome.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[j.boswell]

Well, I haven't had any luck running all of the data at once. I ran
the "7_Day_Avg" for about four hours this morning with no results.
Then I figured that i just needed to run the second query
"Moving_Minimum" because it references the first query and probably
reruns it (let me know if I'm mistaken). I let this one run for about
three and a half hours and gave up. I will try to let it run
overnight, but it was bogging me down, and as far as I know there is
no way to watch its progress.

So I guess I need to make it more efficient, if possible. The first
query "7_Day_Avg" is as follows:

SELECT T1.SITE_NO, T1.DATE, T1.FLOW, Avg(T2.FLOW) AS Avg7Day
FROM [USGS RAW] AS T1 INNER JOIN [USGS RAW] AS T2 ON T1.SITE_NO =
T2.SITE_NO
WHERE ((T1.DATE)>=([T2].[DATE]-3) And (T1.DATE)<=([T2].[DATE]+3))
GROUP BY T1.SITE_NO, T1.DATE, T1.FLOW, Year(T1.DATE);

The second "Moving_Minimum" now reads:

SELECT T3.SITE_NO, T3.DATE, Min(T4.Avg7Day) AS AnnualMinimum
FROM 7_Day_Avg AS T3 INNER JOIN 7_Day_Avg AS T4 ON
T3.SITE_NO=T4.SITE_NO
WHERE (((T4.DATE)>=T3.DATE And (T4.DATE)<=DateAdd("yyyy",1,T3.DATE)))
GROUP BY T3.SITE_NO, T3.DATE
HAVING ((Month(T3.DATE)=10 And Day(T3.DATE)=1));

The second ends up giving me a table with SITE_NO, DATE (10/1/XXXX),
and the minimum 7 day average over the next 365 days. This is exactly
what I'm looking for and it works fine when I limit it to 2 or 3 sites
with a T1.SITE_NO="XXXXXXX" in the where statement of the 7_Day_Avg
query. But it hasn't finished when I try to run them all
(approximately 130 sites, 1.5 million data rows). Not sure how I can
speed this up.

 

[Gary Walter]

yes please help us john...

we really aren't just dumber than rocks
when we expect to juggle 2 instances of
1.5 million records in our little 512MB-2GB of
computer memory... and then, juggle 2 instances
of those 2 jugglings...

please help us john...I don't know what the
problem is with Gary's crap SQL's....

<j.boswell wrote:
Well, I haven't had any luck running all of the data at once. I ran

 

[Gary Walter]

Sorry Jimmy... I was out of line in previous post..

(BTW funeral services for my father are this AM,
so I hope you might cut me some slack...)

If I were you, my strategy would be:

a) make sure you have an index on your
field [Date]...at least a "Dups okay,"
if it cannot be unique, which I assume
unique dates would not make sense
over this much data...

b) work with smal blocks of data at a time,
saving in temp table..

1) Temporarily turn "7_Day_Avg" into a make
table query for just *one site.* Run it.

2) now change it into an append query that
appends data into this new table. You now
have the append SQL while filtering site.

3) Figure out how to provide "blocks of sites"
for this filter so you are working with a small
subset that will operate in RAM and not have
to be shifted out to hard drive virtual memory.

4) In code, execute the SQL for each "block of
sites" until you have appended all records.
(you might want to make this new table in
a separate mdb, and only link to it).

c) OR ask yourself, do you *really* need this for
all sites for *all dates*, i.e., if you only wanted
to see for say last year, then create a preliminary
query that groups on site and date, filtering for
only dates in last year, then add this query to
"7_Day_Avg" joining on T1 site and T1 date...

But, still append that data set to a temp table,
then run the MIN query on the temp table...

Well...those are 3 strategies that I would try...
and apologies again for previous post...

In a way, I should thank you...
I believe you have cured me of my
addiction to always wanting to help people...

yes please help us john...

we really aren't just dumber than rocks
when we expect to juggle 2 instances of
1.5 million records in our little 512MB-2GB of
computer memory... and then, juggle 2 instances
of those 2 jugglings...

please help us john...I don't know what the
problem is with Gary's crap SQL's....

<j.boswell wrote:
Well, I haven't had any luck running all of the data at once. I ran

the "7_Day_Avg" for about four hours this morning with no results.
Then I figured that i just needed to run the second query
"Moving_Minimum" because it references the first query and probably
reruns it (let me know if I'm mistaken). I let this one run for about
three and a half hours and gave up. I will try to let it run
overnight, but it was bogging me down, and as far as I know there is
no way to watch its progress.

So I guess I need to make it more efficient, if possible. The first
query "7_Day_Avg" is as follows:

SELECT T1.SITE_NO, T1.DATE, T1.FLOW, Avg(T2.FLOW) AS Avg7Day
FROM [USGS RAW] AS T1 INNER JOIN [USGS RAW] AS T2 ON T1.SITE_NO =
T2.SITE_NO
WHERE ((T1.DATE)>=([T2].[DATE]-3) And (T1.DATE)<=([T2].[DATE]+3))
GROUP BY T1.SITE_NO, T1.DATE, T1.FLOW, Year(T1.DATE);

The second "Moving_Minimum" now reads:

SELECT T3.SITE_NO, T3.DATE, Min(T4.Avg7Day) AS AnnualMinimum
FROM 7_Day_Avg AS T3 INNER JOIN 7_Day_Avg AS T4 ON
T3.SITE_NO=T4.SITE_NO
WHERE (((T4.DATE)>=T3.DATE And (T4.DATE)<=DateAdd("yyyy",1,T3.DATE)))
GROUP BY T3.SITE_NO, T3.DATE
HAVING ((Month(T3.DATE)=10 And Day(T3.DATE)=1));

The second ends up giving me a table with SITE_NO, DATE (10/1/XXXX),
and the minimum 7 day average over the next 365 days. This is exactly
what I'm looking for and it works fine when I limit it to 2 or 3 sites
with a T1.SITE_NO="XXXXXXX" in the where statement of the 7_Day_Avg
query. But it hasn't finished when I try to run them all
(approximately 130 sites, 1.5 million data rows). Not sure how I can
speed this up.

 

[j.boswell]

Well, I've got what I was looking for. I let it run over the weekend
and it ended up finishing about 36 hours into it. I may try what you
said about grouping into blocks of sites depending on how many times I
have to do this.

Again, I really appreciate all the help. Hope I didn't piss anybody
off with my lack of skills...

Jimmy