By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

report with two one-to-many relationships

P: n/a
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?
Sep 26 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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?
Sep 26 '08 #2

P: n/a
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!

Sep 26 '08 #3

P: n/a
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!
Sep 26 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.