Multiple contacts make multiple pages

[CJ]

Hi Groupies:

My report shows customer order information as the main form, the subform
shows the customer contact. If there is more than one contact, then all of
the contacts are listed.

The problem is that if there is more than one contact, the report prints
more than one page and both pages are identical. The data very easily fits on
one but it will print as many pages as there are contacts.

Any ideas?

 

[Allen Browne]

Presumably you have the customer order information in one table (the one the
main form is bound to), and the contacts are in another table (the one the
subform is bound to.) You therefore need to make a query that uses use the
main form's table, and does not use the subform's table. Use this query as
the RecordSource for the report, and the record will not repeat for every
contact.

If you want to show a contact name on the report (perhaps any name if there
are multiples) you can use DLookup() on the report to read the contact name
from the subform's table. If you need help with that, see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

If that whole approach is not adequate, there are other solutions that would
use Totals queries to group the data, or subqueries to read related data in
the source query, posssibly stacking other queries on top of that. If you
are not sure about subqueries here's a starting point:
http://allenbrowne.com/subquery-02.html

 

[CJ]

Hi Allen, thanks for your response.

I tried using Dlookup, but that only returns the first contact to the
report. I still need both contacts to show up.

I am trying to get my head around how using subqueries or grouping would
solve the problem in this case. Perhaps I'm just a little slow....

Is there code that I could use to tell the report to print just the first
record? That way the end user would not have to worry about printing
duplicates.

 

[Allen Browne]

I'm not clear about what you want.

First paragraph of your reply states, "I still need both contacts to show
up."
Lat paragraph, "...to print just the first record."

Perhaps you need a function like this one that generates one record in the
report, but concatenates the 2 names together:
http://www.mvps.org/access/modules/mdl0004.htm
You would use that function in place of DLookup(), so it returns the names
on one line with commas between.

 

[CJ]

Sorry Allen, I guess I wasn't very clear.

I have a 1 to many relationship between my company and contact information.
My contact information is a subreport on the main order report and is joined
by the Company ID.

If a company only has 1 contact, the report prints the information with the
contact and everything is fine.

If a company has 2 contacts, the report prints with both contacts listed
(which I want) except, instead of 1 copy of the report being visible in
preview, 2 copies are being generated as if the report was 2 pages long. Both
pages are identical, they both show the order info and both of the contacts.
If there are 3 contacts, everything is previewed 3 times.

I have never seen this happen before so I'm not sure what the deal is?

 

[Allen Browne]

So there is a main report, and a subreport.

The main report must be bound to the Company table. Do not include the
Contact table in its RecordSource query. That will avoid the repeating
record.

 

[CJ]

And so it's as simple as that....figures!

Thanks a bunch Allen
CJ

So there is a main report, and a subreport.

The main report must be bound to the Company table. Do not include the
Contact table in its RecordSource query. That will avoid the repeating
record.