I'll try to keep this simple, obviously there is a lot more to it.

I habe a table of Members
MemberID
MemSurName
MemFirstName
MemHeadOfHouseID ' Same for all members of the same household and points back
to the senior MemberID (Yes I know it's sexist)

E.G.
MemberID MemSurName MemFirstName MemHeadOfHouseID
1 1 Smith John 1 2 2 Smith Mary 1 3 3 Smith David 1 4 4 Jones Dave 4 5 5
Jones Tracey 4 Using VBA I can easily produce a query giving families so that
the output looks like this:- SurName FirstNames
Smith John, Mary & David
Jones Dave & Tracey

Now I want to use this query for mail merge letters, (Dear Dave & Tracey),
but as it involves code, I can't use OLE or ODBC linking so I am forced to
use DDE. (OLE doesn't show the query, & ODBC shows the query, but goes back
to "Find Data source") DDE works OK, but every time I re-open the mail merge
letter I get the error message "Word could not establish a DDE connection to
Microsoft Access…". Then have to go through the whole process again to tell
Word the name of the Db and the query to use.

Google hasn't coe up with anything helpful un the DDE connection problem. Has
anyone in the NG a solution? Can anyone come up with a query that will create
the families without using code so that I can use OLE linking which I am
assured is more reliable?

Using Windows 7 Office 2010

Thanks for any advice

Phil

Phil wrote:

If you added another columns, ex:
1 1 1 Smith John
2 1 2 Smith Mary
3 1 3 Smith David
4 2 1 Jones Dave
5 2 2 Jones Tracey
6 3 1 Adams Jerry
7 3 2 Adams Sally

1st is autoid, 2nd is family group (linkID), 3rd is position in family
group (memberID) you could create a pivot table. I assume there'd be a
maximum number of members in a family. In your example, 3 is the max.

You could then create a query with the pivot as the source table and
write a hellacious IIF() statment.

Here's an example of my pivot query
TRANSFORM First(Table1.FirstName) AS FirstOfFirstName
SELECT Table1.LinkID, Table1.LastName
FROM Table1
GROUP BY Table1.LinkID, Table1.LastName
PIVOT Table1.MemberID;

LinkID is the family group, MemberID (1,2 or 3) is the col head, columns
display the first name.

On 09/09/2010 10:39:47, Salad wrote:
Brilliant. Put me on the right track. Is you say the IIF clause looks ......
interesting

Any thoughts on the DDE problem?

Thanks

Phil

Hi, Phil

Phil wrote:
I remember a discussion on a similar problem in a German newsgroup.

The solution was to check the option "Confirm file format conversion on
open" .

Thereafter you have to connect the main document with the data source,
whereby you should be able to choose whether you want to use OLE, DDE or
ODBC.

You may find the above mentioned option (in Word) under File --> Options -->
Advanced --> General (close to the buttom)

HTH
Regards
Jens

On 09/09/2010 17:21:50, "Jens Schilling" wrote:
Thanks, Jens.

Tried it and no good. Only thing that works is DDE, and, as I mentioned
earlier, next time I open the document, I have to set the whole thing up
again.

Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote in
news:h5GdnUsIs733CRXRnZ2dnUVZ8iydnZ2d (AT) brightview (DOT) co.uk:

I avoid this problem by never using queries directly for mail merge.
Instead, I write the data to a temp table and use that as the mail
merge source (using ODBC, never DDE). This allows you to do whatever
you need to do in massaging the data in ways that replicate the
results of the functions that aren't accessible from outside Access.

You might also consider Albert Kallal's mail merge, which writes its
own text file for use as mail merge source.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On 09/09/2010 22:14:09, "David W. Fenton" wrote:
David, probably works running mailmerge from Access, but not so easy when
starting the mailmerge from word. Albert's mail merge worked well in AK2, but
I haven't cheched it in Access 2010 yet. Are you suggesting creating a sort
of universal temp table after closing any form that with a bit of luck may
have the fields needed for the mail merge? Chances of having all the right
fields seem remote. I need something that an operator can do, and the
filtering needed to select the correct records from a universal table would
be horrendous for the operator. That is why I like a few basic queries that
she can select from with no further filtering required.

Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote in
news:jOOdnZMbQKDznhfRnZ2dnUVZ8gudnZ2d (AT) brightview (DOT) co.uk:

You could run the query to populate the temp table from Word by
automating MS Access, then use it as the basis of the mail merge.

If you need the data transformed with functions that are available
only in Access, then you're stuck. An alternative would be to move
the logic from these into your mail merge document, but that
generally requires complicated If/Then/Else field codes, which I
find extremely hard to manage, and would want to avoid. But if may
very well be the most efficient approach to your problem.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/