Best way to handle Inactive Records ?

[Robert]

We have developed a database that contains all equipment we lease out.

There is a new requirement - To identify which lease has been expired BUT
still want to keep those historical data.
Is the best way to add a field to show which lease has been expired. I
think that I can add a checkbox to indicate which lease has been expired and
which one is still active in the data entry form. How should I handle the
report and query ? Should I create another set of report and query for end
user ?

Thanks

 

[Arvin Meyer [MVP]]

It really depends upon your active use of the data. If you never really need
to see it, you can back up the entire database, then use a copy with the
deleted information. An Archive Boolean field also works well if you don't
mind re-writing all your queries behind the forms and reports to add the
where clause (Where Archived = False). Unless you really need to see the
data often, a whole new set of forms and reports seems superfluous.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Amy Blankenship]

You' probably be better off adding an expiration date and then checking if
you're past it. Then the record only has to be visited once as far as data
entry goes.

HTH;

Amy

 

[Robert]

I have talked to End User and clarified the requirement:

He wants to keep a history of the lease of equipment. In other words, now
Equipment A is leased to Customer A now BUT he would like to keep the last
lease history - Equipment A is leased to Customer C last year.

He would like to search the record by Equipment ID and a button will show
the history. Is it a good idea to use "Subform" and "Subreport" in this
case ?

Thanks

 

[Arvin Meyer [MVP]]

The equipment belongs in one table, and the lease itself belongs in another
One (equipment) to many (leases) and yes a subform would display the leases
adequately. A rough schema might look like:

tbEquipment
EquipmentID - Primary Key - Long Integer (probably an autonumber)
EquipmentName - Text
EquipmentDescription - Text
EquipmentPurchaseDate - Date\Time
etc.

tblLeases
LeaseID - Primary Key - Long Integer
EquipmentID - Foreign Key - Long Integer
Leasee - Text
LeaseDate - Date\Time
etc.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access