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

Sounds like a job for subqueries....

However, just as a personal suggestion, the initial design for such a
system I would get away from one big 100 column table.

I would recommend a table structure like this:

tblContractColumns:
ColumnID (autonumber/primary key)
ColumnName

tblContractHeader:
ContractID (autonumber/primary key)
DateCreated
Any other fields which are unique to a contract

tblContractDataEntry:
ContractEntryID (Autonumber/Primary Key)
ContractID (Foreign Key)
DateEntered (Date/Time)

tblContractData:
ContractEntryID (foreign key)
ColumnID (foreign key)
Data

Whalla, never a wasted field.

However, to use your existing 100 table, a subquery in each column can
be used to show the field with the latest value, as long as you have a
date/time field.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Simms
Sent: Tuesday, November 23, 2010 7: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
The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.