The Situation

I maintain a legacy MS-Access database which is used to manage the contacts to a set of doctors offices. Initially the table "offices" (old) included information about the office and the employed doctors like this (abbreviated):

office	street	number	postcode	city	name1	surname1	name2	surname2	name3	surname3

An office could have between 1 and 3 doctors.

Initial Steps

A new requirement came up which asked for the separate modelling of offices and doctors as a 1:n Relationship.

I created a new table doctors:

name	surname	office_id

transformed the underlying data, dropped the redundant fields from the "office"-table and am now in the process of modifying the buisness logic accordingly.

First idea: Elegant or lazy? It fails any way

My first impulse was to create an "office" view which recreated the old "office" table and change the references to the "old" office table to this "office" view. This way I could keep the logic as it is and only adjust as needed.

The problem was, that the "office" table is very fundamental and used all over the place and i didn't manage to recreate the table without writing my own functions using a cursor making the view very costly. That the database is used in a Network environment didn't help because although this worked in principle, the .accdb file blows up to 70 to 80mb resulting in massive load times, making it in effect unusable.

Lets do it properly

So I ditched the "office" view and reworked the database to employ the new data model. A MailMerge Letter is given me particular trouble and i haven't found a good solution yet. The DataSource for the MailMergeDocument is a View relying basicly on the data in the "offices" and "doctors" table.

The issue

previously the Address field in word just used this as a datasource

id	street	postcode	city	name1	surname1	name2	surname2	name3	surnname3
3	Mainstreet	01234	timbuktu	Doe	John	Mae	Jane	Bar	Foo

Now i have an 1:n relationship and the datasource looks like this:

office_id	street	postcode	city	surname	name
3	Mainstreet	01234	timbuktu	John	Doe
3	Mainstreet	01234	timbuktu	Jane	Mae
3	Mainstreet	01234	timbuktu	Foo	Bar

However i still need to get all 3 (or in other cases just 2 or 1) Doctors on the adress field and yes there can be more than 1 office listed in the datasource.

Possible Solutions

I see basicly 2 Options:

1. Transform the view back into the previous format, without using Custom Functions, making it costly again, using a Single SQL-Statement.
2. Using the field functions within the word document to skip through the records as needed. In theory the Nextif-function should do exactly that, however i haven't been able to figure out how and if I can evaluate Data between different records in the word document. I need something like:

Nextif nextrecord.id = currentrecord.id

but i havent found a way to reference the records whithin the fieldfunctions.

Any suggestions for a clean solution are welcome.

Regards

CodePudding user response：

As is so often the way, after spending all this time posing my question, i have found a way to tackle the problem. I thought about solution 1 recreating the old table format without the use of VBA and came up with a lean way to do it.

The idea

If my doctors where numbered per office like this is doc no 1 in office no 6 etc i could simply count to three in the office table and refer to this unique DocID in a Join.

I need a view based on the office table that looks like this:

office_id	docid1	docid2	docid3	street	postcode	city
6	61	62	63	..	..	..

and a view based on the doctors table looking like this:

doctor_id	office_id	doc_id	name	surname
72	6	61	..	..
73	6	62	..	..
74	6	63	..	..

office view

This one is easy enough we take the office table as the basis for the view and simply add the following colums:

[office.id]*10 1 AS DocID1, [office.id]*10 2 AS DocID2, [office.id]*10 3 AS DocID3

doctors view

this one required a bit of googling but i found this forum post (@June7 is that also you in that thread?) so we take the doctors table as the basis and add the following column:

[Office_ID]*10 DCount("1","Doctors","office_ID= " & [Office_ID] & " AND Doctors.ID <= " & [Doctors.ID]) AS DocID

This gives us the desired Doc ID and we can now create a third view based on these 2 where we add the doctors view 3 times and join it each time with docID1, docID2, docID3 respectively and each time add name and surname.

conclusion

It is possible to recreate the old table without using VBA and solely relying on built in functions and SQL. I expect this to be a reasonable fast way to achieve this although i haven't measured it.

I welcome comments and improvements but I am now reasonably happy with the result.

Thanks for your input.

CodePudding user response：

For your original layout, the possibility of more than one entry could have been handled seamlessly via standard field coding in the mailmerge main document. For example:

Dear Dr {MERGEFIELD name1 \f " "}{MERGEFIELD surname1}{IF{MERGEFIELD surname2}<> "" "¶
"}{MERGEFIELD name2 \f " "}{MERGEFIELD surname2}{IF{MERGEFIELD surname3}<> "" "¶
"}{MERGEFIELD name3 \f " "}{MERGEFIELD surname3}¶
{MERGEFIELD office} {MERGEFIELD number} {MERGEFIELD street}
{MERGEFIELD city} {MERGEFIELD postcode}

For your 1:n relationship layout, you could save yourself a lot of effort and use Word's DATABASE field in conjunction with your mailmerge. For example, assuming you have a table of office_ids:

Dear Dr {DATABASE \d "Filepath/MyDatabase.accdb" \s " SELECT [Name], [Surname] FROM [Doctors$] WHERE [office_id] = {MERGEFIELD ID}"}

Note: The field brace pairs (i.e. '{ }') for the above examples are all created in the mailmerge main document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from here. Nor is it practicable (for the most part) to add them via the standard Word dialogues. The spaces shown in the field constructions are all required. Instead of the ¶ symbols shown in the examples, you should use real line/paragraph breaks.

For more details, plus links to practical examples, see:

https://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html

https://www.msofficeforums.com/mail-merge/38721-microsoft-word-catalogue-directory-mailmerge-tutorial.html