Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Guelph, Canada
    Posts
    39

    Unanswered: Sort form records?

    I have a query that gives two columns of data, StudentID and Served (a
    number) as follows:

    SELECT Sum([NoFuture]![Detentions]-[NoFuture]![Served]) AS ToServe,
    NoFuture.StudentID
    FROM NoFuture
    GROUP BY NoFuture.StudentID
    HAVING (((Sum([NoFuture]![Detentions]-[NoFuture]![Served]))<>0))
    ORDER BY Sum([NoFuture]![Detentions]-[NoFuture]![Served]) DESC ,
    NoFuture.StudentID;

    This is used as the basis for a report.

    I have a table StudentCheck with the following fields:
    StudentCheckID, StudentID, DetentionID, Checkbox.

    I have a form that uses the StudentCheck table as its recordsource.
    using only fields: Checkbox and StudentID.

    I'm trying to figure out how to sort the records for the form so that
    they are in the same order as the records in the report.

    It's a bit tricky because of the Sum() function used in the query and the fact that the StudentCheck table doesn’t have the [Detentions] and [NoFuture] fields.
    Any suggestions would be appreciated!

    At the very worst, I’d like to have the form’s records sorted by Lastname.

    TIA Eric

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Query

    Base the record source of the form on a query rather than a table and sort the query in your required order
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Jan 2003
    Location
    Guelph, Canada
    Posts
    39
    >>Base the record source of the form on a query rather than a table and sort the query in your required order

    Sorry, I forgot to mention that the form is based on a query that is based on the underlying table.
    When I try to include the sort criteria that would do the job, it sorts properly, _but it's not updateable.

    Thanks. Eric

Posting Permissions

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