aphelps
Bird Man
- Local time
- Today, 02:36
- Joined
- Aug 2, 2002
- Messages
- 32
Hiya--
I have a table to which I want to append many records. Table looks like this: NestNumber (txt), year, occupied (Y/N), Active (Y/N), nest result (int). So a record might look like:
AC0002, 2000, Y, Y, 210
For a given nest, there can be multiple records, but only one record per nest per year. As it stands now, when a nest is not observed to be occupied or active in a given year, there is no record for that nest. That is, 2000 may be the last year that AC0002 had eagles, but it was checked in subsequent years, the negative data were just not recorded.
What I'd like to be able to do is to append to the table three empty records for the years following the last record for each nest, so:
AC0002, 2000, Y, Y, 210 I'd like to add the next three:
AC0002, 2001, N, N, 133
AC0002, 2002, N, N, 133
AC0002, 2003, N, N, 133
Of course, each nest could have a different last year recorded, so the query would have to determine the last year for each nest in the table, then add three years' worth of records for each nest as above.
The ultimate reason is that I need to be able to find out which nests have been unoccupied and inactive for three continuous years, because that determines their legal status. I've done them all for this year but going through each nest's records, but I don't particularly want to have to do that every year.
As always, thanks for any help you can throw my way! I'm SQL-ignorant but willing to learn, and VBA doesn't scare me...usually.
I have a table to which I want to append many records. Table looks like this: NestNumber (txt), year, occupied (Y/N), Active (Y/N), nest result (int). So a record might look like:
AC0002, 2000, Y, Y, 210
For a given nest, there can be multiple records, but only one record per nest per year. As it stands now, when a nest is not observed to be occupied or active in a given year, there is no record for that nest. That is, 2000 may be the last year that AC0002 had eagles, but it was checked in subsequent years, the negative data were just not recorded.
What I'd like to be able to do is to append to the table three empty records for the years following the last record for each nest, so:
AC0002, 2000, Y, Y, 210 I'd like to add the next three:
AC0002, 2001, N, N, 133
AC0002, 2002, N, N, 133
AC0002, 2003, N, N, 133
Of course, each nest could have a different last year recorded, so the query would have to determine the last year for each nest in the table, then add three years' worth of records for each nest as above.
The ultimate reason is that I need to be able to find out which nests have been unoccupied and inactive for three continuous years, because that determines their legal status. I've done them all for this year but going through each nest's records, but I don't particularly want to have to do that every year.
As always, thanks for any help you can throw my way! I'm SQL-ignorant but willing to learn, and VBA doesn't scare me...usually.