Results 1 to 3 of 3
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Report-Specific order (XP)

    I have a table with membership details of a group of people. They have statuses of Gold, Silver and Bronze. I would like to create a report of these people grouping under membership. Obviously if I request to put them in alphabetical order it will come out as Bronze, Gold, Silver or Silver, Gold, Bronze.

    Can you suggest a way that I could adapt a query so that I could group so it reads Gold, Silver and Bronze I was thinking of assigning a number to the status (Gold=1, Silver=2 etc) but can't quite get my head around the grouping aspect.!!!
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report-Specific order (XP)

    I'd create a separate table tblStatus, with two fields: StatusID (number, long integer) and Status (text, field size 6)

    <table border=1><td>StatusID</td><td>Status</td><td align=right>1</td><td>Gold</td><td align=right>2</td><td>Silver</td><td align=right>3</td><td>Bronze</td></table>
    You could add a field StatusID to your membership table and use an update query to populate this field based on Status, then remove the original Status field. This is not absolutely necessary, however.

    Create a query based on your membership and on tblStatus, joined on Status or StatusID, depending on whether you added StatusID to the membership table. Add the fields from the membership table and the extra field from tblStatus. Use this query as record source for the report. You can group the report on the StatusID field.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> What! No Platinum status?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report-Specific order (XP)

    Wonderful make sense , thank you. I shall have a go.
    Jerry

Posting Permissions

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