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

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

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

In message <b42267b8.0409160743.14e3a988@posting.google.com >, Max
<absolete@aol.com> writes
[color=blue]
>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:[/color]

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.

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

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" <bap@shrdlu.com> wrote in message news:3hu+9zx2DcSBFwLU@shrdlu.com...[color=blue]
> In message <b42267b8.0409160743.14e3a988@posting.google.com >, Max <absolete@aol.com> writes
>[color=green]
>>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:[/color]
>
> 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.
>[/color]