Feasability of creating lengthy contracts from Access as Excel spreadsheets.

[Kronix]

I sell big machines and the offers and orders are several pages long, containing numbers, specifications, and legal information that can change depending on the type of machine sold and the customization options that are chosen.

I want to store our calculations, offers, and orders in an Access database, and then press a button to create the contract in Excel (most likely based on a template) and save it with a unique name, to be printed out and presented to the customer to sign. (I previously used Word to create the contracts, replacing everything in old contracts by hand, but I think switching to Excel will make it more organized and easier to manipulate with Access/VBA.) Numbers, model names, paragraphs, sentences, clauses, cosmetic tables and of course customer details can change (or be deleted) depending on what is required by the item being sold and who it is being sold to.

I am sure I will have to use lots of VBA code to accomplish this, and before I embark on this journey I want to get you folks' input on any barriers or difficulties I might encounter, and what I should consider to make sure that what I am doing is feasable in the long term. I am fairly confident about the storing of data on the Access side -- I think it's very useful since certain technical and legal clauses that must be noted in the contract depending on the specifications can be represented by table relationships. Transferring it into many different parts of the contract is what concerns me most.

Next to the typical tables that are used for organizing information in Access, I must also create tables for each paragraph or section in the contract to decide which strings of text should be included. I estimate I could have over 100 tables in the database when I am done.

 

[Gasman]

Why you would want to put it into Excel, when you have everything in Access?

 

[plog]

Agree with Gasman. No need to shuffle data between programs when you can use a Report object in Access to generate any printables this thing may generate.

Also, and its a big one...

I estimate I could have over 100 tables in the database when I am done.

I think your inexperience with databases in general will be the major hinderance. That in no way can be the correct amount of tables for your (and most likely any) database. I don't know how you envision these tables, but I can say with certainty that you are not familiar with normalization.

Google and read up on that (https://en.wikipedia.org/wiki/Database_normalization). It's the process of setting up your tables/fields correctly to hold the data you want to store. That is the first and most important step in any database.

 

[Kronix]

I want it in Excel in case I encounter something I have to edit manually. Can't do that with a report, at least not as easily. (?)

Over 100 tables, mainly small ones, containing alternate strings of text to build a paragraph. Each paragraph would have about 3 tables related to each other...one concerning the part being ordered and its standard values, a second containing customizable measurements for that part, and a third containing optional legal notes for the chosen part/measurements, or where it is being built or used. Perhaps 20 paragraphs. That is a rough estimate, though. I know about normalization. This is like a tiny database for each paragraph.

Is there a performance issue/limitation with Access? The only link between the 100 tables would be to the order/offer ID, most of them only being used when generating the contract.

 

[Uncle Gizmo]

NO! it's not a performance issue it's an issue of not understanding how MS Access works.

People who use Excel a lot are used to thinking in terms of a table. When they hear about MS Access they believe that it is made of the same sort of tables, well, actually it's not. A typical Excel spreadsheet transferred to access would most likely be reproduced as three separate tables, all different designs. Meaning they would in no way resemble the original data. The data would then be married back together to reproduce the original data.

See my blog for a better explanation, in text and YouTube's:- http://www.niftyaccess.com/excel-in-access/

Sent from my SM-G925F using Tapatalk

 

[plog]

Each paragraph would have about 3 tables related to each other...

That's wrong and not normalized. If every paragraph would have the same 3 tables with the same structure, then you only need 3 tables total not 3*[total paragraphs].

You would then add a field in those tables to designate which paragraph each record relates to. You would not store the paragraph information in the table name...you would store it in the actual table as a value in a field.

Read up on normalization.

 

[Kronix]

People who use Excel a lot are used to thinking in terms of a table. When they hear about MS Access they believe that it is made of the same sort of tables, well, actually it's not.

I am not using tables in Excel in any way related to the database. Only cosmetically if i make the borders for groups of cells visible to separate information. As I mentioned, I originally was using Word to make the contracts. The only reason I am switching to Excel is because it is easier to reference a cell in VBA than to search a document for a paragraph, and then a paragraph for a single number in a Word document to replace. That and I don't have to worry so much about aligning text or tabstops, or resizing invisible Word (cosmetic) tables that want to interfere with surrounding text. And if I do need to change how the Excel contract looks, I know that I am less likely to mess up the backend programming since cell referencing is not affected by spacing. And it's quicker than making cosmetic changes to an Access report.

 

[Kronix]

That's wrong and not normalized. If every paragraph would have the same 3 tables with the same structure, then you only need 3 tables total not 3*[total paragraphs].

You would then add a field in those tables to designate which paragraph each record relates to. You would not store the paragraph information in the table name...you would store it in the actual table as a value in a field.

Read up on normalization.

Every paragraph does NOT have the same structure. Think of each paragraph describing a smaller machine that is a part of the larger machine. I must have a table describing all the possibilities for that one smaller machine. It could be a small table, maybe only 3 values similar to a table for a lookup field or values in a combination box. The value shown is determined by the smaller machine item, and the smaller machine item is determined by the options chosen for the main machine. So that's 2 tables -- 1 for the smaller machine type and 1 for the values chosen for the smaller machine. In addition to the values, certain strings of text must be shown as legal or technical notes for certain smaller machine values or combinations of smaller machines, so that's a third table.

And there are many types of these smaller machines, each with it's own paragraph in the contract. Each paragraph describes a different part of the machine, hence the different types of smaller machines with their own group of tables to determine the contents of the paragraph. One paragraph could describe the motor, another paragraph could describe the control panel, each with its own 3 tables. Of course the motor and control panel won't use the same tables.

Each order will have a different combination of these smaller machines, according to what was picked or what can be picked for the main machine model. Two orders might be for different main machine models that can use the same motor but must use different control panels. So one of the tables for the motor would have the same value since they are the same model motor, but the other 2 tables could be different because different options were chosen, or even affected by other options such as the control panel. Since the control panel is a different model for each order, the values in all 3 of its tables could be different for each order, but they are still getting the information (including the control panel model but also the control panel options) from the 3 tables that are designated for the control panel paragraph in every order. It's also possible that some machines might have no control panel, in which case those 3 tables (or at least 1 of them) will contain a value indicating that the paragraph should be empty.

 

[The_Doc_Man]

Doing this in Excel is what you envision but we see issues. A contract document is a text entity that is best displayed - and if need be, tweaked - using a text editor. Since you want it to be "spiffy" you want it controlled by a utility that can make it look spiffy.

Excel is NOT (most assuredly not) a text-oriented document editor. So if you don't like Access reports (and for what you are doing, I can sort of see that), why in the name of Milton's Nine Hells would you choose ANOTHER non-text-oriented utility?

May I suggest a different approach?

Most contract documents have sections that are almost like "boiler plate" - standard verbiage that never changes. Then they have the stuff that is specific to your contract. So... you don't like the idea of having to diddle around in Word to find the right place for the next paragraph?

If you open a Word Application object and use it to open your contract template, you can use Word's bookmark feature to find the place where a particular type of item goes. In essence, you can jump-scroll your way through the places where your document needs stuff inserted. And guess what? The number of bookmarks you can have is very high.

So what you would perhaps do is copy your empty template with all the boiler plate and bookmarks in place, which you can do using the File System Object (Copy method works just great). And you can supply the correct name when you do the Copy so no need to rename anything.

Then start finding the bookmarks, and using the Word App Object, you can easily do some sort of GoTo {named bookmark} operation. I used that as a modern take on help files by having a manual that described each feature in question, and if the user clicked on Help, it gave them a menu of available topics filtered by information on the current form. And it jumped to the paragraphs of each such topic. Worked like a champ.

Don't let the complexity of the problem stop you. Because it ain't that complex - it is just a bit different because let's be honest, App Object manipulation can sometimes be difficult. We've all been there. But trust me, at the end of the day, diddling internals with Word is not much more difficult than diddling internals with Excel - but Word is MEANT to handle text documents and Excel is not.

 

[Solo712]

That's wrong and not normalized. If every paragraph would have the same 3 tables with the same structure, then you only need 3 tables total not 3*[total paragraphs].

You would then add a field in those tables to designate which paragraph each record relates to. You would not store the paragraph information in the table name...you would store it in the actual table as a value in a field.

Read up on normalization.

Every paragraph does NOT have the same structure. Think of each paragraph describing a smaller machine that is a part of the larger machine. I must have a table describing all the possibilities for that one smaller machine. It could be a small table, maybe only 3 values similar to a table for a lookup field or values in a combination box. The value shown is determined by the smaller machine item, and the smaller machine item is determined by the options chosen for the main machine. So that's 2 tables -- 1 for the smaller machine type and 1 for the values chosen for the smaller machine. In addition to the values, certain strings of text must be shown as legal or technical notes for certain smaller machine values or combinations of smaller machines, so that's a third table.

Kronix, I think it's clear that you have thought it through and the structure of your project is a complex assembly. Unlike some people here, I don't see any issue with what you are doing, and how you are doing it (i.e. via Access and Excel output). That is of course based on what you have told us. The devil might be in the details. But in general, having a large technical - legal spec handled by Access is absolutely doable, and actually preferable than doing it all in Excel, as I am sure you will be able to leverage the power of relational database. If it helps, I have done something similar in creating status certificates for condo units (on sale), a highly structured legal document consisting of prescribed info (pulled out of Access tables) filled in sections, some detailing info in previous sections (obtained by formulas or queries from the tables), some completely stand-alone, but everywhere richly spiked with boilerplate legal mumbo-jumbo.

So, yes, I am sure it's doable. I would not worry much about VBA. If you have gone through the assembly manually, I am sure you will find the coding just follows and replicates the manual process. Naturally, you need to be aware of the event-driven nature of Access but there is plenty of pointers on the Internet that will help you along. My advice would be, plan your project and if possible do it in manageable (modular) increments.

Best of luck,
Jiri

 

[The_Doc_Man]

If you have gone through the assembly manually, I am sure you will find the coding just follows and replicates the manual process.

ABSOLUTELY concur with Jiri on this one. Old Programmer's Rule #1 is "If you can't do it on paper, you can't do it in Access." For which the corollary is, "If you HAVE done it once or twice on paper, you should be able to do it in Access."

 

[Pat Hartman]

I want it in Excel in case I encounter something I have to edit manually. Can't do that with a report, at least not as easily. (?)

That's the point. If the database is your "master", you do not ever want to change an output on the fly. You want to change the data in the database and reprint the report. Access reports are more limited than is Excel or Word so you might want to export to either of them but you would NEVER change the "contract" on the fly. You would always go back and change the database and regenerate the contract.

You estimate that you need 100 tables. Do you think it takes 100 tables to implemnt the Sears Catalog? They sell everything from baby diapers to riding mowers. Many items have one or more options such as color or size or a cliping catcher bag for the mower. Somehow it all fits and they don't have to keep making changes every time they add a new item to the catelog.

 

[Kronix]

Thanks guys, especially Solo712.

Pat, it wouldn't be data I edit. It would be either master template cosmetic changes like spacing or fonts, or changes to parts I have not automated in Access yet -- the first few versions of the database will leave some things to be manually edited.

 

[Pat Hartman]

Creating editable contracts is simply a problem waiting to happen. I wouldn't do it. You will loose your audit trail.

As I said, Access is not as easy to make "pretty" as Excel is so I understand the desire to work with Excel but I would try to create what you need as an Access report rather than giving up before you even start.

 

[Kronix]

Creating editable contracts is simply a problem waiting to happen. I wouldn't do it. You will loose your audit trail.

I said I would leave parts of Excel editable and you're pointing out that that should never happen, and I agree with you. I was mixing up my goals for testing unfinished versions of the WIP database with the first release version of the database. For the first release version of the database, I will not have automated the creation of all of the paragraphs for the database. However, I intend to be able to edit those paragraphs *in Access* using a big text field which will then be exported directly to the appropriate cell in Excel, so I won't be editing any data directly in Excel.

So all that remains of directly touching the Excel document in the release version is cosmetic changes I would want to make to the contract, for example if we create a new logo or header/footer or if I find out in the future I need more spacing or smaller fonts to make a particular paragraph more readable. I assume you would agree that it is better to make such changes directly in Excel?

I wonder if all cosmetic changes in Excel are even possible through Access VBA. Is it possible to create a text field in Access that allows changing of fonts and spacing (essentially everything contained in the "Start" menu ribbon of Office products) and then copy the text including formatting into an Excel cell?