Top 10 query returns 11

[reg999]

I have a query in Access that uses Top 10 to get values from a record set. When I have duplicate values for the 10th and 11th record, both records show up in the results. I've forgotten how to leave out the 11th record.

reg

 

[PHV]

Add the Primary Key (or some field returning an unique value) in the SELECT clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[reg999]

Primary key [ID] field is in the select clause, but still returning 11.

SELECT TOP 10 Targets.ID, Targets.ShtrID, Targets.Gun, Targets.S, "" AS G, Shooters.Name
FROM Targets INNER JOIN Shooters ON Targets.ShtrID = Shooters.ShtrID
WHERE (((Targets.ShtrID)=[Forms]![NameList]![ShtrID]) AND ((Targets.Gun)="Light"))
ORDER BY Targets.ShtrID, Targets.Gun DESC , Targets.S DESC;

 

[reg999]

Values:
49
49
49
47
47
46
46
45
45
44
44

 

[PHV]

Your values doesn't correspond to the previous query (6 columns)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

Actually add the ID field to the Order By section:

SELECT TOP 10 Targets.ID, Targets.ShtrID, Targets.Gun, Targets.S, "" AS G, Shooters.Name
FROM Targets INNER JOIN Shooters ON Targets.ShtrID = Shooters.ShtrID
WHERE (((Targets.ShtrID)=[Forms]![NameList]![ShtrID]) AND ((Targets.Gun)="Light"))
ORDER BY Targets.ShtrID, Targets.Gun DESC , Targets.S DESC, Targets.ID;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[reg999]

My apologies.

ID ShtrID Gun S G Name
1775 1 Light 49 Reg
1679 1 Light 49 Reg
1338 1 Light 49 Reg
1831 1 Light 47 Reg
1361 1 Light 47 Reg
1315 1 Light 46 Reg
1201 1 Light 46 Reg
1803 1 Light 45 Reg
1689 1 Light 45 Reg
1738 1 Light 44 Reg
1669 1 Light 44 Reg

I have a work around for the problem now, but I'm still curious of the proper method.
Thank you,
Reg

 

[reg999]

dhookom,
That's the one!

Thank you very much!

reg

PHV,
You helped me out with another query on a post earlier this year, I'm having a new problem now. If you would help me out again, it would be deeply appreciated. I'll resurrect that post. "Looping