First, let me say that I'm brand new to Access, so please assume I know
nothing. I've got a table (210k records) of deer harvest information.
Date of harvest is one of many variables in the table. Since our
season runs from the first SAT in October to 31 January, valid harvest
dates for 2005-06 were 4 Oct 2005 to 31 Jan 2006. Many records will
have missing or dates outside of this range. Date is needed to assign
each dead deer to a season (Gun, Archery, Muzzleloader, etc). For some
seasons, like Gun, season can be had from WEAPON TYPE, which is one of
the variables on the form. This is possible because there is only one
season where a shotgun is allowed. Problem is, the bow can be used in
3 seasons, so weapon type doesn't tell you what season it should be in.
Long story short, I ultimately want to assign each record a valid date
value. In the past, I've used SAS for this purpose. I do this in two
steps. First I create a variable that identifies whether or not the
date if valid. Then I use a retain statement which holds the value of
date from the previous record and assigns it to the current record when
the date flag is "invalid." In other words, the date from the most
recent valid date is retain and dumped into those records with invalid
dates. This continues until the flag switches to valid, at which time
the retain value is updated. I wont bore you with the details as to
why this is a reasonable approach, but if you're interested, let me
know.
So, you guessed it, can someone tell me how to use an update query to
accomplish this?
I would really appreciate any help you might be able to offer.
nothing. I've got a table (210k records) of deer harvest information.
Date of harvest is one of many variables in the table. Since our
season runs from the first SAT in October to 31 January, valid harvest
dates for 2005-06 were 4 Oct 2005 to 31 Jan 2006. Many records will
have missing or dates outside of this range. Date is needed to assign
each dead deer to a season (Gun, Archery, Muzzleloader, etc). For some
seasons, like Gun, season can be had from WEAPON TYPE, which is one of
the variables on the form. This is possible because there is only one
season where a shotgun is allowed. Problem is, the bow can be used in
3 seasons, so weapon type doesn't tell you what season it should be in.
Long story short, I ultimately want to assign each record a valid date
value. In the past, I've used SAS for this purpose. I do this in two
steps. First I create a variable that identifies whether or not the
date if valid. Then I use a retain statement which holds the value of
date from the previous record and assigns it to the current record when
the date flag is "invalid." In other words, the date from the most
recent valid date is retain and dumped into those records with invalid
dates. This continues until the flag switches to valid, at which time
the retain value is updated. I wont bore you with the details as to
why this is a reasonable approach, but if you're interested, let me
know.
So, you guessed it, can someone tell me how to use an update query to
accomplish this?
I would really appreciate any help you might be able to offer.