report with two one-to-many relationships

[Piotr Sobolewski]

Hello,

I have three tables: persons, telephone_numbe rs 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_numbe rs
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]

Re: report with two one-to-many relationships

Create a query using just persons and telephone_numbe rs (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" <NIE_DZIALA@gaz eta.plwrote in message
news:gbhro1$i23 $2@inews.gazeta .pl...

Hello,
>
I have three tables: persons, telephone_numbe rs 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_numbe rs
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]

Re: report with two one-to-many relationships

Allen Browne wrote:

Create a query using just persons and telephone_numbe rs (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]

Re: report with two one-to-many relationships

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" <NIE_DZIALA@gaz eta.plwrote in message
news:gbhuno$qi4 $1@inews.gazeta .pl...

Allen Browne wrote:
>

>Create a query using just persons and telephone_numbe rs (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!
>