Combining one-to-many relational tables -> Flat TXT File

[Max]

Hello everyone,

I have a task of combining two tables: tblPatient and tblSolution,
related by a KEY field in a one-to-many relationship from tbl Patient
to tblSolution, respectively. So, I have the following table layout:

tblPatient
----------
REC: Autonum (Primary Key)
KEY: Number (NOT UNIQUE)
FN: Text
LN: Text
TEL: Text
tblSolution
----------
KEY: Number (NOT UNIQUE)
RESULT: Memo

a) What I would like, is, essentially, a file for a mail-merge. For
the lack of better understanding and currect merge document layout
requirements, I would like to create a table (for now) with these
fields:

tblPatient.REC, tblPatient.KEY, tblPatient.FN, tblPatient.LN,
tblPatient.TEL, tblSolution.RESULT

and somehow have all of the RESULTS for that KEY combined into one
RESULTS field, comma or Hard Return separataed.

---or---

b) create a table for mail-merge, which will contain the following:
REC, KEY, FN, LN, TEL, RESULT1, RESULT2, RESULT3, RESULT4... RESULT8
(max num of RESULTS is 8).

If anyone is kind enough to comment and show me the light, I would
really appreciate this.

Thanks,
Max

[Bernard Peek]

In message <b4**************************@posting.google.com >, Max
<ab******@aol.com> writes

a) What I would like, is, essentially, a file for a mail-merge. For
the lack of better understanding and currect merge document layout
requirements, I would like to create a table (for now) with these
fields:

Create a query by linking the two tables then export that as a text
file. There's no need to create a merged table.

Use docmd.transfertext

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

[Reggie]

Max, One other thing I see is that you say this is a one to many relationship, however, the Key
field in both tables is Not Unique. In this setup you can't have a one to many relationship using
this field as the PK/FK. For example you could have the following scenario:

tblPatient
----------
REC: KEY
1 2
2 2
3 2

tblSolution
----------
KEY: RESULT
2 Fixed
2 Not Fixed
2 In Process
......

As you can see this is not a one to many relationship.

--
Reggie

----------
"Bernard Peek" <ba*@shrdlu.com> wrote in message news:3h**************@shrdlu.com...

In message <b4**************************@posting.google.com >, Max <ab******@aol.com> writes

a) What I would like, is, essentially, a file for a mail-merge. For
the lack of better understanding and currect merge document layout
requirements, I would like to create a table (for now) with these
fields:

Create a query by linking the two tables then export that as a text file. There's no need to
create a merged table.

Use docmd.transfertext

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.