Report from 2 tables

[briandh]

Hi,
I have a database of organizations, and I want to create a report to show the organization and its mailing info and telephone numbers. The source of the report is a table that contains all of the information on each organization. However - I have another table linked to my main table that contains additional phone numbers for each organization, since many organizations have more than one phone number.
My report works fine without trying to display all of the extra phone numbers. Do you know how to include the additional phone numbers in the report? I don't want a separate report entry for each additional phone number. I want the report to look like this:

Organization Name
Address
City, State Zip
Phone Number
Additional Phone Number 1 (If there is one)
Additional Phone Number 2 (If there is one)
ect.
Email Address


When I try to include the additional phone number table in my report, it will create a whole new report entry, so if an organization has 5 additional phone numbers, it would be in the report 5 different times.

Is this possible to do? I can not figure it out...

Thank you for your help!

Brian

 

[Namsha]

It would be in your sorting and grouping go to view sorting and grouping and make sure it says on each value in all your sorts. Good luck. I can expect anything and everything of myself;
But can only accept or reject what you have to offer!

 

[briandh]

Well, I changed it to "Each Value", and it still doesn't work. The information for the report is coming from 2 different tables - does that matter?

There is only one record in the main table, but many records in the additional numbers table that are linked to a single record in the main table.

This is what it is doing:

Name1
Address1
City1, State1 Zip1
Phone Number1
Additional PhoneNumber 1
Email1

Name1
Address1
City1, State1 Zip1
Phone Number1
Additional PhoneNumber2
Email

But I want it to be:

Name1
Address1
City1, State1 Zip1
Phone Number1
Additional PhoneNumber 1
Additional PhoneNumber 2
Email1


Any other ideas?

 

[Namsha]

Do you have those tables set to a query? If not try setting them to a select query to make sure you have your one to many relationship and obtain the correct information, i.e. one company name and many phone numbers. Then connect that to the report and do the each value.
I can expect anything and everything of myself;
But can only accept or reject what you have to offer!

 

[Namsha]

I have a printed screen try to email me the report and I will try to figure it out then send it back. I can expect anything and everything of myself;
But can only accept or reject what you have to offer!

 

[briandh]

Okay - what is your email address?
Do you want a screenshot of the report?

 

[ryan1]

You can always create a subreport and link it on the ID field from the master table. Although you should be able to do what you want through a query. You need to pull both tables into a query and set the relationship between the tables something like include all records from tblmain and only those from tbltable 2 where the joined records are equal. If your relationship is set up properly you should set what you want.

 

[briandh]

Thank you everyone! The Subreport did the trick! I still have to fool around a little bit with the format of the report now, but as far as getting everything included in the report I am sitting good.

Thanks again!

Brian