Re: Blank Subreport for table with no related records

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!

"Allen Browne" wrote:

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

--
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.

"Perplexed" <Perplexed@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:21F269AA-0C83-4490-BE89-BF9CF5D90D23@xxxxxxxxxxxxxxxx
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.


.

Relevant Pages

  • Re: Passing date range Parameter to subreport
    ... The query has a SQL property of: ... Then open the main report with the subreport. ... MS Access MVP ...
    (microsoft.public.access.reports)
  • Re: Passing date range Parameter to subreport
    ... could you give me an idea as to how to pass a date range to the query ... This would rarely be used in a report. ... MS Access MVP ... would you expect to do with a recordset and a subreport? ...
    (microsoft.public.access.reports)
  • Re: Summarizing records in one table that match a different table
    ... the report. ... using "Language" as the example (i had to add a field to my Clients test ... i created a report based on the query with the joins ...
    (microsoft.public.access.reports)
  • Re: Passing date range Parameter to subreport
    ... query pulls the raw material lots used to produce the product lot (again ... I want prepare a report with the ... MS Access MVP ... Then open the main report with the subreport. ...
    (microsoft.public.access.reports)
  • Re: how to select parameter in subreport when main report is opene
    ... Set the LinkMasterFields property of the subreport control to ... Use this query as the subreport's RecordSource property: ... number and the main is showing the data for the same drawing the revision ... Is the main report showing the data for a single drawing number? ...
    (microsoft.public.access.reports)