Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Using Min in Queries (access 200)

    I want to return minimum values for hotels for each city in my database. I can set a query to return the minimum value for each city but cannot figure out how to then return the name of the hotel. I can write code using dlookup and dmin to return the name for a single city but I think I then need a major select case to go through each city in the db. I have tried using a function as criteria in a select query but I am told I cannot do that. I am probably missing something very simple. Thanks for any help.

    Peter

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Using Min in Queries (access 200)

    It would be useful if you could explain the structure of your tables.

    Often you can do things like this with two queries. One grouping query to find the Min (or Max, or sum etc) then a second one that joins this back to the original data to add in extra fields.

    Another option is to add the extra field to the grouping query as a group by field.
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Using Min in Queries (access 200)

    I have a table that has Hotels Citys and Rates. I want to determine the mimimum rate for each city. As you suggest I can build a query that determines the minimum but that does not return the Hotel as obviously I cannot group on Hotels. I can use a query grouped on city and rate fininding the minimum and then use that in a new query where the links are city and rate that join to the main table , but I am concened that might miss two hotels with the same rate.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Using Min in Queries (access 200)

    Sorry it is still not clear to me what you really want.

    You say :" I want to determine the mimimum rate for each city"

    If that is what you want then how do hotels come into it?

    Do you want the minimum rate for each city,and the hotels that offer it?

    You have a table that has Hotels Citys and Rates. Does it list multiple rates for each hotel or just one? Is a Hotel specific to a city or might you have the same hotel in different cities? e.g. Melbourne, Hilton...Sydney,Hilton... does this table a unique identifier , such as RateID

    Is this the "Main table" you mention or is that another table?

    Why is is that "obviously I cannot group on Hotels" ?

    Can you post a cut down version of the db perhaps?
    Regards
    John



  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Using Min in Queries (access 200)

    Further thoughtson this.

    If you find min rate when grouped by city, then join this back by city and rate, why do you think it wil leave out some hotels?

    I attach a little demo showing that it returns what I think you want
    Regards
    John



Posting Permissions

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