Microsoft Access Expression Problem

[j.boswell]

I have a table [USGS RAW NO TITLE] set up in access with water flow
sites. I am trying to calculate 7-day moving averages of flow for
each day limited by the site (3-days ahead and 3-days behind). I
think I have the expression working right on a 7-day moving average:

Expr1: DAvg("[USGS RAW NO TITLE]![Flow]","USGS RAW NO TITLE","[USGS
RAW NO TITLE]![datetime] BETWEEN " & Format(DateAdd("d",-3,[USGS RAW
NO TITLE]![datetime]),"\#m/d/yyyy\#") & " AND " & Format(DateAdd("d",
+3,[USGS RAW NO TITLE]![datetime]),"\#m/d/yyyy\#"))

However, I cannot get it to limit the averages to the site specific
data. Right now I think it is calculating across the sites, so if
two sites have a flow on the same day, it is using data from both
sites. I can't figure out how to limit it. I would do it in excel
but I have something around 1.5 million rows. Anything would help.

Jimmy

 

[John Spencer]

With that many rows I would be leary of using DAvg. You need to
restrict the calculation to the site in addition to the date range.

DAvg("Flow","[USGS Raw No Title]","SiteID = " & [SiteID] & " AND
DateTime Between #" & DateAdd("d",-3,[DateTime] & "# AND #" &
DateAdd("d",3,[DateTime]) )

The following query =__M_A_Y__ work for you and give you the desired
results faster. Be sure you have indexes on SiteId and DateTime.

SELECT T1.SiteID, Avg(T2.Flow) as Avg7Day
FROM [USGS Raw No Title] as T1 INNER JOIN [USGS Raw No Title] as T2
ON T1.SiteID = T2.SiteID AND
(T1.DateTime >= (T2.DateTime -3)) AND
(T1.DateTime <= (T2.DateTime +3))
GROUP By T1.SiteID

If you want to limit to one site, add a where clause to the query. (I
would start out limiting things to one site until I could see if the
query was running and it was giving the correct result.)
\

SELECT T1.SiteID, Avg(T2.Flow) as Avg7Day
FROM [USGS Raw No Title] as T1 INNER JOIN [USGS Raw No Title] as T2
ON T1.SiteID = T2.SiteID AND
(T1.DateTime >= (T2.DateTime -3)) AND
(T1.DateTime <= (T2.DateTime +3))
WHERE T1.SiteID = 2002
GROUP By T1.SiteID

And YES I made up the field names.

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

 

[Gary Walter]

I'm sorry but I wonder if final query shouldn't
be adjusted (I did not test though).

I think I might want to group by T1.SiteID
and T1.DateTime. Based on "working example
success," there may be no time portion to the TimeDate
field, but, if so, one might want to group on
DatePart(T1.DateTime) instead of T1.DateTime.

If one averages T2.Flow, then T2 is providing the 7 days,
i.e., 3 days on either side of each SiteID/T1.TimeDate group
{or DatePart(T1.DateTime)}...

so, for each SiteID/T1.DateTime group,
one would want to include records from T2
with the same SiteId and
whose T2.DateTime was
between
(T1.DateTime - 3)
and
(T1.DateTime + 3)

just to be sure (if there were a time portion)

T2.DateTime >= DatePart(T1.DateTime - 3)
AND
T2.DateTime < DatePart(T1.DateTime + 4)

SELECT
T1.SiteID,
DatePart(T1.DateTime) As FlowDay,
Avg(T2.Flow) as Avg7Day
FROM
[USGS Raw No Title] as T1
INNER JOIN
[USGS Raw No Title] as T2
ON
T1.SiteID = T2.SiteID
AND

(T2.DateTime >= DatePart(T1.DateTime - 3)
AND
T2.DateTime < DatePart(T1.DateTime + 4))
GROUP By
T1.SiteID,
DatePart(T1.DateTime);

As John said, it wouldn't hurt to add filter
for just one site first to see if working properly.


and, of course, I could be wrong...

With that many rows I would be leary of using DAvg. You need to restrict
the calculation to the site in addition to the date range.

DAvg("Flow","[USGS Raw No Title]","SiteID = " & [SiteID] & " AND DateTime
Between #" & DateAdd("d",-3,[DateTime] & "# AND #" &
DateAdd("d",3,[DateTime]) )

The following query =__M_A_Y__ work for you and give you the desired
results faster. Be sure you have indexes on SiteId and DateTime.

SELECT T1.SiteID, Avg(T2.Flow) as Avg7Day
FROM [USGS Raw No Title] as T1 INNER JOIN [USGS Raw No Title] as T2
ON T1.SiteID = T2.SiteID AND
(T1.DateTime >= (T2.DateTime -3)) AND
(T1.DateTime <= (T2.DateTime +3))
GROUP By T1.SiteID

If you want to limit to one site, add a where clause to the query. (I
would start out limiting things to one site until I could see if the query
was running and it was giving the correct result.)
\

SELECT T1.SiteID, Avg(T2.Flow) as Avg7Day
FROM [USGS Raw No Title] as T1 INNER JOIN [USGS Raw No Title] as T2
ON T1.SiteID = T2.SiteID AND
(T1.DateTime >= (T2.DateTime -3)) AND
(T1.DateTime <= (T2.DateTime +3))
WHERE T1.SiteID = 2002
GROUP By T1.SiteID

And YES I made up the field names.

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

I have a table [USGS RAW NO TITLE] set up in access with water flow
sites. I am trying to calculate 7-day moving averages of flow for
each day limited by the site (3-days ahead and 3-days behind). I
think I have the expression working right on a 7-day moving average:

Expr1: DAvg("[USGS RAW NO TITLE]![Flow]","USGS RAW NO TITLE","[USGS
RAW NO TITLE]![datetime] BETWEEN " & Format(DateAdd("d",-3,[USGS RAW
NO TITLE]![datetime]),"\#m/d/yyyy\#") & " AND " & Format(DateAdd("d",
+3,[USGS RAW NO TITLE]![datetime]),"\#m/d/yyyy\#"))

However, I cannot get it to limit the averages to the site specific
data. Right now I think it is calculating across the sites, so if
two sites have a flow on the same day, it is using data from both
sites. I can't figure out how to limit it. I would do it in excel
but I have something around 1.5 million rows. Anything would help.

Jimmy

 

[Gary Walter]

Also, if you are designing in QBE,
you may find the non-equi joins
frustrating...they can be moved to
WHERE clause...

SELECT
T1.SiteID,
DatePart(T1.DateTime) As FlowDay,
Avg(T2.Flow) as Avg7Day
FROM
[USGS Raw No Title] as T1
INNER JOIN
[USGS Raw No Title] as T2
ON
T1.SiteID = T2.SiteID
WHERE
DatePart(T1.DateTime)<= T2.DateTime + 3
AND
DatePart(T1.DateTime)>T2.DateTime - 4
GROUP BY
T1.SiteID,
DatePart(T1.DateTime);

which is what John basically had in first place...
sorry...I just couldn't see that until now...


"Gary Walter"wrote:

I'm sorry but I wonder if final query shouldn't
be adjusted (I did not test though).

I think I might want to group by T1.SiteID
and T1.DateTime. Based on "working example
success," there may be no time portion to the TimeDate
field, but, if so, one might want to group on
DatePart(T1.DateTime) instead of T1.DateTime.

If one averages T2.Flow, then T2 is providing the 7 days,
i.e., 3 days on either side of each SiteID/T1.TimeDate group
{or DatePart(T1.DateTime)}...

so, for each SiteID/T1.DateTime group,
one would want to include records from T2
with the same SiteId and
whose T2.DateTime was
between
(T1.DateTime - 3)
and
(T1.DateTime + 3)

just to be sure (if there were a time portion)

T2.DateTime >= DatePart(T1.DateTime - 3)
AND
T2.DateTime < DatePart(T1.DateTime + 4)

SELECT
T1.SiteID,
DatePart(T1.DateTime) As FlowDay,
Avg(T2.Flow) as Avg7Day
FROM
[USGS Raw No Title] as T1
INNER JOIN
[USGS Raw No Title] as T2
ON
T1.SiteID = T2.SiteID
AND

(T2.DateTime >= DatePart(T1.DateTime - 3)
AND
T2.DateTime < DatePart(T1.DateTime + 4))
GROUP By
T1.SiteID,
DatePart(T1.DateTime);

As John said, it wouldn't hurt to add filter
for just one site first to see if working properly.


and, of course, I could be wrong...

With that many rows I would be leary of using DAvg. You need to restrict
the calculation to the site in addition to the date range.

DAvg("Flow","[USGS Raw No Title]","SiteID = " & [SiteID] & " AND DateTime
Between #" & DateAdd("d",-3,[DateTime] & "# AND #" &
DateAdd("d",3,[DateTime]) )

The following query =__M_A_Y__ work for you and give you the desired
results faster. Be sure you have indexes on SiteId and DateTime.

SELECT T1.SiteID, Avg(T2.Flow) as Avg7Day
FROM [USGS Raw No Title] as T1 INNER JOIN [USGS Raw No Title] as T2
ON T1.SiteID = T2.SiteID AND
(T1.DateTime >= (T2.DateTime -3)) AND
(T1.DateTime <= (T2.DateTime +3))
GROUP By T1.SiteID

If you want to limit to one site, add a where clause to the query. (I
would start out limiting things to one site until I could see if the
query was running and it was giving the correct result.)
\

SELECT T1.SiteID, Avg(T2.Flow) as Avg7Day
FROM [USGS Raw No Title] as T1 INNER JOIN [USGS Raw No Title] as T2
ON T1.SiteID = T2.SiteID AND
(T1.DateTime >= (T2.DateTime -3)) AND
(T1.DateTime <= (T2.DateTime +3))
WHERE T1.SiteID = 2002
GROUP By T1.SiteID

And YES I made up the field names.

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

I have a table [USGS RAW NO TITLE] set up in access with water flow
sites. I am trying to calculate 7-day moving averages of flow for
each day limited by the site (3-days ahead and 3-days behind). I
think I have the expression working right on a 7-day moving average:

Expr1: DAvg("[USGS RAW NO TITLE]![Flow]","USGS RAW NO TITLE","[USGS
RAW NO TITLE]![datetime] BETWEEN " & Format(DateAdd("d",-3,[USGS RAW
NO TITLE]![datetime]),"\#m/d/yyyy\#") & " AND " & Format(DateAdd("d",
+3,[USGS RAW NO TITLE]![datetime]),"\#m/d/yyyy\#"))

However, I cannot get it to limit the averages to the site specific
data. Right now I think it is calculating across the sites, so if
two sites have a flow on the same day, it is using data from both
sites. I can't figure out how to limit it. I would do it in excel
but I have something around 1.5 million rows. Anything would help.

Jimmy

 

[Gary Walter]

the arithmetic (please tell me if I'm wrong):

T2.DateTime >= DatePart(T1.DateTime - 3)
AND
T2.DateTime < DatePart(T1.DateTime + 4)

is same as

T2.DateTime >= DatePart(T1.DateTime) - 3
AND
T2.DateTime < DatePart(T1.DateTime) + 4

is same as

T2.DateTime + 3 >= DatePart(T1.DateTime)
AND
T2.DateTime -4 < DatePart(T1.DateTime)

is same as

DatePart(T1.DateTime)<=T2.DateTime + 3
AND
DatePart(T1.DateTime)>T2.DateTime - 4

 

[j.boswell]

I'm not sure what I am doing wrong. By the way, I'm using the SQL
Design option in Access Queries. Hope that's right. This is what I
have in the Query.

SELECT T1.SITE_NO, Avg(T2.FLOW) as Avg7Day
FROM [USGS RAW] as T1
INNER JOIN
[USGS RAW] as T2
ON T1.SITE_NO = T2.SITE_NO AND
(T1.DATE >= (T2.DATE -3)) AND
(T1.DATE <= (T2.DATE +3))
WHERE T1.SITE_NO = 03345000
GROUP By T1.SITE_NO;

When I try to go to Datasheet View I get the error: "Data Type
Mismatch In Criteria Expression"
When I go to the Design View it says" Access can't represent the join
expression T1.DATE >=(T2.DATE-3) in Design view"

I'm not really sure what this means or if these are somehow related.
It seems like I've got a problem with my date, which is formatted as a
date (M/D/YYYY). I indexed both the "DATE" field and the "SITE_NO"
field. Sorry, about the misunderstandings but the only language I
know is FORTRAN, so this doesn't make a whole lot of sense to me.

 

[j.boswell]

By the way...my date field does not include time...

 

[Gary Walter]

It sounds like SITE_NO is text,
so need to wrap literal value in quotes.

You can move the non-equi part of the JOIN
to the WHERE clause so you can see query
in Design View.

DATE is a reserved Access word, so if that
is the actual name of the field, wrap it in brackets.

SELECT
T1.SITE_NO,
T1.[DATE],
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))
AND
T1.SITE_NO = "03345000"
GROUP By
T1.SITE_NO,
T1.[DATE];

 

[j.boswell]

I changed the "WHERE" statement to look for text, because my SITE_NO
column is actually a text value. The query is running now, but it
only returns a table with one site number and one 7-day average. I
need it to return a 7-day average for every day that I have a
measurement. Here's what I've got so far...

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

 

[John Spencer]

It looks like Site_No is a text field. If so, you need quotes around the
search value.

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

The type of join criteria used above cannot be represented in the query
grid. Normally you use an equi-join (A = B) and not an non-equi join (A >
B or A <> B or A is between B and C). The query grid can only handle
equi-joins.

As Gary Walter noted elsewhere, you can move the non-equi part of the join
into the where clause. Then you would end up with something that could be
shown in the query grid (although it may be a bit slower). In SQL view that
would look like

SELECT T1.SITE_NO, 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.SITE_NO = "03345000" AND
(T1.DATE >= (T2.DATE -3)) AND
(T1.DATE <= (T2.DATE +3))
GROUP By T1.SITE_NO;

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

 

[j.boswell]

I am still only getting a "one row" table with a site number and one
value for 7-day average. What I'm looking for is a table with every
date listed and a corresponding 7-day average for each date.

SELECT T1.SITE_NO, 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.SITE_NO = "03345000" AND
(T1.[DATE] >= (T2.[DATE] -3)) AND
(T1.[DATE] <= (T2.[DATE] +3))
GROUP By T1.SITE_NO;

 

[j.boswell]

Now we're getting somewhere. Thanks for all the help. Here's what I
have so far.

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.SITE_NO)="03345000") AND ((T1.DATE)>=([T2].[DATE]-3) And
(T1.DATE)<=([T2].[DATE]+3)))
GROUP BY T1.SITE_NO, T1.DATE, T1.FLOW;

This return a table with the SITE_NO, DATE, FLOW, and 7dayavg as
column headers. I only included the flow so that I could check the 7-
day averages.

There is one more part to this calculation that I could really use
some help with. Now that I have this table, I need to find the
minimum for each year, for each site. The year needs to go from 10/01/
XXXX to 09/31/XXXX. This can be created in a whole new column, which
would actually be better because then I could check it against the
values in the 7day avg column.

 

[John Spencer]

It would probably be best to calculate the min value per site per year in a
separate query and then link the two queries together in a third query.

SELECT SITENO
, Year(DateAdd("m",3, [Date])) as FY
, Min(FLOW) as AnnualMinimum
FROM [USGS RAW]
GROUP BY SITENO, Year(DateAdd("m",3, [Date])

You'll need to adjust the other query to get the FY in it also.

SELECT T1.SITE_NO, T1.DATE, T1.FLOW
, Avg(T2.FLOW) AS Avg7Day, Year(DateAdd("m",3, [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(DateAdd("m",3, [Date])) as FY

Save both these queries and then join them together as if they were tables
on SiteNo and FY

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

 

[j.boswell]

The minimum that I'm calculated is not based on the flow. It is based
on the 7-day Avg Flow from the other query. This is where I have been
having some problems. It would be nice if I could use a Min(Avg(X))
type function, but Access will not accept that. Not sure how to best
accomplish this...

 

[j.boswell]

The problem I am having right now is that I need to calculate the
minimum based on the 7-day average flow, not the daily values. This
would be easy if access would let me use a min(avg(X)) type function,
but it won't. I haven't figured out a way around this yet.

 

[j.boswell]

When I try the following:

SELECT T1.SITE_NO, T1.DATE, T1.FLOW, Avg(T2.FLOW) AS
Avg7Day,Year(DateAdd("m",3,[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(DateAdd("m",3,[DATE])) AS
FY;

I receive this error: "Syntax error (missing operator) in query
expression 'Year(DateAdd("m",3,[DATE])) AS FY'.

I think this is referring to my GROUP BY statement. So I removed the
'Year(DateAdd("m",3,[DATE])) AS FY' in the GROUP BY statement. I've
tried replacing it with a few things but keep receiving this error

"The specified field '[DATE]' could refer to more than one table
listed in the FROM clause of your SQL statement."

 

[John Spencer]

Since you have two tables with a field named Date then you need to
qualify the reference to the Date field by adding the table name

Year(DateAdd("m",3,T1.[Date]))

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

 

[j.boswell]

Here's where I'm at... I have two queries. The first calculates a 7-
day moving average (Avg7Day) for each site. Here's how it's working:

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 second table is supposed to give me the minimum "7-day average"
for (each day + 1 year). So for 1/1/2001, it would give me the
minimum "7-day average" betweeen 1/1/2001 and 12/31/2001. For
1/2/2001, it would give me the minimum "7-day average" between
1/2/2001 and 1/1/2001. Right now all it is doing is reprinting the 7-
day average for each day. I can't figure out what the problem is.
Here's how it looks:

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];

I just don't know at this point.

Please help...

 

[j.boswell]

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.

 

[Gary Walter]

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;