Report with continuation page 1

[ehsguy77]

I have to create a report that will serve as a template to print shipping manifests. Manifests can be multiple pages long based on the number of line items needing to be manifested. In my instance, manifest information is pulled from a query based on tblManifests and tblManifestItems.

Here's the kicker. If a manifest has over 4 items on it (say it has 19 items), then the first 4 items need to print to page 1 of the report, the next 9 items need to print to page 2 of the report, and the remaining items would print to page 3 of the report. Pages 2 and 3 would be based on the same template, while page 1 is another template.

Does anyone know how to distribute records from a child table onto different pages of a report, and to make page one of a report unique from subsequent pages?

 

[dhookom]

I would use two queries. The first would select the top 4 items and would be the basis of a subreport that would fit in your report header section. The other query would be the remainder of the records and would be the record source of the main report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ehsguy77]

Sounds like a good idea - how do I limit the top four records in my query?

 

[dhookom]

Queries have a TOP property that you can set. This assumes you have set an sort order in the query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ehsguy77]

Okay - got the top 4 working. Now, I'm thinking I need to make another report for the template for continuation pages, which have room for 9 records per page. This report would need it's own query. How do I make this report show all records but the top 4? And can I limit it to 9 records per page in the report?

 

[dhookom]

If you have a query that shows the top 4, you can join it in another query with a join that shows all the records from your other query. Set a criteria under a field from the "top 4" query to Is Null. This will eliminate the top four records from the other query.

To break after a record, add a page break control at the bottom of the detail section and name it "pgbrk". Then add a text box to the detail section:
Name: txtCount
Control Source: =1
Running Sum: Over All
Then add code to the on format event of the detail section:
Me.pgbrk.Visible = Me.txtCount=9


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ehsguy77]

Duane,

You said

If you have a query that shows the top 4, you can join it in another query with a join that shows all the records from your other query. Set a criteria under a field from the "top 4" query to Is Null. This will eliminate the top four records from the other query.

I'm having a problem making this work. I'm know how to edit join properties, but I'm not sure which data from qryTopFour to include in qryRestofRecords (which would consist of the same datasource tables used to make qryTopFour?). Does it matter what field from qryTopFour I set to Is Null?

 

[dhookom]

Two queries:
1) the top 4 query (qryTopFour)
2) same as qryTopFour but with no TOP value set (qryAll)
3) Create a third query (qryRestOfRecords) based on the two previous queries. Join the appropriate fields and set the join properties to include all records from qryAll. Add any field from qryTopFour that would have data and set its critia to Is Null

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ehsguy77]

I've tried exactly what you said, and cannot get it to work. When I add a field to qryRestofRecords from qryTopFour and set the criteria to Is Null, I get no records. If I don't add any fields from qryTopFour, but all fields from qryAll, I get all records (with relationship between two fields defined to show all of qryAll). Any ideas? Have you tried this?

 

[dhookom]

There is no "qryRestOfRecords" until you have a query of top four and a query of All. You combine these two queries in a third query which creates your "qryRestOfRecords".



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ehsguy77]

Right - I've created qryTopFour and qryAll. Then I created a new query in Design View and added qryTopFour and qryAll to it as the data sources. I linked two fields (Manifest#) and set join properties to show all from qryAll, added all fields from qryAll and one from qryTopFour and set it's criteria to Is Null. Am I missing something?

 

[dhookom]

Your are missing "telling me the results of your query". I don't know if you are showing no results, all but the top four, all records...

I assume qryAll shows all records regardless of Manifest#. Another method would be to create a query:
[blue][tt]
SELECT *
FROM qryALL
WHERE [Manifest#] not in (SELECT [Manifest#] FROM qryTopFour);
[/tt][/blue]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ehsguy77]

qryRestOfRecords was either showing all records, or none (if using Is Null). I'll try your alternate method - thanks for your patience.

 

[ehsguy77]

Alternate method works like a charm. You've been swell...thanks again.