Sort by Number of Pages (1 Viewer)

[Freddy]

I have a report of customer info that is currently sorted by customer number. There are 1000s of customers and the report is well over 5,000 pages. I've been asked by the bosses on high to sort the report by the number of pages the customer receives so we can sent the smaller reports to a folding machine and larger ones for hand stuffing.

I'm considering adding some code to the on print event or in the page footer to log the number of pages each customer gets and then a second print job to sort by the number of pages.

Does someone have a better suggestion, preferrable one that does not kill trees with the first print?

 

[Fornatian]

Assumedly the number of records denotes the number of pages printed. If so create a query which returns a sum for each record of the number of records against each customer ref. Once you have that sorted, join that to your source data for the report using a join on the customer ref. Add the recordno field to your grid and sort by that.

It's off the top of my head so here's a quick example:

With base data like:

ID Text
1 Ian
2 John
3 Paul
4 Mike
1 Ian

Your Record Count Query should look something like:

SELECT Table1.ID, Sum(1) AS RecordNo
FROM Table1
GROUP BY Table1.ID
ORDER BY Sum(1) DESC;

and the query using it looks like:

SELECT Table1.ID1, Table1.Text, qryRecordCount.RecordNo
FROM Table1 INNER JOIN qryRecordCount ON Table1.ID = qryRecordCount.ID
ORDER BY qryRecordCount.RecordNo DESC;

The resulting table looks like:

ID1 Text RecordNo
5 Ian 2
1 Ian 2
4 Mike 1
3 Paul 1
2 John 1

Obviously, you can hide the record no from the query result if you wish.

 

[Fornatian]

Now I've had five to think about it, you don't need a sum query, a record count on ID field will do.

 

[Freddy]

If I understand you post correctly, this would work if the number of records per page were constant.

The report is pretty complex with several groups and headers/footers and grow/shrink fields, so the number of records that fit in a page varies.

 

[Fornatian]

Hmmmm.....

If a field is pushing the record vertically would a calculation based on the number of characters in the field divided by the number of assumed characters in a line on the text box giving height per record(ish). (I know some characters are wider than others but hey, this is free advice you know )

A sum of this may be a better indicator of report length?

I thought my first suggestion had it but sadly not.

If not I think we may have to enter the horrible realm of report format events.

 

[Freddy]

Thanks for the reply. This proved to be more than I can handle myself, so its in the hands of more experienced programmers. We're going with some event driven process executed during the first print run.

 

[Fornatian]

can you post the code when done so we can all see how its done (without having to do the research ourselves)