Jim Lawrence
accessd at shaw.ca
Tue Mar 25 14:57:03 CDT 2014
Hi Bill: Keeping date and time fields can be very useful. Traditionally, I use a single character to signify a records progress or activity state. This allows records set certain ways to be queried out very quickly. I also like to archive records, through over-night processes, that have been completed, for what ever reason, into separate tables. It all keeps the working tables lean and fast....and if your system has a client across the counter every second counts. The downside is that summary reporting, like month-end accounts, can be slower, as records must be pulled and assembled from multiple tables but a few extra seconds are then irrelevant. Jim ----- Original Message ----- From: "Bill Benson" <bensonforums at gmail.com> To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com> Sent: Tuesday, March 25, 2014 7:27:59 AM Subject: Re: [AccessD] Disconnected records - was Re: Unbound Form Check For Changes <<records should never be deleted and should <<only be toggled/flagged active or inactive. I know the toggle /flag suffices usually but I like knowing WHEN a record became inactive (or got reactivated). Since there is enough info in these date fields to infer whether a record is active, I eliminated the IsActive flag. I started using Last Made Active Date and Last Made Inactive Date fields in entity tables. To determine if a record is active it has to satisfy this If Nz (LMID,#1/1/1900#) < LMAD. The LMAD can work as a CREATE date, if willing to let that be overridden upon reactivation. I chose to have both LMAD and a Create date. Can't have too many dates, I had none in high school. I don't know whether that test for active records slows down the database but it never seemed to make a difference in systems I was working on. I think another option would be logging tables, but I was not that interested nor was the client, we were building a prototype. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com