Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    145

    Unanswered: Report Sorting and Grouping

    Hello,
    We enter individual store purchases into a program manually. Each time an entry is made a reference number is assigned to it. The reference number increments by one. We do this so we can print out a report in the same order as inputed so we can check and see that we entered each line accurately. This is all done in Clipper and was developed awhile ago by someone else. I am using Access and what I want to do is sort the whole report by reference number, but I want to group each store and have a total at end of each store. I can group fine by store and get totals, but then the reference numbers are all mixed up. I can sort by reference numbers, but then I get a total for each store entry not the total of all entries for each store. What is happening is the report is grouping by reference number, but I only want it to sort by reference number and group by store. Thanks for any help.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Report Sorting and Grouping

    Originally posted by gwgeller
    Hello,
    We enter individual store purchases into a program manually. Each time an entry is made a reference number is assigned to it. The reference number increments by one. We do this so we can print out a report in the same order as inputed so we can check and see that we entered each line accurately. This is all done in Clipper and was developed awhile ago by someone else. I am using Access and what I want to do is sort the whole report by reference number, but I want to group each store and have a total at end of each store. I can group fine by store and get totals, but then the reference numbers are all mixed up. I can sort by reference numbers, but then I get a total for each store entry not the total of all entries for each store. What is happening is the report is grouping by reference number, but I only want it to sort by reference number and group by store. Thanks for any help.
    Switch your sort order then ...

  3. #3
    Join Date
    Jan 2004
    Posts
    145
    I don't think a one line answer is what's needed here. I've tried.

  4. #4
    Join Date
    Dec 2003
    Posts
    21
    When you are creating your report your grouping should be by store (if you are using wizard, it is the first grouping option you have to choose) and then you can sort by your reference number. hope that helps.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by gwgeller
    I don't think a one line answer is what's needed here. I've tried.
    Well you didn't state what you've already tried. Now, my "one line answer" MEANT that you should still group by the store (basically creating a header and footer) BUT put it 1st in the list of sort orders followed by the reference number ... This should put it in something like:

    Store A
    Ref 1
    Ref 2
    Ref 5
    Ref 6
    Store B
    Ref 3
    Ref 7
    Ref 11
    Store C
    Ref 4
    Ref 8
    Ref 9



    Is this what you're looking for?

  6. #6
    Join Date
    Jan 2004
    Posts
    145
    I'll do a little more explaining. We get reports with a number of different stores on them and their purchases. Usually when we enter them manually we do one store at a time. So store 'A' may get reference numbers 1-40 and store 'B' reference numbers 41-70. That is also the order each record was entered. This is what I'd like the report to look like:

    RefNo Store
    1 A
    2 A
    . .
    . .
    40 A
    Total
    41 B
    42 B
    . .
    . .
    70 B
    Total

    Say I forgot one entry for Store A and I put that in last, it would be assigned ref no. 71. I want my report to look like this:

    RefNo Store
    1 A
    2 A
    . .
    . .
    40 A
    Total
    41 B
    42 B
    . .
    . .
    70 B
    Total
    71 A
    Total

    So it needs to sort by reference number. For each continuous group of stores I would like a total. If the continuity is broke then there would be multiple total for that store. I hope this helps. Thanks again.

  7. #7
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Sounds like a good old fashioned control break to me...

    I'd add a third field to the table called "GroupNo"... numeric... default to 0... It won't matter until report time...

    Before printing the report, in code I'd do one quick iteration through the table (making sure it was sorted ascending by RefNo, and break on a change in store ID... At that time increment the GroupNo by one...

    In the report open code, I'd do something like this...

    Code:
    Private Sub Report_Open(Cancel As Integer)
    
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
        Dim pintGroupNo As Integer
        Dim pstrStoreHold As String
        
        pintGroupNo = 1
        Set db = CurrentDb
        Set rst = db.OpenRecordset("Select * From tblYourName Order By RefNo;")
        
        rst.MoveFirst
        If rst.RecordCount > 0 Then
        
            pstrStoreHold = rst![Store]
            
            Do Until rst.EOF
                If pstrStoreHold <> rst![Store] Then
                    pintGroupNo = pintGroupNo + 1
                    pstrStoreHold = rst![Store]
                End If
                rst.Edit
                rst!GroupNo = pintGroupNo
                rst.Update
                rst.MoveNext
            Loop
    
        End If
            
    End Sub
    and then in the report design I'd group by groupno...

    HTH

  8. #8
    Join Date
    Jan 2004
    Posts
    145
    Trudi,
    I think your approach will work, but I have a hang up. I am using a query as my record source. I also cannot manipulate the design of the underlying table. The problem is when I try to update the groupNo the query is read only. I thought queries could be made updateable, but I don't remember how, if they can. Can they?
    Thanks,
    GG

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •