Taking the sum between two dates

[Ryan Fisher]

Hi there,
I have two tables in Access. The first table contains information on
visits to bird's nests. It contains fields: [VisitID], [NestID],
[WeatherStationID],[DateoftheVisit],[DateofPreviousVisit]. [VisitID]
is the identification code for the particular visit (autonumber field),
[NestId] is the identification code given to the particular nest,
[WeatherStationID] is the closest weather station, [DateoftheVisit] is
the date of the current visit (mm/dd/yyyy), and [DateofPreviousVisit]
(mm/dd/yyyy) is the date of the previous visit to the nest. The second
table contains information on daily precipitation from various weather
stations, so this table has three fields: [WeatherStationID], [Date]
(mm/dd/yyyy), and [Precipitation]. What I would like to do is get a
sum of precipitation between [DateofVisit] and [DateofPreviousVisit]
for each nest for the weather station that is closest to it. For
example, if a visit to a nest was conducted on 06/01/2004 and then on
06/05/2005, I would like to get a sum of precipitation for 06/01,
06/02, 06/03, 06/04, and 06/05 from the closest weather station (e.g.,
if precip was 5, 10, 1, 0, and 2, then my sum would be 18 for that
particular nest and visit). I hope I've included enough information
and in the correct format...kind of a newbie here.
Thanks!

 

[John W. Vinson]

Hi there,
I have two tables in Access. The first table contains information on
visits to bird's nests. It contains fields: [VisitID], [NestID],
[WeatherStationID],[DateoftheVisit],[DateofPreviousVisit]. [VisitID]
is the identification code for the particular visit (autonumber field),
[NestId] is the identification code given to the particular nest,
[WeatherStationID] is the closest weather station, [DateoftheVisit] is
the date of the current visit (mm/dd/yyyy), and [DateofPreviousVisit]
(mm/dd/yyyy) is the date of the previous visit to the nest. The second
table contains information on daily precipitation from various weather
stations, so this table has three fields: [WeatherStationID], [Date]
(mm/dd/yyyy), and [Precipitation]. What I would like to do is get a
sum of precipitation between [DateofVisit] and [DateofPreviousVisit]
for each nest for the weather station that is closest to it. For
example, if a visit to a nest was conducted on 06/01/2004 and then on
06/05/2005, I would like to get a sum of precipitation for 06/01,
06/02, 06/03, 06/04, and 06/05 from the closest weather station (e.g.,
if precip was 5, 10, 1, 0, and 2, then my sum would be 18 for that
particular nest and visit). I hope I've included enough information
and in the correct format...kind of a newbie here.
Thanks!

I'm concerned about the redundancy of the date fields: the DateOfPreviousVisit
should already be in the table in another record, right? Storing it twice
opens the door for errors. You may want to consider a slightly more complex
query looking up the maximum previous date for this nest.

That said, as your table is currently designed, you can get the desired total
using a Totals query with a join. Create a new Query adding Visits and Weather
to the query grid. Initially, join the [Dateofthevisit] field to the [Date]
field in the weather table, and also join the WeatherStationID fields.

Open the query in SQL view and edit it so that instead of

.... ON [Visits].[Dateofthevisit] = [Weather].[Date]...

it reads

.... ON [Visits].[DateOfPreviousVisit] <= [Weather].[Date] AND
[Visits].[DateOfTheVisit] >= [Weather].[Date]

This "non equi join" will retrieve records where the date of the weather
record is between the two dates.

Do note that Date is a reserved word and a bad choice of fieldname - Access
can and will get it confused with the builtin Date() function. Consider
renaming it to PrecipDate or something else appropriate.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Ryan Fisher]

Hi there,
I have two tables in Access. The first table contains information on
visits to bird's nests. It contains fields: [VisitID], [NestID],
[WeatherStationID],[DateoftheVisit],[DateofPreviousVisit]. [VisitID]
is the identification code for the particular visit (autonumber field),
[NestId] is the identification code given to the particular nest,
[WeatherStationID] is the closest weather station, [DateoftheVisit] is
the date of the current visit (mm/dd/yyyy), and [DateofPreviousVisit]
(mm/dd/yyyy) is the date of the previous visit to the nest. The second
table contains information on daily precipitation from various weather
stations, so this table has three fields: [WeatherStationID], [Date]
(mm/dd/yyyy), and [Precipitation]. What I would like to do is get a
sum of precipitation between [DateofVisit] and [DateofPreviousVisit]
for each nest for the weather station that is closest to it. For
example, if a visit to a nest was conducted on 06/01/2004 and then on
06/05/2005, I would like to get a sum of precipitation for 06/01,
06/02, 06/03, 06/04, and 06/05 from the closest weather station (e.g.,
if precip was 5, 10, 1, 0, and 2, then my sum would be 18 for that
particular nest and visit). I hope I've included enough information
and in the correct format...kind of a newbie here.
Thanks!

I'm concerned about the redundancy of the date fields: the DateOfPreviousVisit
should already be in the table in another record, right? Storing it twice
opens the door for errors. You may want to consider a slightly more complex
query looking up the maximum previous date for this nest.

That said, as your table is currently designed, you can get the desired total
using a Totals query with a join. Create a new Query adding Visits and Weather
to the query grid. Initially, join the [Dateofthevisit] field to the [Date]
field in the weather table, and also join the WeatherStationID fields.

Open the query in SQL view and edit it so that instead of

... ON [Visits].[Dateofthevisit] = [Weather].[Date]...

it reads

... ON [Visits].[DateOfPreviousVisit] <= [Weather].[Date] AND
[Visits].[DateOfTheVisit] >= [Weather].[Date]

This "non equi join" will retrieve records where the date of the weather
record is between the two dates.

Do note that Date is a reserved word and a bad choice of fieldname - Access
can and will get it confused with the builtin Date() function. Consider
renaming it to PrecipDate or something else appropriate.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hi John,
Thanks very much that worked perfectly (and saved me a substantial
headache!). Yes the field of previous visit was calculated just so I
can get the interval between the current and previous visit for each
nest. I wasn't sure if it might have been helpful for this query so I
left it in. But you are correct in that it is redundant because that
information is stored in another record.
Thanks so much for the help. Much appreciated
Ryan