[AccessD] A database problem....looking for a solution

Another idea (and I appreciate the feedback):

Have a status/indicator column: 1 char; values=O,C,A
(Original,Consolidated,Amendment)
A newly entered contract defaults to "O" (Original)
When an amendment is added, the Original row gets cloned, the amendment
changes are made, and the status is set to "C" for consolidated. Subsequent
amendments get added as new rows, and a process is run which "folds" into
the consolidated row.
The original row gets a new status of "R" (revised). There are now 3 rows
for that contract: Revised,Amendment, and Consolidated.

All queries for summary reporting have a where clause: WHERE STATUS IN
("O","C")

In that manner, original, UNREVISED contracts and CONSOLIDATED contracts get
reported....
Yet the details are still in the database.

YES THIS IS VERY WASTEFULL IN TERMS OF DATABASE SPACE USAGE.
However, it doesn't require a temp table...all of the work is done at the
time of update.
Thoughts ?

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
> Sent: Wednesday, November 24, 2010 11:57 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] A database problem....looking for a solution
>
> Mark,
>
>   I have seen these types of systems (and designed a few like
> this), but usually they:
>
> 1. Go in the other direction - Current record is the latest
> and changes occurred in the past.
> 2. The "log" is usually one changed field per record, not the
> complete record with only changed columns.
>
>   So with that approach, if you need to go back, you take the
> current row, then do a series of one field updates until you
> reach some point in the past.  You then have a view of a
> record at a given point in time.
>
>   That approach is used a lot in HR and medical applications.
>   I've also
> seen this done with a version # type thing; every change to a
> record creates a new record with an incremented version
> number. You can then compare field by field between any two
> versions to see the differences.
>
>   As far as your current setup, I can't see anyway either
> that this would be easily doable with SQL alone.  As AD and
> Paul have pointed out, a temp table along with code in some
> form (VBA, SP, etc) is going to be required to build the view
> of the record.
>
>   But that begs the question; how does it do that now and
> knowing that there is not a good method of doing this with
> SQL, why was it designed like that in the first place?
>
>   I always ask myself the latter because it was either done
> through lack of knowledge on their part, or lack of
> understanding on my part.
>
> Jim.
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Simms
> Sent: Tuesday, November 23, 2010 08:19 PM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] A database problem....looking for a solution
>
> A row is inserted with contract data - 100 columns are populated.
> 6 months later, an "amended" row is inserted  - 10 columns
> are changed to reflect updated data; the other 90 columns are null.
> 4 months after that, another 4 columns are changed and
> another "amended" row is inserted.
>
> So essentially, there is a base row, and then update
> rows....similar to a log.
> I see no easy way with SQL to report A SINGLE ROW with all
> columns that reflects the columns changed in the amended rows.
> However, inserting the first row into a temp table and then
> conditionally updating using the amended rows, and then
> reporting from the temp table seems like one way to go.
>
> Any other suggestions ?
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>