Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How Do I can separate records for reports

Discussion in 'Information Technology' started by zionsaal@gmail.com, Jul 28, 2006.

  1. zionsaal@gmail.com

    zionsaal@gmail.com
    Expand Collapse
    Guest

    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?
     
  2. Loading...

    Similar Threads Forum Date
    Sexes Sitting Separately New to Gurdwara Apr 18, 2013
    India Bill for Separate Status to Sikhism Today Breaking News Aug 24, 2012
    General Home Ministry okays separate law for Sikh marriages Hard Talk Dec 23, 2011
    USA US Group Seeks Separate Registration of Sikh Marriages Breaking News Sep 2, 2011
    India Centre Drops Proposal for Separate Sikh Marriage Act Breaking News Aug 30, 2011

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    <zionsaal@gmail.com> wrote in message
    news:1152154898.756971.14450@m73g2000cwd.googlegroups.com...
    >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?
     
  4. zionsaal@gmail.com

    zionsaal@gmail.com
    Expand Collapse
    Guest

    Allen Browne wrote:
    > 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.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > <zionsaal@gmail.com> wrote in message
    > news:1152154898.756971.14450@m73g2000cwd.googlegroups.com...
    > >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
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    <zionsaal@gmail.com> wrote in message
    news:1152201306.525160.54850@m38g2000cwc.googlegroups.com...
    >
    > Allen Browne wrote:
    >> 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@gmail.com> wrote in message
    >> news:1152154898.756971.14450@m73g2000cwd.googlegroups.com...
    >> >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
     
  6. zionsaal@gmail.com

    zionsaal@gmail.com
    Expand Collapse
    Guest

    Allen Browne wrote:
    > 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!!!
     
  7. zionsaal@gmail.com

    zionsaal@gmail.com
    Expand Collapse
    Guest

    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
     
  8. zionsaal@gmail.com

    zionsaal@gmail.com
    Expand Collapse
    Guest

    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
     
  9. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    <zionsaal@gmail.com> wrote in message
    news:1152203354.188816.224760@p79g2000cwp.googlegroups.com...
    >
    > Allen Browne wrote:
    >> 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!!!
    >
     
  10. zionsaal@gmail.com

    zionsaal@gmail.com
    Expand Collapse
    Guest

    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
     
  11. zionsaal@gmail.com

    zionsaal@gmail.com
    Expand Collapse
    Guest


    > 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
     

Share This Page