1. Senior Member
Join Date
Dec 1969
Posts
6,476

&nbsp;<BR>Hope someone can help me with this.<BR>Tried a few different ways and just can&#039;t seem to get it right.<BR><BR>Access 2000 Database<BR>Table = IHTComp<BR>Fields = TourID, Tour, Round, Member, Total<BR><BR>Examples of records<BR><BR>A, 1, 1, Russ, 100<BR>A, 1, 1, Bob, 50<BR>A, 1, 1, Dave, 200<BR>A, 1, 2, Ray, 125<BR>A, 1, 2, Jay, 150<BR>A, 1, 2, Ross, 175<BR>A, 1, 3, John, 200<BR>A, 1, 3, Fred, 100<BR>A, 1, 3, Pat, 150<BR>A, 2, 1, Lagg, 160<BR>A, 2, 1, Jerry, 170<BR>A, 2, 1, Berry, 150<BR>A, 2, 2, Russ, 110<BR>A, 2, 2, Randy, 50<BR>A, 2, 2, Perry, 130<BR>A, 2, 3, Dave, 140<BR>A, 2, 3, Ron, 160<BR>A, 2, 3, Robb, 170<BR><BR>I am tring to show only 1 record with the Lowest Total, for Each Grouping of Tour and Round.<BR>Examply if Displayed Records.<BR><BR>A, 2, 3, Dave, 140<BR>A, 2, 2, Randy, 50<BR>A, 2, 1, Berry, 150<BR>A, 1, 3, Fred, 100<BR>A, 1, 2, Ray, 125<BR>A, 1, 1, Bob, 50<BR><BR>This Query<BR><BR>SELECT TOP 1 IHTComp.TourID, IHTComp.Tour, IHTComp.Round, IHTComp.Member, Sum(IHTComp.Total) AS SumOfTotal FROM IHTComp GROUP BY IHTComp.TourID, IHTComp.Tour, IHTComp.Round, IHTComp.Member ORDER BY IHTComp.Tour DESC , IHTComp.Round DESC , Sum(IHTComp.Total)<BR><BR>Will only select 1 record only. (A, 2, 3, Dave, 140)<BR><BR>IF I Take out the (TOP 1) in the select statement then it shows all records ordering by Tour Desc, Round Desc, Total Asc.<BR><BR>I just can not seem to get this right.<BR><BR><BR>Can someone lend me a hand in showing the lowest Total in each Tour, Round grouping please?<BR><BR>Hope I explained that right.<BR><BR>Thanks in advance.<BR><BR>Russell M

2. Senior Member
Join Date
Dec 1969
Posts
2,437

SELECT I.TourID, <BR> I.Tour, <BR> I.Round, <BR> I.Member, <BR> I.Total <BR> FROM IHTComp <BR> where total in (select min(total) <BR> from IHTComp <BR> where Tour = i.tour<BR> and round = i.round )<BR> ORDER BY I.Tour DESC , I.Round DESC

3. Senior Member
Join Date
Dec 1969
Posts
6,476

## Almost, but got errors

I got an error on that Sql<BR><BR>To few paramaters<BR>Expected 5<BR><BR><BR>sql="SELECT I.TourID, I.Tour, I.Round, I.Member, I.Total FROM IHTComp where total in (select min(total) from IHTComp where Tour = i.tour and round = i.round ) ORDER BY I.Tour DESC , I.Round DESC"<BR><BR><BR>I Tried this<BR><BR>sql1="SELECT IHTComp.TourID, IHTComp.Tour, IHTComp.Round, IHTComp.Member, IHTComp.Total FROM IHTComp where total in (select min(total) from IHTComp where Tour = IHTComp.tour and round = IHTComp.round ) ORDER BY IHTComp.Tour DESC , IHTComp.Round DESC " <BR><BR>It does select the Min Total<BR>But the Group By only works for Tour.<BR>Meaning i only got 1 record for Each Tour like this<BR><BR>Tour, Round, Member, Total<BR>2, 1, Russ, 100<BR>1, 1, Bob, 150<BR><BR>Missing these records<BR>2, 3,<BR>2, 2,<BR>1, 3,<BR>1, 2,<BR><BR>Ill keep playing with it an see what i can do.<BR><BR>Any other suggestions?<BR><BR>Thanks again <BR>Russell M<BR>

4. Senior Member
Join Date
Dec 1969
Posts
2,437

## Forgot the correlation name.

sql="SELECT I.TourID, I.Tour, I.Round, I.Member, I.Total FROM IHTComp as I where total in (select min(total) from IHTComp where Tour = i.tour and round = i.round ) ORDER BY I.Tour DESC , I.Round DESC" <BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
6,476

## Thanks Lars

Worked perfect.<BR><BR>Thanks again<BR><BR>Russell M

#### Posting Permissions

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