2 identical report pages on the same sheet 1

[danvlas]

Hi all,

I recently came across an interesting issue and haven't yet found a solution:
A continuous report needs to be printed in such a way that each report page is printed twice on the same sheet.
For an one-page report it's not a problem, as I can place two subreports with the same source, however I can't find a way to do this for a multi-page report.

Thanks in advance for any ideas.


Daniel Vlas
Systems Consultant

 

[dhookom]

Do you actually want the same record to print twice on the same page? This would result in the first record printing twice on page 1 and the second record printing twice on page 2 etc.

If this is the case the you might be able to simply add a table with two records into the record source query of your report. This will duplicate every record in the record source of your report.

Duane
Hook'D on Access
MS Access MVP

 

[danvlas]

Duane, thank you for taking the time to look at it.

However, it's not a 'single record' report.

I also thought of a cross join in the beginning, but I can't find a suitable way to order the 2 sets by "chunks" of records in order to properly separate them on the report.

Suppose I have a packing list report that has 100 records. Again, suppose there is room for 50 records on the entire page. But I want the page to contain 2 identical sets, so that I tear it on the middle and I get 2 identical copies (company logo and totals included).

In other words, I want the first page to contain two identical sets consisting of the first 25 records. The second page would contain 2 sets of records 26-50 and so on.




Daniel Vlas
Systems Consultant

 

[dhookom]

If the sections can't grow, you should be able to add a "chunk" column to the report's record source that numbers the first 25 records with a 1, the next 25 records with a 2, the next 25 records with a 3, etc. Then base you main report on a table containing 1, 2, 3, ... Add the same subreport twice into the detail section and use Link Master/Child to print the appropriate "chunks".

Duane
Hook'D on Access
MS Access MVP

 

[danvlas]

That might work. However it's a multi-user system and I'll have to check the concurrency on this particular report recordsource.
I may need to create a table on the fly with all the info, then run a loop to number the 'chunks', then set the report's recordsource to this table.

I'll see if the db admin allows me to create and delete objects in their database, but it might work.

Good time to persuade them to split the system


Thanks.





Daniel Vlas
Systems Consultant

 

[dhookom]

You shouldn't have to do much updating to get the "chunks". This is a query of the Orders table in Northwind that creates chunks of 25 records.

SELECT (Select Count(*) FROM Orders O WHERE O.OrderID<Orders.OrderID)\25 AS Chunk, Orders.*
FROM Orders
ORDER BY Orders.OrderID;

Duane
Hook'D on Access
MS Access MVP

 

[danvlas]

Without a temp table it will be impossible for me to do that, as there is no unique record identifier in the set. The rows are obtained by grouping and aggregation.

Even if there was such an ID, I try to avoid using these aggregate calculations that occur for every row, as they significantly slow down the query.
However, the number of records is going to be small, so I'll compare the speed obtained for each solution.

Anyway, depending on how I'll populate the temp table I might use the 'modulo' way:
- for a DAO recordset I can grab the auto ID and calculate the 'chunk number' before issuing an .Update
- For ADO, I'l need to check the @@Identity, then issue an Update command
- For an Insert statement, I'll have to issue an Update query immediately after that.

I'll have to make sure the ID starts from 1 every time (I must admit this concern does not exist in the solution you indicated). Probably I'll need a local table that can be created, used and deleted on the fly.
Splitting the database will be a 'must'. Compact-on-close will be checked for the front-end.

The most important thing is that I have something I can work with... then choose the most suitable solution.

Thanks again,



Daniel Vlas
Systems Consultant

 

[dhookom]

So, you are saying there is no uniqueness about the records. I'm not sure how you would define chunks without some field(s) that determine which set of chunks the records go into.

Why do you think you need to start the ID at 1 every time. My solution doesn't care as long as the values are unique.

Also, I have avoided bloat in a front-end before by creating a temporary MDB file with temporary reporting tables. I used code the basically builds the MDB and tables as needed.

Duane
Hook'D on Access
MS Access MVP

 

[danvlas]

Let me explain myself:

1. A 'chunk' represents the maximum number of records that fit half of the page.
I said there was "no unique record identifier", not "no uniqueness". The combination of grouped columns make the records unique, but they are names: box name, product name, truck plate. I meant there was no unique ID field in the 'original' query. AFAIK, there is no Row_Number() function in Access to create a 'counter' in a query.

2. In this case, I will need to create the 'background' to fill in the "child field":
a. a 'temporary' table to dump the results of the query
b. an AutoNumber column in this table (which is a standard for me)
c. a 'chunk' number, to test the performance between having such number written in the table and using your solution. If performance is not hurt significantly (or is even better than using stored values), I'll use your way and remove the field from the table as not necessary. If this is the case, I already admitted above that your solution was not affected by the start number. But, if I decide to use the 'write-to-table' way, I must make sure it starts from 1, in order to be able to calculate the 'chunk' number in an update query, using the same formula. If it were SQL Server, I would for sure use the 'stored' way to reduce the hits to the database, but In Access I'm not sure which is better.

3. Since 'compact-on-close' option appeared in Access XP, it's easy to use the front-end for local table creation and deletion at run-time, but I also used to create full mdb files back in Access 97 and 2K.


Best regards




Daniel Vlas
Systems Consultant