Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Burbank, Illinois, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting (2000 SR1)

    Hello
    I work at a school distict and we are getting ready for 8th grade graduation. I had a request to make labels of all the eight graders in alphabetical order. That of course is no problem. My problem is not only do they want it in alphabetical order, but they would also like it to be boy, girl, boy, girl. An example of the request would be
    Adams, James (boy) Afash, Juanita (girl), Adams Robert (boy) Answer, Jane.
    Is this possible to do? They would like the labels in this order because they call the students up by boy, girl, boy, girl etc... in alpha order.
    Any help you could give me will be greatly appreciated.
    Thanks in advance!
    Wendy

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Sorting (2000 SR1)

    The only practical way I see of doing this would be to create 2 sets of labels, one for boys and one for girls.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Sorting (2000 SR1)

    You could run a sub like example below to sort students in alternating male/female order, alphabetically. For this to work you'd have to add a field to your Students table named "SORT" (number, long integer). Also assumes you have a "Sex" or "Gender" field identifying student as M (male) or F (female), and separate fields for Last Name and First Name.
    <pre>Public Sub SortBoysAndGirls()
    On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim lngSort As Long
    Dim qry As DAO.QueryDef

    Set db = CurrentDb
    strSQL = "SELECT SORT, LNAME, FNAME, GENDER FROM STUDENTS " & _
    "WHERE GENDER = 'M' ORDER BY LNAME, FNAME;"
    lngSort = 1
    Set rst = db.OpenRecordset(strSQL)
    With rst
    Do Until .EOF
    .Edit
    ![Sort] = lngSort
    .Update
    lngSort = lngSort + 2
    .MoveNext
    Loop
    .Close
    End With

    strSQL = "SELECT SORT, LNAME, FNAME, GENDER FROM STUDENTS " & _
    "WHERE GENDER = 'F' ORDER BY LNAME, FNAME;"
    lngSort = 2
    Set rst = db.OpenRecordset(strSQL)
    With rst
    Do Until .EOF
    .Edit
    ![Sort] = lngSort
    .Update
    lngSort = lngSort + 2
    .MoveNext
    Loop
    .Close
    End With

    strSQL = "SELECT LNAME, FNAME, GENDER, SORT FROM STUDENTS ORDER BY SORT;"
    Set qry = db.CreateQueryDef("BoysAndGirls_qry", strSQL)
    DoCmd.OpenQuery qry.Name

    Exit_Sub:
    Set db = Nothing
    Set rst = Nothing
    Set qry = Nothing
    Exit Sub
    Err_Handler:
    Dim strErrMsg As String
    strErrMsg = "Error No " & Err.Number & ": " & Err.Description
    Beep
    MsgBox strErrMsg, vbExclamation, "ERROR MESSAGE"
    Resume Exit_Sub

    End Sub
    </pre>

    Hopefully you have close to equal number of male and female students, otherwise there'll be a lot of "leftovers" at end of list. The query created by this sub can be used as record source for labels.

    Someone might have a better (simpler) way of doing this. I tested with a table of over 6,000 records so took a little while to loop thru all the records but seemed to do what you are looking for.
    HTH

  4. #4
    Lounger
    Join Date
    Nov 2001
    Location
    Fort Dodge, Iowa, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting (2000 SR1)

    Just a quick note RE: MarkD's solution. He mentioned that - when tested on ~6,000 records - it ran a bit slow.

    I had a similar problem to this one where I put alternating ID's against two types of sorted records (as you have it: male gets ODD ID's, female gets EVEN ID's). I then set up a program which would count from "1" up to the sum of both record-counts: checking - via DLOOKUP - each of the two recordsets and adding a record to the UNIFIED LISTING recordset from whichever recordset was able to DLOOKUP a valid record. Obviously, the MALES recordset would only pass a valid recordset when the count was ODD and the FEMALES recordset when the count was EVEN. It would be easy to include the current count as a field in the UNIFIED LISTING recordset, if necessary. Obviously a very similar solution to MarkD's.

    It also had a very similar flaw: it took FOREVER to run...even on a relatively small total record count. What I ended up doing was splitting out the MALES and FEMALES queries as separate, make-table queries which were fired by the program. (NOTE: if you turn off WARNINGS before starting a make-table query it will default to overwriting any existing table with the same name.)

    What I found was that average runtime - even on many thousands of records - was dropped from hours to less than 5 minutes. Keep in mind...I didn't add logging/error handling until _after_ I had the time issue handled.

    So try running your UNIFICATION bit - whether it's a program, query, or whatever - against tables, instead of queries.

    Hope that saves you some time.

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Sorting (2000 SR1)

    For the record, when I said "a bit slow" I meant appx 5-10 seconds (on home machine, only 650 MHZ, its overdue for an overhaul...). Tested same routine at work on 900 MHZ PC it took appx 1-2 seconds to update table (6000 + records) and open query with sorted records. Both PC's are PIII with 256 MB RAM. I neglected to mention earlier, the "SORT" field should be indexed.

Posting Permissions

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