Calculate 7 day high lows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have spent the last several days looking on this site for any help for
calculating a 7 day high and 7 day low. I have found many discussions about
dates and using the current dates for calculations. I need to find the 7 day
high and low for dates that occured in the past, normally one to two years
ago.

I have a table with the following fields:

Date (Date of temperature)
Time (temp is taken every 1/2 hour)
Temp (in celcius)
Site (id number for site)
There are over 6000 records for each site in a separate table (there are 37
sites). I am mainly interested in the daily averages, minimums and maximums
so I created a query with the following fields and calculations which group
by date:

[date]
[average_temp]
[minimum_temp]
[maximum_temp]

results look like this:

DATE AVERAGE_TEMP MINIMUM_TEMP MAXIMUM_TEMP
5/26/2005 23.19 20.97 25.91
5/27/2005 20.29 18.05 24.52
5/28/2005 17.42 15.97 19.02
5/29/2005 16.16 14.71 17.73
5/30/2005 17.80 15.02 22.31
5/31/2005 17.04 15.82 19.34
6/1/2005 15.77 14.55 18.22
6/2/2005 15.78 14.08 18.37
6/3/2005 17.04 13.31 22.81

I need to gather the running seven day highs and lows from these
calculations. For this one site (there are 37 total) I have a starting date
of 5/26/2005 and ending date of 10/06/05. The dates will be different in
each site.

For example the result I need is the following:

DATE 7 day high 7 day low
5/26/2005 0 0 No 7 day data available
5/27/2005 0 0 No 7 day data available
5/28/2005 0 0 No 7 day data available
5/29/2005 0 0 No 7 day data available
5/30/2005 0 0 No 7 day data available
5/31/2005 0 0 No 7 day data available
6/1/2005 25.91 14.55 using data from 5/26-6/1
6/2/2005 24.52 14.08 using data from 5/27-6/2
6/3/2005 22.81 13.31 using data from 5/28-6/3
ETC........through 10/06/2005

I am no expert at access and have never used Visual Basic so please be
gentle with me. However, I am very interested in increasing my skills and
would appreciate any suggestions on how to learn VBA for access on a limited
budget.

Thanks in advance for your help.
 
The following MIGHT work, although I would be happier if you didn't have field
name Date, but had a field name of WxDate or some other name other than the
reserved word - Date.

SELECT Y.[Date],
(SELECT Max(Maximum_Temp)
FROM YourQuery as MT
WHERE MT.[Date] Between Y.[Date]-6 and Y.[Date]) as MaxTemp,
(SELECT Min(Minimum_Temp)
FROM YourQuery as MT
WHERE MT.[Date] Between Y.[Date]-6 and Y.[Date]) as MinTemp
FROM YourQuery as Y
WHERE 7 = (SELECT Count(MT.[Date])
FROM YourQuery as MT
WHERE MT.[Date] Between Y.[Date]-6 and Y.[Date])

That will probably be fairly slow. And I suspect you are going to post back and
say, by the way I wanted to do this per site. If so, you will need to modify
the above to include a site comparison in each of the sub-queries. That means
your master query also needs a site id in it.

I have spent the last several days looking on this site for any help for
calculating a 7 day high and 7 day low. I have found many discussions about
dates and using the current dates for calculations. I need to find the 7 day
high and low for dates that occured in the past, normally one to two years
ago.

I have a table with the following fields:

Date (Date of temperature)
Time (temp is taken every 1/2 hour)
Temp (in celcius)
Site (id number for site)
There are over 6000 records for each site in a separate table (there are 37
sites). I am mainly interested in the daily averages, minimums and maximums
so I created a query with the following fields and calculations which group
by date:

[date]
[average_temp]
[minimum_temp]
[maximum_temp]

results look like this:

DATE AVERAGE_TEMP MINIMUM_TEMP MAXIMUM_TEMP
5/26/2005 23.19 20.97 25.91
5/27/2005 20.29 18.05 24.52
5/28/2005 17.42 15.97 19.02
5/29/2005 16.16 14.71 17.73
5/30/2005 17.80 15.02 22.31
5/31/2005 17.04 15.82 19.34
6/1/2005 15.77 14.55 18.22
6/2/2005 15.78 14.08 18.37
6/3/2005 17.04 13.31 22.81

I need to gather the running seven day highs and lows from these
calculations. For this one site (there are 37 total) I have a starting date
of 5/26/2005 and ending date of 10/06/05. The dates will be different in
each site.

For example the result I need is the following:

DATE 7 day high 7 day low
5/26/2005 0 0 No 7 day data available
5/27/2005 0 0 No 7 day data available
5/28/2005 0 0 No 7 day data available
5/29/2005 0 0 No 7 day data available
5/30/2005 0 0 No 7 day data available
5/31/2005 0 0 No 7 day data available
6/1/2005 25.91 14.55 using data from 5/26-6/1
6/2/2005 24.52 14.08 using data from 5/27-6/2
6/3/2005 22.81 13.31 using data from 5/28-6/3
ETC........through 10/06/2005

I am no expert at access and have never used Visual Basic so please be
gentle with me. However, I am very interested in increasing my skills and
would appreciate any suggestions on how to learn VBA for access on a limited
budget.

Thanks in advance for your help.
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe it would be a query something like this: (Put this in the SQL
view of a new query).

Replace <query name> w/ the name of the query that gets the intermediate
results. Do not remove the Alias "Q" from the column designations & the
FROM clause.

Don't use Date as a column name it is also the name of a VBA function.
Anyway it is not descriptive enough - what date?

It ain't pretty, but, it works.

PARAMETERS date_start Date, date_end Date;

SELECT [Date],

IIf(DateDiff("d",date_start, [Date] ) >= 6,
(SELECT Max(Maximum_Temp) FROM <query name> WHERE [date] BETWEEN
Q.[date] -6 and Q.[date]), 0)
As [7 day hi],

IIf(DateDiff("d",date_start, [date] ) >= 6,
(SELECT Min(Minimum_temp) FROM <query name> WHERE [date] BETWEEN
Q.[date] -6 and Q.[date]), 0)
As [7 day lo],

IIf(DateDiff("d",date_start, [date] ) >= 6,
"Using data from " & Format([date]-6,"mm/dd") & "-" &
Format([date],"mm/dd"), "No 7 day data available") As Description

FROM <query name> As Q

WHERE [date] BETWEEN date_start And date_end

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9WEIoechKqOuFEgEQLV8ACgyjtoHQwB0je99PFv4yyRquSoWdwAn3wf
N01QIJoEqtONV/lIV+iBMRU2
=CJI4
-----END PGP SIGNATURE-----

I have spent the last several days looking on this site for any help for
calculating a 7 day high and 7 day low. I have found many discussions about
dates and using the current dates for calculations. I need to find the 7 day
high and low for dates that occured in the past, normally one to two years
ago.

I have a table with the following fields:

Date (Date of temperature)
Time (temp is taken every 1/2 hour)
Temp (in celcius)
Site (id number for site)
There are over 6000 records for each site in a separate table (there are 37
sites). I am mainly interested in the daily averages, minimums and maximums
so I created a query with the following fields and calculations which group
by date:

[date]
[average_temp]
[minimum_temp]
[maximum_temp]

results look like this:

DATE AVERAGE_TEMP MINIMUM_TEMP MAXIMUM_TEMP
5/26/2005 23.19 20.97 25.91
5/27/2005 20.29 18.05 24.52
5/28/2005 17.42 15.97 19.02
5/29/2005 16.16 14.71 17.73
5/30/2005 17.80 15.02 22.31
5/31/2005 17.04 15.82 19.34
6/1/2005 15.77 14.55 18.22
6/2/2005 15.78 14.08 18.37
6/3/2005 17.04 13.31 22.81

I need to gather the running seven day highs and lows from these
calculations. For this one site (there are 37 total) I have a starting date
of 5/26/2005 and ending date of 10/06/05. The dates will be different in
each site.

For example the result I need is the following:

DATE 7 day high 7 day low
5/26/2005 0 0 No 7 day data available
5/27/2005 0 0 No 7 day data available
5/28/2005 0 0 No 7 day data available
5/29/2005 0 0 No 7 day data available
5/30/2005 0 0 No 7 day data available
5/31/2005 0 0 No 7 day data available
6/1/2005 25.91 14.55 using data from 5/26-6/1
6/2/2005 24.52 14.08 using data from 5/27-6/2
6/3/2005 22.81 13.31 using data from 5/28-6/3
ETC........through 10/06/2005

I am no expert at access and have never used Visual Basic so please be
gentle with me. However, I am very interested in increasing my skills and
would appreciate any suggestions on how to learn VBA for access on a limited
budget.

Thanks in advance for your help.
Click to expand...
 
Thank you for the responses. I cannot use the "parameter" option. The users
will not know the dates prior to accessing the information. So, I am trying
John's suggestion but I must be doing something wrong. Below is the SQL I am
using: I am getting the following error:
Syntax error (missing operation) in query expression (SELECT
Max(Maximum_Temp) FROM 283290daily as MT
WHERE MT.[xDate] Between Y.[xDate]-6 and Y.[xDate]) as MaxTemp,

I changed the date field to [xdate]. my query name is "283290daily". Thanks
for your help and input.

SELECT Y.[xDate],
(SELECT Max(Maximum_Temp)
FROM 283290daily as MT
WHERE MT.[xDate] Between Y.[xDate]-6 and Y.[xDate]) as MaxTemp,
(SELECT Min(Minimum_Temp)
FROM 283290daily as MT
WHERE MT.[xDate] Between Y.[xDate]-6 and Y.[xDate]) as MinTemp
FROM 283290daily as Y
WHERE 7 = (SELECT Count(MT.[xDate])
FROM 283290daily as MT
WHERE MT.[xDate] Between Y.[xDate]-6 and Y.[xDate])--
Click to expand...
Kelley


John Spencer said:
The following MIGHT work, although I would be happier if you didn't have field
name Date, but had a field name of WxDate or some other name other than the
reserved word - Date.

SELECT Y.[Date],
(SELECT Max(Maximum_Temp)
FROM YourQuery as MT
WHERE MT.[Date] Between Y.[Date]-6 and Y.[Date]) as MaxTemp,
(SELECT Min(Minimum_Temp)
FROM YourQuery as MT
WHERE MT.[Date] Between Y.[Date]-6 and Y.[Date]) as MinTemp
FROM YourQuery as Y
WHERE 7 = (SELECT Count(MT.[Date])
FROM YourQuery as MT
WHERE MT.[Date] Between Y.[Date]-6 and Y.[Date])

That will probably be fairly slow. And I suspect you are going to post back and
say, by the way I wanted to do this per site. If so, you will need to modify
the above to include a site comparison in each of the sub-queries. That means
your master query also needs a site id in it.

I have spent the last several days looking on this site for any help for
calculating a 7 day high and 7 day low. I have found many discussions about
dates and using the current dates for calculations. I need to find the 7 day
high and low for dates that occured in the past, normally one to two years
ago.

I have a table with the following fields:

Date (Date of temperature)
Time (temp is taken every 1/2 hour)
Temp (in celcius)
Site (id number for site)
There are over 6000 records for each site in a separate table (there are 37
sites). I am mainly interested in the daily averages, minimums and maximums
so I created a query with the following fields and calculations which group
by date:

[date]
[average_temp]
[minimum_temp]
[maximum_temp]

results look like this:

DATE AVERAGE_TEMP MINIMUM_TEMP MAXIMUM_TEMP
5/26/2005 23.19 20.97 25.91
5/27/2005 20.29 18.05 24.52
5/28/2005 17.42 15.97 19.02
5/29/2005 16.16 14.71 17.73
5/30/2005 17.80 15.02 22.31
5/31/2005 17.04 15.82 19.34
6/1/2005 15.77 14.55 18.22
6/2/2005 15.78 14.08 18.37
6/3/2005 17.04 13.31 22.81

I need to gather the running seven day highs and lows from these
calculations. For this one site (there are 37 total) I have a starting date
of 5/26/2005 and ending date of 10/06/05. The dates will be different in
each site.

For example the result I need is the following:

DATE 7 day high 7 day low
5/26/2005 0 0 No 7 day data available
5/27/2005 0 0 No 7 day data available
5/28/2005 0 0 No 7 day data available
5/29/2005 0 0 No 7 day data available
5/30/2005 0 0 No 7 day data available
5/31/2005 0 0 No 7 day data available
6/1/2005 25.91 14.55 using data from 5/26-6/1
6/2/2005 24.52 14.08 using data from 5/27-6/2
6/3/2005 22.81 13.31 using data from 5/28-6/3
ETC........through 10/06/2005

I am no expert at access and have never used Visual Basic so please be
gentle with me. However, I am very interested in increasing my skills and
would appreciate any suggestions on how to learn VBA for access on a limited
budget.

Thanks in advance for your help.
Click to expand...
Click to expand...
 
Try surrounding that table name with []. Also, you can drop the [] around
xDate since it now is not a reserved word.

(SELECT Max(Maximum_Temp) FROM [283290daily] as MT
WHERE MT.xDate Between Y.xDate-6 and Y.xDate) as MaxTemp,

Try doing just the Max temp calculation and see if it works. If so, then
add in the Where clause. And then add in the MinTemp calculation.

Kelley said:
Thank you for the responses. I cannot use the "parameter" option. The
users
will not know the dates prior to accessing the information. So, I am
trying
John's suggestion but I must be doing something wrong. Below is the SQL I
am
using: I am getting the following error:
Syntax error (missing operation) in query expression (SELECT
Max(Maximum_Temp) FROM 283290daily as MT
WHERE MT.[xDate] Between Y.[xDate]-6 and Y.[xDate]) as MaxTemp,

I changed the date field to [xdate]. my query name is "283290daily".
Thanks
for your help and input.

SELECT Y.[xDate],
(SELECT Max(Maximum_Temp)
FROM 283290daily as MT
WHERE MT.[xDate] Between Y.[xDate]-6 and Y.[xDate]) as MaxTemp,
(SELECT Min(Minimum_Temp)
FROM 283290daily as MT
WHERE MT.[xDate] Between Y.[xDate]-6 and Y.[xDate]) as MinTemp
FROM 283290daily as Y
WHERE 7 = (SELECT Count(MT.[xDate])
FROM 283290daily as MT
WHERE MT.[xDate] Between Y.[xDate]-6 and Y.[xDate])--
Click to expand...
Kelley


John Spencer said:
The following MIGHT work, although I would be happier if you didn't have
field
name Date, but had a field name of WxDate or some other name other than
the
reserved word - Date.

SELECT Y.[Date],
(SELECT Max(Maximum_Temp)
FROM YourQuery as MT
WHERE MT.[Date] Between Y.[Date]-6 and Y.[Date]) as MaxTemp,
(SELECT Min(Minimum_Temp)
FROM YourQuery as MT
WHERE MT.[Date] Between Y.[Date]-6 and Y.[Date]) as MinTemp
FROM YourQuery as Y
WHERE 7 = (SELECT Count(MT.[Date])
FROM YourQuery as MT
WHERE MT.[Date] Between Y.[Date]-6 and Y.[Date])

That will probably be fairly slow. And I suspect you are going to post
back and
say, by the way I wanted to do this per site. If so, you will need to
modify
the above to include a site comparison in each of the sub-queries. That
means
your master query also needs a site id in it.

I have spent the last several days looking on this site for any help
for
calculating a 7 day high and 7 day low. I have found many discussions
about
dates and using the current dates for calculations. I need to find the
7 day
high and low for dates that occured in the past, normally one to two
years
ago.

I have a table with the following fields:

Date (Date of temperature)
Time (temp is taken every 1/2 hour)
Temp (in celcius)
Site (id number for site)
There are over 6000 records for each site in a separate table (there
are 37
sites). I am mainly interested in the daily averages, minimums and
maximums
so I created a query with the following fields and calculations which
group
by date:

[date]
[average_temp]
[minimum_temp]
[maximum_temp]

results look like this:

DATE AVERAGE_TEMP MINIMUM_TEMP MAXIMUM_TEMP
5/26/2005 23.19 20.97
25.91
5/27/2005 20.29 18.05
24.52
5/28/2005 17.42 15.97
19.02
5/29/2005 16.16 14.71
17.73
5/30/2005 17.80 15.02
22.31
5/31/2005 17.04 15.82
19.34
6/1/2005 15.77 14.55
18.22
6/2/2005 15.78 14.08
18.37
6/3/2005 17.04 13.31
22.81

I need to gather the running seven day highs and lows from these
calculations. For this one site (there are 37 total) I have a starting
date
of 5/26/2005 and ending date of 10/06/05. The dates will be different
in
each site.

For example the result I need is the following:

DATE 7 day high 7 day low
5/26/2005 0 0 No 7 day data
available
5/27/2005 0 0 No 7 day data
available
5/28/2005 0 0 No 7 day data
available
5/29/2005 0 0 No 7 day data
available
5/30/2005 0 0 No 7 day data
available
5/31/2005 0 0 No 7 day data
available
6/1/2005 25.91 14.55 using data from 5/26-6/1
6/2/2005 24.52 14.08 using data from 5/27-6/2
6/3/2005 22.81 13.31 using data from 5/28-6/3
ETC........through 10/06/2005

I am no expert at access and have never used Visual Basic so please be
gentle with me. However, I am very interested in increasing my skills
and
would appreciate any suggestions on how to learn VBA for access on a
limited
budget.

Thanks in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 
This worked perfectly. Thank you so much for your help!
--
Kelley


John Spencer said:
Try surrounding that table name with []. Also, you can drop the [] around
xDate since it now is not a reserved word.

(SELECT Max(Maximum_Temp) FROM [283290daily] as MT
WHERE MT.xDate Between Y.xDate-6 and Y.xDate) as MaxTemp,

Try doing just the Max temp calculation and see if it works. If so, then
add in the Where clause. And then add in the MinTemp calculation.

Kelley said:
Thank you for the responses. I cannot use the "parameter" option. The
users
will not know the dates prior to accessing the information. So, I am
trying
John's suggestion but I must be doing something wrong. Below is the SQL I
am
using: I am getting the following error:
Syntax error (missing operation) in query expression (SELECT
Max(Maximum_Temp) FROM 283290daily as MT
WHERE MT.[xDate] Between Y.[xDate]-6 and Y.[xDate]) as MaxTemp,

I changed the date field to [xdate]. my query name is "283290daily".
Thanks
for your help and input.

SELECT Y.[xDate],
(SELECT Max(Maximum_Temp)
FROM 283290daily as MT
WHERE MT.[xDate] Between Y.[xDate]-6 and Y.[xDate]) as MaxTemp,
(SELECT Min(Minimum_Temp)
FROM 283290daily as MT
WHERE MT.[xDate] Between Y.[xDate]-6 and Y.[xDate]) as MinTemp
FROM 283290daily as Y
WHERE 7 = (SELECT Count(MT.[xDate])
FROM 283290daily as MT
WHERE MT.[xDate] Between Y.[xDate]-6 and Y.[xDate])--
Click to expand...
Kelley


John Spencer said:
The following MIGHT work, although I would be happier if you didn't have
field
name Date, but had a field name of WxDate or some other name other than
the
reserved word - Date.

SELECT Y.[Date],
(SELECT Max(Maximum_Temp)
FROM YourQuery as MT
WHERE MT.[Date] Between Y.[Date]-6 and Y.[Date]) as MaxTemp,
(SELECT Min(Minimum_Temp)
FROM YourQuery as MT
WHERE MT.[Date] Between Y.[Date]-6 and Y.[Date]) as MinTemp
FROM YourQuery as Y
WHERE 7 = (SELECT Count(MT.[Date])
FROM YourQuery as MT
WHERE MT.[Date] Between Y.[Date]-6 and Y.[Date])

That will probably be fairly slow. And I suspect you are going to post
back and
say, by the way I wanted to do this per site. If so, you will need to
modify
the above to include a site comparison in each of the sub-queries. That
means
your master query also needs a site id in it.


Kelley wrote:

I have spent the last several days looking on this site for any help
for
calculating a 7 day high and 7 day low. I have found many discussions
about
dates and using the current dates for calculations. I need to find the
7 day
high and low for dates that occured in the past, normally one to two
years
ago.

I have a table with the following fields:

Date (Date of temperature)
Time (temp is taken every 1/2 hour)
Temp (in celcius)
Site (id number for site)
There are over 6000 records for each site in a separate table (there
are 37
sites). I am mainly interested in the daily averages, minimums and
maximums
so I created a query with the following fields and calculations which
group
by date:

[date]
[average_temp]
[minimum_temp]
[maximum_temp]

results look like this:

DATE AVERAGE_TEMP MINIMUM_TEMP MAXIMUM_TEMP
5/26/2005 23.19 20.97
25.91
5/27/2005 20.29 18.05
24.52
5/28/2005 17.42 15.97
19.02
5/29/2005 16.16 14.71
17.73
5/30/2005 17.80 15.02
22.31
5/31/2005 17.04 15.82
19.34
6/1/2005 15.77 14.55
18.22
6/2/2005 15.78 14.08
18.37
6/3/2005 17.04 13.31
22.81

I need to gather the running seven day highs and lows from these
calculations. For this one site (there are 37 total) I have a starting
date
of 5/26/2005 and ending date of 10/06/05. The dates will be different
in
each site.

For example the result I need is the following:

DATE 7 day high 7 day low
5/26/2005 0 0 No 7 day data
available
5/27/2005 0 0 No 7 day data
available
5/28/2005 0 0 No 7 day data
available
5/29/2005 0 0 No 7 day data
available
5/30/2005 0 0 No 7 day data
available
5/31/2005 0 0 No 7 day data
available
6/1/2005 25.91 14.55 using data from 5/26-6/1
6/2/2005 24.52 14.08 using data from 5/27-6/2
6/3/2005 22.81 13.31 using data from 5/28-6/3
ETC........through 10/06/2005

I am no expert at access and have never used Visual Basic so please be
gentle with me. However, I am very interested in increasing my skills
and
would appreciate any suggestions on how to learn VBA for access on a
limited
budget.

Thanks in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Date Help W/Avg work days 7
Days between calculation 1
booking query 2 1
calculated field - difficult 11
Calculating the days in a month 3
Looping through data with calculations 1
Filter for Day of the Week 4
Compute change in Fields from Previous day 1

Back
Top