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

Mark,

The temp table is the way I would probably go, or you could (I think) as
long as the table row of contract data has a unique identifier and all
columns are null unless have been updated, could you do something like
below:

SELECT UniqueID, MAX(Field1) AS F1, MAX(Field2) AS F2 (etc etc)
FROM YourContractTable
GROUP BY UniqueID

Or there is another way (he thinks again) but would be using a stored
procedure and unless you already have one a insert date field with the date
time that the record was inserted.  But the only way I know how to do that
is with the use of the #TempTable and a cursor, so not sure if that would be
an ideal solution

Paul
On 24 November 2010 01:18, Mark Simms <marksimms at verizon.net> wrote:

> 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
>



-- 
Paul Hartland
paul.hartland at googlemail.com