Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    12

    Unanswered: lowest number event

    Hi,
    I am trying to write a query to return the the lowest number event from a list. For example, in the table I have the following data.

    Machine A - 6
    Machine A - 22
    Machine A - 45
    Machine B - 31
    Machine B - 44
    Machine B - 68
    Machine C - 2
    Machine C - 99
    Machine C - 155

    What I need the query to do is return only one instance of Machine A, B and C. But for that instance to be the lowest corrosponding number. ie

    Machine A 6
    Machine B 31
    Machine C 2

    Any assistance would be greatly appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi,
    You can do a group query, group on Machine and min of number

    ie if you fields are "Machine", "Number"
    then group on "Machine" and min on "Number"

    hopefully I've understood what you wanted

    Regards
    John

  3. #3
    Join Date
    Mar 2004
    Posts
    13
    SELECT Table.Machine, Min(Table.Value)
    FROM Table
    GROUP BY Table.Machine;

  4. #4
    Join Date
    Mar 2004
    Posts
    12

    Lowest number event.

    Thanks for your replies,
    however, I really am just beginning on my Learning Access Journey so I don't really follow either of the replies. If you could just expound a little bit on what I need to do I would very much appreciate it.

    Regards
    Jeff

  5. #5
    Join Date
    Mar 2004
    Location
    UK
    Posts
    30
    Hiya, I'm not sure how much you've already learnt so I'm sorry if this sounds a bit condescending Hope it clears it up.

    Taking Stickers explaination,

    1.Open a new query. Select the table with your info in and click add

    2.Select the two fields and drag them down on to the query columns in the lower half of the window.

    3.Turn on grouping. This is done by clicking on the sigma icon on the tool bar (looks like a Greek E)

    4.Underneath the machine column set it to Group by

    5. under the number column set the drop down to Min

    I've attached a gif image of what it should look like.
    Attached Thumbnails Attached Thumbnails snap.gif  

  6. #6
    Join Date
    Mar 2004
    Posts
    12
    Hi and Thanks to all.

    After a couple of frustrating hours you ledgends cleared up my problem in a matter of minutes.

    Thankyou so much for your help. And, no, you were definately not condescending. Your gif image was a big help.

    Regards
    Jeff Nicholson

Posting Permissions

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