append query for vals that don't exist (1 Viewer)

[aphelps]

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.

 

[GumbyD]

Adam -

I see what you are trying to do, but I am not sure if the extra records are really necessary. If you create a query the groups by nest and then get the max on the year, won't that give you the last year the nest was used? Then you can just criteria for <2000 for this year or <2001 for next year?

GumbyD

 

[aphelps]

Yes, yes, of course I can. I only need to ensure that there are no empty records for years with no data in there, or else they'd be counted as active years.

I figured that there'd be a dead-easy way to do it, too. It's the options in the "Total" row in the query builder, I never remember that they are there. Thanks...