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]

Create a query using just persons and telephone_numbers (an *outer* join, in
case someone has no phone numbers), and use a subreport to show the email
addresses.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Piotr Sobolewski" <NI********@gazeta.plwrote in message
news:gb**********@inews.gazeta.pl...

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?

[Piotr Sobolewski]

Allen Browne wrote:

Create a query using just persons and telephone_numbers (an *outer* join,
in case someone has no phone numbers), and use a subreport to show the
email addresses.

Great idea, and really works! Actually, I created a report with persons only
and added two subreports: one with telephones, and one with emails.

Thanks a lot!

[Allen Browne]

Excellent.

To extend the idea, you can set the subreport up for (say) 4 columns (File |
Page Setup | Columns, set for "Across then down.") That gives you up to 4
phone numbers side-by-side before it needs a 2nd line on the report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Piotr Sobolewski" <NI********@gazeta.plwrote in message
news:gb**********@inews.gazeta.pl...

Allen Browne wrote:

>Create a query using just persons and telephone_numbers (an *outer* join,
in case someone has no phone numbers), and use a subreport to show the
email addresses.

Great idea, and really works! Actually, I created a report with persons
only
and added two subreports: one with telephones, and one with emails.

Thanks a lot!