Blank Subreport for table with no related records

  • Thread starter Thread starter Perplexed
  • Start date Start date
P

Perplexed

Is it possible to have the third table subreport print out blank fields even
if there is no corresponding data related to the main table?

My subreport prints out if there is data in the third table that is joined
to the main table by the primary key; however, I would like to have blanks
print out on my report so that the person reviewing the data can enter the
corresponding information in the blank fields when appropriate.
 
Base your subreport on an outer join query, so the subreport does get a
record (all nulls) even if your Table3 has no record.

As an example, say the main report lists clients, and the subreport lists
their invoices. You want the subreport to print the fields, even if the
client has no invoices.

So you create a query using both the Client table and the Invoice table,
joined on the ClientID. Double-click the line joining the 2 tables in the
upper pane of query design. Access pops up a dialog with 3 options. Choose
the one that says:
All records from tblClient, and any matches from tblInvoice.
Verify that this gives you all clients (even those without an invoice.)
Save the query, as use it as the RecordSource for your subreport.

If outer joins are new, here's an introduction:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html
 
Thanks Allen! Once again you come through with a solution to my problem.
I had to adjust a little because my main report is based on a query to
filter which clients are in the report and then make a subreport from the
query. A couple of time the criteria (sql code) disappeared altogether but
somehow I got it to work. Now on to the next challenge.

Have a great day!
 
Thanks Allen, you came through again.
I had to tweak a little but finally got it to work. Now on to the next
challenge.
Have a great day!
 
Back
Top