Openreport to print only one page with multipage sub-report?

  • Thread starter Thread starter Ynot
  • Start date Start date
Y

Ynot

I have a report with a sub-report on it. The main report is always a single
page. I am printing it with a Docmd using a wherecondition. The sub-report
however contains dated entries. I sequence the sub-report descending and
only need the number of dated items that will fit on one page. How can I
only print one page if the sub-report has more than the number of items that
will fit on one page?



In my report, I can fit 18 entries into the sub-report on one page.
 
Ynot said:
I have a report with a sub-report on it. The main report is always a single
page. I am printing it with a Docmd using a wherecondition. The sub-report
however contains dated entries. I sequence the sub-report descending and
only need the number of dated items that will fit on one page. How can I
only print one page if the sub-report has more than the number of items that
will fit on one page?

In my report, I can fit 18 entries into the sub-report on one page.
Click to expand...


How about using a TOP 18 query (sorted by the date
descending) for the subreport's record source?
 
I need a bit more help. I don't know how to form the query. Since it is a
subreport, here is what Access put in. I need the top 18 of the left joined
table.

SELECT JobDates.DateStart, DateDescTable.DateDesc, JobDates.JobNumber,
JobDates.DateTaskEntered, JobDates.TicketNumber, DateDescTable.DateSF
FROM JobDates LEFT JOIN DateDescTable ON
JobDates.DateType=DateDescTable.DateKey
ORDER BY JobDates.DateStart DESC;


Thanks in advance for the help
 
Ynot said:
I need a bit more help. I don't know how to form the query. Since it is a
subreport, here is what Access put in. I need the top 18 of the left joined
table.

SELECT JobDates.DateStart, DateDescTable.DateDesc, JobDates.JobNumber,
JobDates.DateTaskEntered, JobDates.TicketNumber, DateDescTable.DateSF
FROM JobDates LEFT JOIN DateDescTable ON
JobDates.DateType=DateDescTable.DateKey
ORDER BY JobDates.DateStart DESC;
Click to expand...


I don't know what you mean by "here is what Access put in",
but I think You can just change it to:

SELECT TOP 18 . . .
. . .
 
Marsh,



Thanks for the reply. I was having a confused moment. I did change my
query and added top 18 but the results were very strange. Access gave me
the top 18 records of the main report but continued to give me all records
of the sub-reports.



I wrote the query again from scratch and when simply executing the query it
works fine when I execute the main report however I am only getting one
record on the sub report. I am still digging to try to even define the
problem better. Any Ideas?



Tony
 
OK, I have defined the problem but don't have a solution.



This statement works. It returns the top 18 rows



SELECT TOP 18 JobDates.*, DateDescTable.*

FROM JobDates LEFT JOIN DateDescTable ON
JobDates.DateType=DateDescTable.DateKey

ORDER BY JobDates.DateStart;



This statement does not work. It returns all 46 entries. The only
difference is the "where" statement that is required to print my one page
main report.



SELECT TOP 18 JobDates.*, DateDescTable.*

FROM JobDates LEFT JOIN DateDescTable ON
JobDates.DateType=DateDescTable.DateKey

WHERE jobnumber=11020

ORDER BY JobDates.DateStart;



Any Ideas??
 
Ynot wrote:>This statement works. It returns the top 18
rows
SELECT TOP 18 JobDates.*, DateDescTable.*

FROM JobDates LEFT JOIN DateDescTable ON
JobDates.DateType=DateDescTable.DateKey
ORDER BY JobDates.DateStart;>

This statement does not work. It returns all 46 entries. The only
difference is the "where" statement that is required to print my one page
main report.

SELECT TOP 18 JobDates.*, DateDescTable.*
FROM JobDates LEFT JOIN DateDescTable ON
JobDates.DateType=DateDescTable.DateKey
WHERE jobnumber=11020
ORDER BY JobDates.DateStart;
Click to expand...


Elsewhere in this thread, you said this query is the record
source of the main report. I had meant the Top 18 query to
be the record source of the subreport.

But now that I think more deeply on this issue, it may be
more complicated than I first thought. The subreport's
query may also need the JobNumber Where clause to get just
the top 18 for the specified job.

If you can't unravel my idea(?), post back with a more
detailed description of the data tables for both the main
report and the subreport and how they are related. I also
need to know if you're using the subreport control's Link
Master/Child properties.
 
Sorry to be confusing. The source of the main report is a query named
"JobsAll" that pulls together 3 tables - Job Card, Customer, and Job Start
information. The sub-report uses a query "JobDatesSubRQ" that pulls
together a date table with all dates of all items associated with a job and
a date description table that is a standard description for each date code
in the date table.



I am using the sub-report control to match on "JobNumber" in the date table
so that only dates associated with a single job are on the sub-report for
that job.



I am invoking the printing using a "docmd.openreport" with a
"wherecondition" Jobnumber = "Me.Jobnumber" off of an edit job card form.
This allows me to print a job card for the job that has just been changed or
entered.



I hope this helps. I may be able to put a small phony database together
with an example of this, let me know if that will help. And thanks for all
the help so far.
 
Ynot said:
Sorry to be confusing. The source of the main report is a query named
"JobsAll" that pulls together 3 tables - Job Card, Customer, and Job Start
information. The sub-report uses a query "JobDatesSubRQ" that pulls
together a date table with all dates of all items associated with a job and
a date description table that is a standard description for each date code
in the date table.

I am using the sub-report control to match on "JobNumber" in the date table
so that only dates associated with a single job are on the sub-report for
that job.

I am invoking the printing using a "docmd.openreport" with a
"wherecondition" Jobnumber = "Me.Jobnumber" off of an edit job card form.
This allows me to print a job card for the job that has just been changed or
entered.
Click to expand...

Let's try this:

Leave the main report's query as you originally had it.

Remove the subreport control's Link Master/Child properties.

Set the subreport's Record Source to this SQL statement:

SELECT TOP 18 *
FROM JobDatesSubRQ
WHERE JobNumber = Forms!editjobcardform.Jobnumber
ORDER BY [Job Start] DESC
 
Marsh,



I tested your suggestion and got the same results I mentioned earlier.



I figured out what is happening but I don't know how to solve it. Perhaps
there is another way to solve this.



This describes the results. This statement returns More than 18 rows:



SELECT TOP 18 JobDates.DateStart, DateDescTable.DateDesc,
JobDates.JobNumber, JobDates.DateTaskEntered, JobDates.TicketNumber,
DateDescTable.DateSF

FROM JobDates INNER JOIN DateDescTable ON
JobDates.DateType=DateDescTable.DateKey

WHERE JobDates.JobNumber=reports!jobcardprintN.Jobnumber

ORDER BY JobDates.DateStart;



For simplicity of testing, I changed TOP 18 to TOP 5 and here is what I
found. If the dates are all different then I get only 5 dates. If however
any of the 5 dates have multiple rows with the same date, I get all rows
with that date irrespective of the number of rows. There are variations on
this depending on how many dates occur on the same day. Following are some
examples:



The following would return 7 rows



09/10/2004

09/09/2004

09/08/2004

09/07/2004

09/07/2004

09/07/2004

09/07/2004

09/06/2004



The following would return only 5 rows



09/10/2004

09/09/2004

09/08/2004

09/07/2004

09/06/2004

09/05/2004

09/05/2004

09/05/2004

09/04/2004



The following would return only 5 rows



09/10/2004

09/07/2004

09/07/2004

09/07/2004

09/07/2004

09/02/2004



Marshall Barton said:
Ynot said:
Sorry to be confusing. The source of the main report is a query named
"JobsAll" that pulls together 3 tables - Job Card, Customer, and Job Start
information. The sub-report uses a query "JobDatesSubRQ" that pulls
together a date table with all dates of all items associated with a job
and
a date description table that is a standard description for each date code
in the date table.

I am using the sub-report control to match on "JobNumber" in the date
table
so that only dates associated with a single job are on the sub-report for
that job.

I am invoking the printing using a "docmd.openreport" with a
"wherecondition" Jobnumber = "Me.Jobnumber" off of an edit job card form.
This allows me to print a job card for the job that has just been changed
or
entered.
Click to expand...

Let's try this:

Leave the main report's query as you originally had it.

Remove the subreport control's Link Master/Child properties.

Set the subreport's Record Source to this SQL statement:

SELECT TOP 18 *
FROM JobDatesSubRQ
WHERE JobNumber = Forms!editjobcardform.Jobnumber
ORDER BY [Job Start] DESC
Click to expand...
 
Marsh,



I got it. Yahooo



I tried adding DISTINCT and that didn;t work. I had to add "TicketNumber"
which is the autonumber primary key of the JobDates table to the ORDER BY
clause. That solved the problem since it became a tie-breaker when several
dates were the same.



It works like a charm now. Thanks for all your help. Without it I might
have thrown my hands up days ago.



Tony





SELECT TOP 18 JobDates.DateStart, DateDescTable.DateDesc,
JobDates.JobNumber, JobDates.DateTaskEntered, JobDates.TicketNumber,
DateDescTable.DateSF

FROM JobDates LEFT JOIN DateDescTable ON JobDates.DateType =
DateDescTable.DateKey

WHERE (((JobDates.JobNumber)=[reports]![jobcardprintN].[Jobnumber]))

ORDER BY JobDates.DateStart DESC , JobDates.TicketNumber;


Ynot said:
Sorry to be confusing. The source of the main report is a query named
"JobsAll" that pulls together 3 tables - Job Card, Customer, and Job Start
information. The sub-report uses a query "JobDatesSubRQ" that pulls
together a date table with all dates of all items associated with a job
and
a date description table that is a standard description for each date code
in the date table.

I am using the sub-report control to match on "JobNumber" in the date
table
so that only dates associated with a single job are on the sub-report for
that job.

I am invoking the printing using a "docmd.openreport" with a
"wherecondition" Jobnumber = "Me.Jobnumber" off of an edit job card form.
This allows me to print a job card for the job that has just been changed
or
entered.
Click to expand...

Let's try this:

Leave the main report's query as you originally had it.

Remove the subreport control's Link Master/Child properties.

Set the subreport's Record Source to this SQL statement:

SELECT TOP 18 *
FROM JobDatesSubRQ
WHERE JobNumber = Forms!editjobcardform.Jobnumber
ORDER BY [Job Start] DESC
Click to expand...
 
Hey Tony, that's great to hear.

A tie breaker is exactly the way to go and the one you chose
is probably the best you have.

Keep on truckin and come on back if(?) have another issue.
 
Back
Top