Duplicate table and run select query from new table with one click

[Don]

I have a 2006 inventory table. In 2007, I'd like my user to be able to
click a button which duplicates that table and then queries the new
table so that he can edit the select records that meet a certain
criteria. That new updated table would then be the 2007 inventory
table. Is this possible? I would have to make sure that the button's
event updates the query's row source to the new table? Or, filter the
new table with a predefined query that meets the criteria for the
records to be updated?

 

[Jeff Boyce]

Don

Are you saying that your database would/will have separate tables for
2006_Inventory, 2007_Inventory, ... 2099_Inventory? Why?

When you start embedding data in fieldnames (i.e., 'repeating fields') or in
tablenames (?'repeating tables' - same structure, another 'year'), you are
trying to turn Access into a spreadsheet.

To get the best use of Access' features and functions, you need your data
well-normalized.

Would it work in your situation (I can't tell, you didn't really describe
it) to have a single inventory table, and one extra field for
"InventoryYear"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Don]

Sorry. I'll be more detailed.

My boss in responsible for 5000 lots of forest (lots 1-5000), each with
specific characteristics like dominant species, density, etc.. He just
completed an inventory of all the lots and has that data in a table.
Next year, and every year afterwards, he will update his inventory to
reflect timber harvests.

He "wants to be able to click a button and have Access tell him which
records need to be updated from last year's inventory" based on whether
or not they have been harvested.

So, I was thinking that the current inventory would contain a checkbox
field that is checked after the lot is harvested. In January, he can
"click his button" and last year's inventory table would be copied,
renamed this year's inventory and those fields that have a check in the
checkbox would open for editing.

 

[Don]

Sorry again. I forgot to mention that he wants all the data from the
previous years to be kept not updated and lost.

 

[Don]

OK. So how do I create a database that is capable of entering, storing,
querying and reporting data that may or may not change yearly while
keeping all previous data when updated?

Don

 

[Jeff Boyce]

Don

If I'm understanding correctly, you are looking for a way to keep specific
information about each "lot", as well as a "history" of each lot.

Does everything about the lot change when it is "harvested"? If so, then
your information detail and "history" are all in one record, perhaps
including fields like:

LotHistory
LotHistoryID (primary key)
LotID (Foreign key, pointing to Lot table)
DateOfAssessment (can't be empty - this is why/when the History
record is created)
DateOfHarvest (could be empty)
DominantSpeciesID (Foreign key, pointing to Species table)
Density
...

I'm sure I'm missing a lot (I'm not there, I can't see what you're working
with), but a table like this would give you a way to create queries to find,
for any lot, all the Assessment Dates, or the "last" (i.e., most recent)
Harvest Date, or how long it has been since a specific lot was harvested, or
....

Does that get closer to what you're trying to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP