Grouped Query - show the Min Source

[Jackson via AccessMonster.com]

Hi,

If I had a table, say tblTest with the following information in it:

Date Item Name Price
11/05/09 A Bob 1.10
11/05/09 A Bill 1.25
11/05/09 A Tom 1.15
11/05/09 B Bob 2.10
11/05/09 B Bill 2.05
11/05/09 B Tom 2.15

If I want to find the lowest price for each item, I'd group by Item and Date,
omit Name and min(Price). What I want to do is have this result but then in a
new field called Source return the name of who had the minimum price for each
item. If there are more than one with the same minimum price then the first
sorted Name would do so that's not an issue. Any ideas on how I do this?

 

[Michel Walsh]

You can select one of the four methods presented at
http://www.mvps.org/access/queries/qry0020.htm


Probably using two queries is the most 'intuitive' solution.



Vanderghast, Access MVP