How do I update date related data, while discarding the oldest?

F

Fil70

I am designing my first Access database (it's for storing germination results
on seed lots). I would like to update a record every 6 months or so. I want
to keep the previous entry, but to discard the entry from 12 months ago, so
the results shuffle down one, the oldest entry being discarded. Does this
make any sense to anyone?
If so, is this possible using Access. Any help would be greatly appreciated.
 
V

Vincent Johns

Fil70 said:
I am designing my first Access database (it's for storing germination results
on seed lots). I would like to update a record every 6 months or so. I want
to keep the previous entry, but to discard the entry from 12 months ago, so
the results shuffle down one, the oldest entry being discarded. Does this
make any sense to anyone?
If so, is this possible using Access. Any help would be greatly appreciated.
Click to expand...

You can do this via a Delete Query that would erase any records older
than 12 months. However, unless your database is likely to become
inconveniently large, you can get much the same effect (without erasing
anything) by displaying your current results via Queries that filter out
old records. The old records would still be in the Tables, but
invisible. That way, if you later decided you would like to look at the
older values, they'd be retrievable without going to your backup files.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
E

Ed Warren

It's possible to do what you want, but why? You can keep all your data for
each seed lot but only use the selected rows for any reports, analysis you
want to do.
You would want to start with at least the following tables.

SeedLots:
SeedLotID (primary Key)
SeedLotDescription
(other stuff needed about the seedlot)

GerminationResults
GerminationResultsID (primaryKey)
SeedLotID (foreign Key -->refers back to the SeedLots table in a 1:M
relationship)
GerminationDate
GerminationResults
(other stuff about the Germination observation)

When you query the data you will want to : For each SeedLot select the top
two rows from GerminationResults based on GerminationDate.

Ed Warren.
 
Top