Using a Union query in a subreport

[Larry AK]

Is there a problem using a union query in a subreport? I tried to
essentially concontonate 2 types of note/comment fields from separate tables
into a single query in order to have a single subreport. The wizard pukes
when I hit finish, and Access wants to backup and repair my database.

I can add subreports with data from each of the fields separately with no
problem. but then I have a blank line if nothing exists in one but not the
other.

 

[Gina Whipp]

Larry,

I use union queries in subreports so I'm going to say that is not the
problem. But a few more details might help shed some light on the
problem...

1. What version of Access?
2. Can you open the union query or does it bomb? (If no, fix the query and
then try again.)
3. Can you open any report? (If not, make sure you have a Default Printer
in Windows.)
4. How is the subreport linked to the main report?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Larry AK]

This is in Access 2003 (11.5614.5606)

I can generate a report using the query without any problem. It is when I
try to add a subreport using the query into my main report that Access bombs.


The main table is a list of sales Opportunities, keyed by a generated Id.
The Union query combines a query to retrieve the list of Notes associated
with any Opportunity, the Opportunity Id is carried in that Notes table and
is the link. The other half of the Union is getting the "Closed Reason" for
an Opporunity. This query uses a code in the Opportunity table to retrieve
the text associated with that code. The link is again the Opportunity Id.

I am guessing the problem may be due to the fact that the 2nd part of the
Union query, references the Id field in the main table driving the main
report.

To get around it, I have created 2 separate fields on the report - one as a
subreport with the list of notes associated with the main table, and the
other the Closed Reason text based on the code in the main table.