have a list of numbers in access and I want to show them in a special way

[amran88]

Hello,
I am new to this forum and I want to learn from you.
I have a list of numbers in access and I want to show them in a special way when displayed in the report or print preview.

the list:

when I display the report it shows like this:


but what i want is in the first page is to show row 1,2,3 the second page row 4,5,6 and the third page 7,8,9 , then in the first page after row 3 row 10,11,12 the second page after row 6 row 13,14,15 the third page after row 9 row 16,17,18.
can you help me please I really want this.
thank you.

[ranman256]

in the report, in page setup:
set for X columns,
set print DOWN, then across

[amran88]

thank you for replying.
and that what i did, which is shown in the picture above.
But what I want is to show on the first page is the row number 1,2,3,10,11,12,19,20,21.............

[CJ_London]

what happens if you select ACROSS then DOWN?

[ssanfu]

What are the dimensions of the gray box?
You want 3 columns.... you need to know how many rows can fit on a page?

With 60 serial numbers, are you sure you will only need 3 pages?

I would think you would need another field (PageNum) to enter the page number. Then the query/report would be sorted by PageNum, then serial_number.

[amran88]

hi,
i think i did not explain my problem right.


I have two columns with 10000 numbers or a row with a serial number from 1 to 10000 so I will need more than 3 pages.
One page fits 60 serial number or row.
When the report is opened, the page is shown with a 60 numbers or rows with a serial number from 1 to 60 and the second page appears with 60 numbers or rows with a serial number from 61 to 120....................


In the report page there are 4 columns and 60 cards,
let's say that there are 6000 serial number or rows, divide the 6000 serial number on 60 cards per page it will be 100 pages.
What I want from the report is from page 1 starting from serial number 1, 2, 3. Page 2 serial number 4,5,6 and page 3 serial number 7,8,9 to page 100 serial number 298,299,300.
and return to page 1 after serial number 3 continue from the last serial number on page 100 serial number 301, 302, 303 and continue like this to page 100 and go back to page 1...

If there is any code in any other program such as Excel or any other program please tell me

thank you.

[rpeare]

Fun problem

So let's say on each page you have three columns
In each column there are 20 rows

On page one on the left hand side you would expect to see the numbers 1 through 20
On page 2 on the left hand side you would expect to see the numbers 21 through 40
on page 3 on the left hand side you would expect to see the numbers 41 - 60

the in the center column (assuming 6000 serial numbers) on page 1 you would expect to see numbers 3001 through 3020, followed by 3021 through 3040 and 3041 through 3060 on page 2 and 3 respectively?

finally in the third column on page 1 you would expect to see serial numbers 5001 through 5020, then on page 2 and 3 5021 through 5040 and 5041 through 5060 respectively.

Am I correctly understanding the problem?

[CJ_London]

I have a different interpretation

return to page 1 after serial number 3 continue from the last serial number on page 100 serial number 301, 302, 303 and continue like this to page 100 and go back to page 1...

so just to repeat back for understanding based on your example

on page 1, the first 3 rows will be 1,2,3
the second row will be 301, 302, 303
the third row will be 601,602,603
etc

[rpeare]

HAH didn't notice it at first but there's also the discrepancy between the number of columns (four) and the example (which only has 3 columns)

we need more input from the original poster!

[ssanfu]

This how I read it:

From Post #3

But what I want is to show on the first page is the row number 1,2,3,10,11,12,19,20,21.............

This is the table structure I used"




The report - but not totally correct. Everything is on one page instead of 3 pages. (haven't gotten that far - waiting on OP response/clarification)
But you can see what serial_numbers should be on which page........

[amran88]

yes this is what i want thank you,
Can you send me the file you worked on.

[June7]

Review https://www.accessforums.net/showthread.php?t=73256

[rpeare]

Man I hate it when the OP posts something intriguing and then goes afk!

Here's my solution.

I created a table and put in several batches

tblSerialTest
SerialNumber  SerialBatch
0000001       1
0000002       1
0000005       1
0000006       1
0000101       2  
0000102       2

I created a 'temp' table

tblReportTemp
SerialBatch  SerialNumber PageNum RowNum ColNum

I have a button click on a form where the user selects the batch they want to print

Attached to that button I have this code

Dim rstDim iCPP 'columns per page
Dim iRPP 'rows per page
Dim iPC  'page count
Dim iSNC 'serial number count
Dim iCurrRec 'current record number
Dim iCurrPage, iCurrCol, iCurrRow 'current page number, column number & row number


dstart = Now


CurrentDb.Execute ("DELETE * FROM tblReportTemp")
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppendReportTemp"
DoCmd.SetWarnings True


Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblReportTemp ORDER BY SerialNumber")
If rst.RecordCount = 0 Then
    MsgBox "No records in this batch", vbOKOnly, "ERROR Processing Report"
    Exit Sub
Else
    iSNC = CLng(DCount("*", "tblReportTemp")) 'total records in recordset
    iCPP = 2    'Modify the number of columns here
    iRPP = 4    'modify the number of rows here
    iPC = Int(iSNC / (iCPP * iRPP)) 'page count based on records, column and line count
    If iSNC Mod (iCPP * iRPP) <> 0 Then iPC = iPC + 1
    iCurrPage = 1
    Do While rst.EOF <> True
        iCurrRec = iCurrRec + 1
        If iCurrRec Mod iCPP = 0 Then iCurrCol = iCPP Else iCurrCol = iCurrRec Mod iCPP
        If iCurrRec Mod (iCPP * iPC) = 0 Then iCurrRow = Int(iCurrRec / (iCPP * iPC)) Else iCurrRow = Int(iCurrRec / (iCPP * iPC)) + 1
        CurrentDb.Execute ("UPDATE tblReportTemp SET PAGENUM = " & iCurrPage & ", ROWNUM = " & iCurrRow & ", COLNUM = " & iCurrCol & " WHERE SERIALNUMBER = '" & rst.Fields("serialnumber") & "'")
        If iCurrRec Mod iCPP = 0 Then
            If iCurrPage = iPC Then
                iCurrPage = 1
            Else
                iCurrPage = iCurrPage + 1
            End If
        End If
        rst.MoveNext
    Loop
End If
dend = Now
Debug.Print "Complete in " & DateDiff("s", dstart, dend) & " seconds"

The code runs in approximately 23 seconds for 6000 records.

I started working on a query to do the same sorting (calculating page, row and column) but didn't go far with it, it seemed to be running faster than 23 seconds though.

I tried a few tests and it seemed to work for batches that took more than one page of labels.

I'd be interested to see your solution ssnafu. Given some time I am confident this could be fairly quick to run and not require a temp table I just wasn't going to invest in it absent comment from the OP

[CJ_London]

this is my solution - takes a fraction of a second to run for 6000. It does work off a calculated row number rather than a table with 6000 records in it. It also depends on the number of records being exactly divisible by number of columns x number of rows to give an exact number of pages. If you don't the additional fields get added as additional rows rather than creating an extra page. Leave that for someone else to worry about

to create the calculated row number, create a table with a single field and populate with the numbers 0 to 9. my table is called admCounter and the field is called ctr

then create this query

SELECT CLng([singles].[ctr]+([tens].[ctr]*10)+[hundreds].[ctr]*100)+([thousands].[ctr]*1000) AS [Counter]
FROM admCounter AS tens, admCounter AS singles, admCounter AS hundreds, admCounter AS thousands
ORDER BY CLng([singles].[ctr]+([tens].[ctr]*10)+[hundreds].[ctr]*100)+([thousands].[ctr]*1000);

The ORDER BY is not really required but is there anyway

and save it as admCounter

finally to the query proper

SELECT "3553 5657 8952 3146" AS NUMBER1, admCount.Counter AS Serial_Number, 6000 AS rcount, 15 AS [rows], 4 AS cols, [rcount]\([rows]*[cols]) AS pages, ((([counter]-1) Mod ([pages]*[cols]))\[cols])+1 AS page, ([counter]-1)\([cols]*[pages])+1 AS rownum, (([counter]-1) Mod cols)+1 AS col, ([page]*1000)+([rownum]*10)+[col] AS SortOrder
FROM admCount
WHERE (((admCount.Counter) Between 1 And 6000));

I've included the basis of each element so you can see the calculation - the assumption is in the report it will be sorted on the sortorder column and the rows go across, then down. In a live environment you would be able to populate rcount, rows and cols from other variables and remove the stage calculations. Note: changed cols from 3 to 4 as 45 items per page does not divide equally into 6000

whether this meets to op's requirements is not clear, the screenshots imply a table, but not sure why one is needed - at best you would have a cartesian query with serial number in one table and NUMBER1 as a single value in another table

[ssanfu]

@amran88,

You didn't provide a dB or a clear example of what you wanted - hence the other posts above my post #10 (Posts 3 -9).

My dB is just a table with 4 fields.....I created the table and wrote a little code to fill in the serial numbers.
I manually entered the page numbers for the 60 records.

A asked the dimensions of the dark box to try and calc the number of rows on a page.
I should have just asked the number of rows on a page.

Then I created a report - but without a lot of records, all pages ended up on the same page.

I haven't created any code yet to set the page number of each record - rpeare's code or June's code could be modified to do the job.


Soooooo: How many rows (shaded boxes) are on a printed page???