How to Build and Interact with a Database of Contract Terms to Build New Contract?

Thank you for the insight. My library isn't quite that large, but I expect by the end of my career, it could be in that ballpark. You've given me some good things to consider.

I currently use Mail Merge to populate pieces of an agreement such as the names of the parties, the closing date, etc., but Mail Merge doesn't seem to be able to help me with what I've described above. Thank you.

Hi DLG,

I would not choose for a search through a large number of contracts.

Instead, I would use a number of standard paragraphs, stored in the Paragraph_tbl, of which can be selected depending on the specifics of a contract contract. If necessary, paragraphs could be more dynamical with "tokens" or parameters, to add more flexibility.

In case of new situations not yet covered, just add a new paragraph to Paragraph_tbl,

Probably this can be realized with subreports.

I don't use Access reports, because in my opinion they are too static (or my experience with them is too low).

I generate my "reports" directly as external file using RTF- of HTML-code, with all the flexibilty that I want to have. The advantage is that the "code" is readable, and can be created by relative simple string manipulations.

Imb.

I did something similar a while back in Access. The key is identifying boilerplate clauses that can be used to make up a contract. You can then build a report that incorporates, not only the static boilerplate clauses, but clauses that supply info about the parties involved and info specific to the contract (like amounts, etc).

But it is an ambitious project. that I would not take lightly.

I agree with Tom that a fully developed database would demand the services of an experienced professional developer, but you might be able to build a less functional but workable tool more easily if the volume of contracts is manageable.  In broad outline the model would comprise the following tables:

1. Contracts:- This would have one row for each contract in the library, and would have columns to represent the attributes which are specifically those of contracts per as.  In database terms these would be said to be 'functionally determined solely by the whole of the table's primary key'.  Contract date would be an example.
2. Contract Types:- This would either be table referenced by a foreign key column in the Contracts table, i.e. the relationship type between Contracts and ContractTypes would be one-to-many (unary); or, if the relationship type were to be many-to-many (binary) would be modelled by a third table which resolves the binary relationship type into two unary relationship types by having, as its composite primary key, two foreign key columns referencing the primary keys of Contracts and ContractTypes respectively.  This third table could also include non-key columns representing attributes of the relationship type.
3. Clauses:- This would be in a unary relationship type with Contracts, so would include a ContractID foreign key column referencing the primary key of Contracts.  The text of the clause would be of Long Text data type (previously known as 'memo' fields).  The table might also include other columns representing other attributes of the clause, e.g. ClauseTypeID referencing the key of a ClauseTypes table, or, if a clause might be of more than one type, you could model a binary relationship type with a ClauseTypes table by an additional table as described above in the case of ContractTypes.

The model as described above would not be dissimilar than that put forward by Tom, but if the classification of clauses as clause types were to be omitted to keep the amount of time building the database manageable, it would be possible to search for specific words or phrases in clauses without their being indexed.  I would envisage a search being undertaken by first selecting one or more contract types in multi-select list box, and then searching for one or more phrases and/or words in the thus restricted number of contracts. Performance would of course be enhanced if the number of clause to be searched could be restricted by first selecting one or more clause types.

To illustrate how you would search for words or phrases take a look at FindWords.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file uses T S Eliot's famous poem The Waste Land as its example.  The poem has five sections, and in my demo I have made each line a separate row (434 in total) in a table. I imported the poem as a text file and then inserted the lines into the Access table by means of a set of 'append' queries.  The demo includes, in addition to the main FindWord function, a couple of auxiliary functions to allow searches for multiple words or phrases:

 ![](https://learn-attachment.microsoft.com/api/attachments/a4fb8eed-74ae-47b8-8343-769f8ec8a6ae?platform=QnA

Interesting project.

You did not say how many contracts you have access to, but for sake of discussion I'm guessing 10,000 10-page documents.

There are several parts to this:

1. Ingesting the contracts into a database.
2. Breaking up each contract into "clauses", and indexing them
3. Finding relevant clauses to be considered for the next contract
4. Inserting clauses into the document

Re 1, I think importing into SQL Server is going to provide many benefits, so that will be my back-end database, while Access will be my front-end. Full Text Indexing will be enabled. This gives us superior querying to what Access can do (e.g. see Full-Text Search - SQL Server | Microsoft Learn).

Re 2, the traditional way is to have a human do this: bring up the next doc, indicate each clause, and enter keywords to index them. With 100K pages, that will take a long time. If we pay someone for a year, that will require 100k/2000 = 50 pages per hour, one page per minute. That feels tight but in the ballpark.

A tantalizing idea would be to use AI for this. Large Language Models are good at text processing. This is the bleeding edge of programming, but I would not discard it just yet.

Re 3, we can use the keywords to find the clauses, and we can use Full Text Index searches as well to find text in the contracts. Then a scoring algorithm would present them ranked by relevancy.

Re 4, using OLE Automation to interact with a Word document (better: the Word Object Model) to insert clauses is pretty straightforward, and more powerful than Mail-Merge which is really solving a different problem.

All in all, not something I would expect you could do on your own. This requires a seasoned professional developer.