report with two one-to-many relationships

[Piotr Sobolewski]

Hello,

I have three tables: persons, telephone_numbers and email_addresses. They
are connected one-to-many:
- one person has many telephone numbers,
- one person has many email addresses.

I want to create report, which will show persons and for each person will
show his telephone numbers and email addresses. However, when I create a
report based on "select * from persons natural join telephone_numbers
natural join email_addresses", it don't work well. If a person has two
telephone numbers and two email addresses, my report has four rows - a
cartesian product of telephones and emails.

Is there any way to achieve what I want?

 

[Allen Browne]

Answered in:
comp.databases.ms-access

 

[Rob Parker]

Hi Piotr,

You'll need to do this using two subreports, linked to the main report via
the person field (or possibly a PersonID field, if you have one). The
subreports will use the telephone_numbers and email_addresses tables as
their recordsources, and the main report will use the persons table as its
recordsource. You can place the subreports side-by-side if you wish, they
do not have to be arranged vertically.

HTH,

Rob