[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