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

Drew - that is the proper design of such a system. Unfortunately, I
"inherited" the current "mess".
I would have built a TemplateTable that has all of the possible contract
"terms"....and build the contracts from the terms.
Instead of a static flat table, it would be dynamic with a single entry for
a single term.
The current design must accommodate the worst-case-scenario....all contract
terms.

I've scoured the industry and experts in the field: there are no OTS
"canned" solutions....
Likely because of the high cost of developing such a complex system that
must handle so many nuances and variations. Imagine the work involved in
designing a tool which can craft a contract report that requires exact
placement of each of the terms. A pivot query would be needed to "flatten"
the structure to permit a form-like report to be developed. And so on and so
forth.


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
> Sent: Wednesday, November 24, 2010 3:26 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [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