Taking the sum between two dates

  • Thread starter Thread starter Ryan Fisher
  • Start date Start date
R

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!
 
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!
Click to expand...

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
 
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!
Click to expand...

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
Click to expand...

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
 
Back
Top