How Do I can separate records for reports

[zionsaal]

I have a reports that display 3 records on a page one at the top, and
on the middle
and on the bottom
I have lets say (442) records that means 148 pages and the last page
will be only one record

and I want to sort it by a id number
but when I cut all the pages in 3 to have each record one at the other
I will need to sort it manually
but I want the sort should start on the first page on top and then on
the next page on top
until the last page (meaning page no. 148) and then should the sort go
to the middle the same way till the last page and then to the bottom

I can add a field to the table and put into it by code a numeric sort
no. that it should go with the id sort like
id 1, (sort 1) id 2, (sort 4) id 3 (sort 7) id 4 (sort 10)...
and then sort the report by the field sort
but how do i break it to 3 that after the record 148 it should go like
this
2,5,8,11,14
because on this way i will be able to cut all the pages at ones and
have the sort automatic

dose any one have a way how to do that?

 

[Allen Browne]

First step will be to get a sequential counter in your query.
You can then use \ and Mod on this counter to sort the report.

This example assumes your table is called Table1, with an autonumber named
ID that you want to sort by. Create a query and type this expression into a
fresh column in the Field row:
Kount: (SELECT Count([ID]) FROM [Table1] AS Dupe
WHERE Dupe.[ID] < [Table1].[ID])
Test: you should a field named Kount give you sequential numbering.
Save the query.

Create another query based on the one you just saved.
In the Field row, enter 2 columns like this:
ItemNumber: [Kount] Mod 3
PageNumber: [Kount] \ 3
Test: you should see ItemNumber cycle from 0 to 2, and PageNumber counting
by 3s.
Save.

Set the RecordSource of your report to the second query.
In the Sorting And Grouping dialog, enter 2 rows:
ItemNumber Ascending
PageNumber Ascending

Notes:
1. Using two stacked queries like that should avoid the problem you often
get with subqueries where Access compains about a "multi-level group-by
clause."
2. The misspelling of Kount is intentional, as Count is a reserved word.
3. If the subquery gives you grief, you could use DCount(), though it would
be much slower. If subqueries are new, see:
http://support.microsoft.com/?id=209066

 

[zionsaal]

First step will be to get a sequential counter in your query.
You can then use \ and Mod on this counter to sort the report.

This example assumes your table is called Table1, with an autonumber named
ID that you want to sort by. Create a query and type this expression into a
fresh column in the Field row:
Kount: (SELECT Count([ID]) FROM [Table1] AS Dupe
WHERE Dupe.[ID] < [Table1].[ID])
Test: you should a field named Kount give you sequential numbering.
Save the query.

Create another query based on the one you just saved.
In the Field row, enter 2 columns like this:
ItemNumber: [Kount] Mod 3
PageNumber: [Kount] \ 3
Test: you should see ItemNumber cycle from 0 to 2, and PageNumber counting
by 3s.
Save.

Set the RecordSource of your report to the second query.
In the Sorting And Grouping dialog, enter 2 rows:
ItemNumber Ascending
PageNumber Ascending

Notes:
1. Using two stacked queries like that should avoid the problem you often
get with subqueries where Access compains about a "multi-level group-by
clause."
2. The misspelling of Kount is intentional, as Count is a reserved word.
3. If the subquery gives you grief, you could use DCount(), though it would
be much slower. If subqueries are new, see:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a reports that display 3 records on a page one at the top, and
on the middle
and on the bottom
I have lets say (442) records that means 148 pages and the last page
will be only one record

and I want to sort it by a id number
but when I cut all the pages in 3 to have each record one at the other
I will need to sort it manually
but I want the sort should start on the first page on top and then on
the next page on top
until the last page (meaning page no. 148) and then should the sort go
to the middle the same way till the last page and then to the bottom

I can add a field to the table and put into it by code a numeric sort
no. that it should go with the id sort like
id 1, (sort 1) id 2, (sort 4) id 3 (sort 7) id 4 (sort 10)...
and then sort the report by the field sort
but how do i break it to 3 that after the record 148 it should go like
this
2,5,8,11,14
because on this way i will be able to cut all the pages at ones and
have the sort automatic

dose any one have a way how to do that?

didn't works its sorts the same way if enter In the Sorting And
Grouping dialog ID

 

[Allen Browne]

How many rows do you have in total in the report's Sorting'n'Grouping box?
In which order?

Does the query show the numbers as you would expect? When you view the query
output, are these 2 columns right-aligned (like numbers), or left-aligned
(like text?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

First step will be to get a sequential counter in your query.
You can then use \ and Mod on this counter to sort the report.

This example assumes your table is called Table1, with an autonumber
named
ID that you want to sort by. Create a query and type this expression into
a
fresh column in the Field row:
Kount: (SELECT Count([ID]) FROM [Table1] AS Dupe
WHERE Dupe.[ID] < [Table1].[ID])
Test: you should a field named Kount give you sequential numbering.
Save the query.

Create another query based on the one you just saved.
In the Field row, enter 2 columns like this:
ItemNumber: [Kount] Mod 3
PageNumber: [Kount] \ 3
Test: you should see ItemNumber cycle from 0 to 2, and PageNumber
counting
by 3s.
Save.

Set the RecordSource of your report to the second query.
In the Sorting And Grouping dialog, enter 2 rows:
ItemNumber Ascending
PageNumber Ascending

Notes:
1. Using two stacked queries like that should avoid the problem you often
get with subqueries where Access compains about a "multi-level group-by
clause."
2. The misspelling of Kount is intentional, as Count is a reserved word.
3. If the subquery gives you grief, you could use DCount(), though it
would
be much slower. If subqueries are new, see:
http://support.microsoft.com/?id=209066

I have a reports that display 3 records on a page one at the top, and
on the middle
and on the bottom
I have lets say (442) records that means 148 pages and the last page
will be only one record

and I want to sort it by a id number
but when I cut all the pages in 3 to have each record one at the other
I will need to sort it manually
but I want the sort should start on the first page on top and then on
the next page on top
until the last page (meaning page no. 148) and then should the sort go
to the middle the same way till the last page and then to the bottom

I can add a field to the table and put into it by code a numeric sort
no. that it should go with the id sort like
id 1, (sort 1) id 2, (sort 4) id 3 (sort 7) id 4 (sort 10)...
and then sort the report by the field sort
but how do i break it to 3 that after the record 148 it should go like
this
2,5,8,11,14
because on this way i will be able to cut all the pages at ones and
have the sort automatic

dose any one have a way how to do that?

didn't works its sorts the same way if enter In the Sorting And
Grouping dialog ID

 

[zionsaal]

How many rows do you have in total in the report's Sorting'n'Grouping box?
In which order?

Does the query show the numbers as you would expect? When you view the query
output, are these 2 columns right-aligned (like numbers), or left-aligned
(like text?

I don't Have any rows their

the query show me if i sort it by the id number like this

ID itemNumber PageNumber
1 0 0
2 1 0
3 2 0
4 0 1
5 1 1
6... 2 1

that means if i sort the report by
itemNumber,PageNumber
it will be the same as the sort of the id field
0,0
1,0
2,0
0,1
1,1
2,1
0,3
......
Its the same

thank you very much for helping me!!!

 

[zionsaal]

I want it should be like this

ID MySort
1 1
2 4
3 7
4 10
5 13
6 16
7 2
8 5
9 8
10 11
11 14
12 3
13 6
14 9
15 12
16 15

that means the computer divides the number of records in 3 Like if the
number of records Ara 16 its divides in 3 means 6 pages 16/3 = 5.33
that means i need 6 pages
then it can input in a new field from 1 till 6
1+3 like this 1,4,7,10....
till the record 7 it should start again from 2
like 2,5,.........
remember that I need also to configure if the divide is not equal like
this one 16/3 = 5.33

 

[zionsaal]

someone sent me this code write for Delphi
does any one know how to convert it to VB access code?

begin

NoOfRecord := tbl1.RecordCount;



if NoOfRecord <= 6 then

NoOfPages := 1

else

NoOfPages := Trunc(NoOfRecord / 6);



RemainderCards := NoOfRecord mod 6;



PageNo := 1;

CardNo := 1;

I := 1;

tbl1.First;



while not tbl1.Eof do begin

if PageNo > NoOfPages then begin

if ((PageNo - NoOfPages) = 1) and (RemainderCards > 0) then

Dec(RemainderCards)

else begin

PageNo := 1;

Inc(CardNo);

end;

end;



tbl1.Edit;

tbl1Sort.AsInteger := ((PageNo -1) * 6) + CardNo;

tbl1CardNo.AsInteger := I;

tbl1.Post;

tbl1.Next;



Inc(PageNo);

Inc(I);

end;

thanks in advance

 

[Allen Browne]

Try adding the two rows to the Sorting And Grouping dialog in the report:
ItemNumber Ascending
PageNumber Ascending
in that order.

That will not sort as you said.
It will sort all the ItemNumbers that are zero first.
Within that, it will sort by PageNumber.

Following the example in your later post, it sorts like this:

ID MySort ItemNo PageNo
1 1 0 0
2 4 0 1
3 7 0 2
4 10 0 3
5 13 0 4
6 16 0 5
7 2 1 1
8 5 1 2
9 8 1 3
10 11 1 4
11 14 1 5
12 3 2 0
13 6 2 1
14 9 2 2
15 12 2 3
16 15 2 4

 

[zionsaal]

thanks Allen I found the code for it

only one thing I can't debug
that I can't find way to stop the loop (Do Until...)
the method **.EOF didn't work
thanks again for helping me


Private Sub Command5_Click()
Dim NoOfRecord As Integer, NoOfPages As Integer, RemainderCards As
Integer
Dim I As Integer, PageNo As Integer, vCardNo As Integer

NoOfRecord = Me.Recordset.RecordCount
If NoOfRecord <= 6 Then
NoOfPages = 1
Else
NoOfPages = Fix(NoOfRecord / 6)
RemainderCards = NoOfRecord Mod 6
End If
PageNo = 1
vCardNo = 1
I = 1

Do {What can I put here?}
If PageNo > NoOfPages Then
If ((PageNo - NoOfPages) = 1) And (RemainderCards > 0) Then
RemainderCards = RemainderCards - 1
Else
PageNo = 1
vCardNo = vCardNo + 1
End If
End If
Me.CardSort = ((PageNo - 1) * 6) + vCardNo
Me.CardNo = I
DoCmd.GoToRecord , , acNext
PageNo = PageNo + 1
I = I + 1
Loop
End Sub

 

[zionsaal]

thanks Allen I found the code for it

only one thing I can't debug
that I can't find way to stop the loop (Do Until...)
the method **.EOF didn't work
thanks again for helping me


Private Sub Command5_Click()
Dim NoOfRecord As Integer, NoOfPages As Integer, RemainderCards As
Integer
Dim I As Integer, PageNo As Integer, vCardNo As Integer

NoOfRecord = Me.Recordset.RecordCount
If NoOfRecord <= 6 Then
NoOfPages = 1
Else
NoOfPages = Fix(NoOfRecord / 6)
RemainderCards = NoOfRecord Mod 6
End If
PageNo = 1
vCardNo = 1
I = 1

Do {What can I put here?}
If PageNo > NoOfPages Then
If ((PageNo - NoOfPages) = 1) And (RemainderCards > 0) Then
RemainderCards = RemainderCards - 1
Else
PageNo = 1
vCardNo = vCardNo + 1
End If
End If
Me.CardSort = ((PageNo - 1) * 6) + vCardNo
Me.CardNo = I
DoCmd.GoToRecord , , acNext
PageNo = PageNo + 1
I = I + 1
Loop
End Sub

I can use
Do Until Me.NewRecord