Find the MIN result in a query

[LB79]

Hello,

I'm having some trouble getting some results in a query (example attached).
Basically I have a table containing rates for the same route but from different vendors.
I want my query to show the lowest result (1 result).
I have setup my query to show the MIN rate, but the ref if stopping the query for working as I would like.
I need to have the ref so that I can link it with other data.

Can anyone offer any advice here?

Thanks

 

[Kryst51]

Change your ref to "last" instead of group by, I think that will work.

 

[James Dudden]

I think you need 2 queries to do this. Something like this.

Create a simple query and add the main table using the fields: from, to and unique price grouped with min on the price field.
Create another query and add the main table and the simple query you just created. Link the table to the query on the from, to and the price.

 

[James Dudden]

Change your ref to "last" instead of group by, I think that will work.

Sorry but I don't think that will give the correct ref.

 

[Kryst51]

Sorry but I don't think that will give the correct ref.

Just checked and you're right. Sorry for the mistake. I thought it would be a simple change in the query, as I have come across similar problems before. When I ran the "last" change, I didn't check to see if it returned the correct result.

 

[LB79]

Hello - I wonder if you can help me with this again. Im still having problems.

In the previous post you suggested:

Create a simple query and add the main table using the fields: from, to and unique price grouped with min on the price field.
Create another query and add the main table and the simple query you just created. Link the table to the query on the from, to and the price.

But the thing is, the price may not be unique.
Surely over the years of Access development, this sort of thing must have come up?

Perhaps I mis-explained before, but I have the following:

• Ref (Unique)
• From (Not unique)
• To (Not unique)
• Rate (Not unique)
• Vendor (Not unique)

In the query, I want to show the record with the lowest Rate for each From/To combination (Ref needs to remain in).
I just cant seem to figure it out.

Thanks for any more help

 

[Scooterbug]

First, I would add a new field to the table. RateID as an autonumber and use that as your primary key. Relying on a user defined unique identifier can lead to heartaches down the path.

Attached is the db you posted. I simply used a dmin() for the criteria for the rate.

 

[James Dudden]

Could you post/email me a db with lots of records and the results you would like to see. As far as i can tell at the moment what I have said should work.

 

[HAMMAMABUARQOUB]

WRITE this line directly in the Criteria section directly under the field Rate...

=DMin("[Rate]","[Table1]")

note.. this can also display all the min results, means if you add more records with 100 rate the will all be displayed!
THIS IS THE QUERY...

IT SHOWD THIS...

 

[LB79]

Thanks for the suggestions - Ive tried the DMin and while it gets the min, its not quite what Im after.
Ive added some more records to the example.
When the query is run, the results should show 2 records - 1 with ref ID B2, and the other with ref ID E5 (These records have different from/to locations, and are both the min rates for their combinations).

Thanks

 

[Scooterbug]

So you want a list of the lowest rate for every route grouping? Correct?

 

[LB79]

Exactly! Sorry if ive not been clearer

 

[HAMMAMABUARQOUB]

I suggest the following...
Creat a new field that is a merge in the field "from to" together, group by it,, REMOVE THE FROM TO FIELDS FROM THE LIST
thanks

 

[Scooterbug]

Hmmm...that's a bit more difficult. I assumed that you were going to add a WHERE clause to the query based on the actual route that is being assigned.

Of the top of my head, the best way to generate the list is to have a yes/no field indicating which is the lowest rate for each Start and End destination. But getting to that point, IMHO, is the tricky part.

 

[LB79]

HAMMAMABUARQOUB - Thanks for the suggestion but the unique Ref would stop the grouping.

Scooterbug - Thanks - Unfortunately the source data is controlled by another office and its unlikely they will amend their processes as it doesnt effect them. Looking on the net and in forums etc, im suprised that something like this which on the surface seems so simple, is so awkward.

 

[Scooterbug]

I can get a list of the lowest rates...the problem comes with trying to attach the Ref with that record. Since each ref is different, when you attempt to throw that into the query it prevents the grouping because it then makes each record unique. I'm not sure exactly how you are using this data. I'm sure there is something that can be done.

Off the top of my head, you could have two list boxes (or a combo box and a list box). One would show the lowest rates and the other one would show the each route with the Ref number. Give me a bit and I will do up an example. Not sure if that is what you are looking for though.

 

[LB79]

Thanks so much for your time and effort on this Scooterbug.
Without going into to much detail (as im not allowed), we have thousands of records containing combinations of locations, rates, and vendors, which are all used, but our marketing team want to be able to see the most competitive rates for each pairing at the touch of a button.

 

[David R]

Ahhhh, marketing.

Are the pairings unique? I had to do something similar and ended up doing one query to determine the 'lowest value', and then reverse engineer in another query to get back to the primary key that was throwing my first query out of whack.

 

[James Dudden]

I'm still fairly sure that my original answer works! Please see the attached file and look at the query called filtered.

 

[Scooterbug]

Ok...not as difficult as I thought. Check out Query2. Does that suit your needs?

Edit: Doh....not working out like I thought...back to the drawing board. James, your's does work...but for some reason I cringe when there are 3 joins, dunno why