Setting up Related Tables

[jenvandiver]

I need help on the structure of tables for my database.

First of all, what I'm trying to do is create a form to question users and then use that information to produce paragraphs for automatic insertion into a Word document.

One paragraph of the document will cover audience and have four subparagraphs: a, b, c, d, each covering a different category.

For each of these subparagraphs (categories), the user must choose one or more objects related to that category from a list or combo box, then one or more choices from a second list, etc.

These choices need to be tied to pre-stored sentences that could then be inserted as a paragraph into the Word doc.

Any help on how to save the user's choices in a table and link them to a sentence table would be appreciated!

Thanks!

 

[glynch]

Here is the approach that I would take:

Let's say for example that you have 20 different entries that you want to have a person make a selection on, and those 20 entries would be inserted into the text. I would create 20 lookup tables to contain the list of possible values to choose from for each particular text entry. Each of these 20 tables would have just 2 fields, an autonumber id field, and a text field to hold the text values.

Then I would create a large table with 20 long integer fields (one to hold the id value from each lookup table for the selection made in each combo box). Each lookup table would be the recordsource for it's corresponding combo box on your form. when a person fills out the form with all of their choices, have them hit a button to save the data. In the on_click event of that button, populate a record in your large table using the id values of all of the choices they made.

This large table can be used as the source of data for an Access report that can be exported to Word or as the datasource for a merge document in Word.

HTH, Good Luck

 

[glynch]

As an addendum to my last post, I just noticed that you mentioned that you needed to tie each text entry to a particular sentence. I would need to hear more of an explanation as to why that is necessary. But you could add a sentence id field to the large table, tied to a sentence lookup table. I'm not quite sure of the best way to configure that without anymore info.

 

[jenvandiver]

For example, one paragraph in the Word document will cover audience, with four subparagraphs: Operator, Maintainer, Unit Leader and Staff Planner. From each of these areas, the user will need to choose the specific Military Occupational Specialty (MOS), and any Additional Skill Identifiers (ASI) applicable to that MOS. So if they choose an 11B MOS with a D3 ASI for an operator, their choices would need to be linked to a pre-stored sentence which says something like "The operator for this system shall be an 11B MOS with D3 ASI". I won't be writing the sentences, but the ultimate goal is for the user to choose the specific audience for each subparagraph and have correctly worded sentences automatically inserted into a Word document. Standard language is the real necessity here...

 

[glynch]

It sounds like the sentence to be printed is determined by the choice or choices that are made as in the example that you gave. If that is the case, then choosing a MOS value and then an ASI value would determine the sentence that they are inserted into. If that is ALWAYS the case for all text combo selections then you can have a lookup table holding all of your sentences (along with a long integer id field). Then create a sentence - combo choices incidence table. an example of a record in that incidence table would be PrimaryComboID, SubordinateComboID, (as many as are needed), SentenceID.

So in your example a MOS value of 11B (let's say the ID value for that MOS record is 14), an ASI value of D3 (ID = 21), SentenceID = 7. you can write a query using that incidence table record to link you to the three text values in the lookup tables to assemble the text for your sentence.

I hope this is clear enough to understand.

 

[jenvandiver]

I understand what you're getting at. The problem is I'm still learning about queries (make table, append, update) and tables (look-up). It's not real clear to me at this point what makes a lookup table different or when you should use one. I'm just used to regular, plain tables...

 

[glynch]

A lookup table is a plain regular table, another commonly used term for the same type of table is master table. They are called lookup tables because they normally have just an ID field and one text field and are often used to feed combo boxes such as you would be using. It is not anything overly complex. If yo understand the idea of an incidence table like idescribed in the other posts then you are well on you way to solving your problem.

If you need more help with combo boxes and their recordsources and dependant combo boxes like with your MOS and ASI values, you can post another question on this same thread and I or someone else can continue to help you.

Good Luck

 

[jenvandiver]

So I need to create a lookup table to feed my combo boxes? Can you walk me through an example, using my MOS table/combo box?

 

[glynch]

Create a table tblMOS
Fields:
MosID Autonumber
MosValue Text (5) or whatever length it needs to be

Create a query (qryMOS): SELECT MosID, MosValue FROM tblMOS;

Create one form form all of the text selections you need to make to fill out a letter (frmDataEntry). Place a combo box on the form (cboMOS). Open the properties window for the combo and make the rowsource qryMOS. Enter these other values in the properties:

Column Count: 2
Bound Column: 1
Limit to List: Yes