Need to convert multiple tables into a flat file for Word merge

[VanceT]

I am a student working for a university library in the production of a
directory of private foundations in our state. We need to generate a large
directory of these foundations using MS Word (2003). However, the data for
the foundations has been stored in multiple tables in Access (2003) so that
for every one foundation stored in the primary table there are many sample
grants listed in a separate table, many officers and directors in another
table, and many areas of funding interest in yet another table.

I have generated a report in Access with the appropriate subreports to
generate the entire list of all the foundations we track. I have tried using
the "publish to MS Word" feature, but the *.rtf file is occasionally missing
some information in large text fields from the main table. The data shows up
properly in the report viewer in Access, so I pretty confident that the
problem here is occuring when the data is being exported to the *.rtf file.

This has led me to consider creating a flat-file table to be used with the
"merge" feature of MS Word. However, I do not know how to convert the data
from the related tables (with the "many" relationships) into a flat-file
format. The data in all of these related tables is text.

What I am starting with is something like this:

Table1
fldFoundationID (primary key)
fldName
fldAddress
fldNotes

Table2
fldFoundationID
fldSampleGrantID (primary key)
fldSampleGrant

These two are related by the field fldFoundationID where the Table1 has the
"one" side of the relationship and Table2 has the "many" side.

Obviously, I can have numerous grants listed for each foundation, but we
have limited the number of grants that we sample to 7.

The desired conversion would be something like this:
fldFoundationID (primary key)
fldName
fldAddress
fldSampleGrant1
fldSampleGrant2
fldSampleGrant3
fldSampleGrant4
fldSampleGrant5
fldSampleGrant6
fldSampleGrant7
fldNotes

It would help tremendously if anyone can tell me how to create such a
flat-file table. Likewise, it would be equally helpful if anyone could tell
me why "publishing" the report to *.rtf in Word resulted in data occasionally
being lost.

 

[Klatuu]

I have no answer for the .rft format issue. I have not had the opportunity
to use that feature; however, I believe you already have what you need to get
the data into a text file.

I would assume that since you can create a report with the correct data, you
have a Query built that contains the data you want. If so, Use the
TransferText method to move it to a text file. You can get more information

 

[Andi Lee Davis]

Hi There,

try building a query with the tables using a relationship where the One
Table looks up to the many Table, and then exporting the query as a MS doc..
Or even better, Excel.

To do this Create a new query using design view.
Right Click and Add/Show Tables.
Select the Tables you wish to show in the query.
If a relationship has not already been created, create on by dragging the
field with your Primary key from the Main Table to the PK in the Many side or
sub table where lots of records reflect to one in the main table.

Edit the relationship, right click and select Join Properies, and select the
option to show all records of your main table and only those from the related
table.

Now select the fields from either table you wish to show.

You could then Copy and paste the cells of the Query by clicking in the top
left corner to select them all and copy and paste into word, notepad or excel.
Another alternative is to do a Mail Merge (If you wanted to send a letter)
and lookup the query in your database from word.

Hope this is helpful.

Thanks

Andi Lee Davis

 

[Pierre-André]

Hello,


This function seem to be the same I used to tranfer the information of dBase
with 11 related tables containing the information of the alumni of my school
( private info + profession + employer, with relations 1 to multiple in both
senses ) to Word files with different sorting keys ( one by Name, one by
Employer, one by Promotion year, one by ZIP code, and one by NACE code of the
employer ) and write a directory. Of course, each sorting key record is not
duplicated when it has several "members" ( f.i. one company with several
employees ).

Is it ?

For each section, I first built a query to get the related information on
one single table. In the result a sorting key record with several members is
duplicated ( one line by "member" ). Never mind.
For each section, I then built an "Etat" ( "Report" in English ? ) using the
function "sort & group" to prevent duplication of the sorting key -- even
with several levels, f.i. Company name, division name, location name, crew
members ).

From memory ( I made it one year ago ), if the information of your fields is
shortened, make these fields longer in your "Report"
For easier work of the Word files later, I decided to put one field by line
and with a separate color.

Then view the results of your "Report" and use the "Office link - export to
word".
In the word file, the different coulors will help you to get a better
presentation by using the function "search & replace" for each field ( f.i.
company street, tel. & fax nb, ... ).

The above described process was realized with Office'97.
I managed 6000 names, 4000 adresses, 2800 functions in 2000 employer's
locations with a basic computer ( 666 Mhz, 312 Mo Ram and Windows 2000 )
Once programmed, this allowed me to compose the 5 sections on my directory (
500 pages ) in +/- 12 hours.

If you want screen copies, (e-mail address removed)

Hope having been helpful

Pierre-André