Solved

# Access Select Query Problem

Posted on 2015-02-23
139 Views
i Have a query that is supposed to pull the top 10 scores or less for each individual out of a table.  The SQL below works unless the last few scores are the same number.

Example:

Bob HS scores: 100, 97, 98, 96, 95, 94, 93, 92, 92, 92, 90, 89, 87

Query should return

BoB HS scores:  100, 97, 98, 96, 95, 94, 93, 92, 92, 92

query is returning

Bob HS Scores:  100, 97, 98, 96, 95, 94, 93

If Bob HS Scores are : 100, 97, 98, 96, 95, 94, 93, 92, 91, 90, 89, 88

It does return the correct scores of:

100, 97, 98, 96, 95, 84, 93, 92, 91, 90

DisplayName = text
Competitor = Text
HS = Number

SQL:

SELECT t01HalfSupport.DisplayName, t01HalfSupport.Competitor, t01HalfSupport.HS
FROM t01HalfSupport
WHERE ((((select count(*)
From t01HalfSupport as X2
where x2.DisplayName =t01HalfSupport.DisplayName
and x2.HS >=t01HalfSupport.HS
))<=10))
ORDER BY t01HalfSupport.DisplayName, t01HalfSupport.Competitor, t01HalfSupport.HS DESC;
0
Question by:Hiken
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 5

LVL 120

Expert Comment

ID: 40626371
try adding a unique record id to your table and revise query like this

SELECT t01HalfSupport.DisplayName, t01HalfSupport.Competitor, t01HalfSupport.HS
FROM t01HalfSupport
WHERE ((((select count(*)
From t01HalfSupport as X2
where x2.DisplayName =t01HalfSupport.DisplayName
and x2.HS >=t01HalfSupport.HS and x2.ID <=t01HalfSupport.ID
))<=10))
ORDER BY t01HalfSupport.DisplayName, t01HalfSupport.Competitor, t01HalfSupport.HS DESC
0

LVL 30

Expert Comment

ID: 40626488
This a repeated issue. Try to search for it in EE solutions.
I commented on a few in EE.
0

Author Comment

ID: 40626523
Hi Rey,

I changed competitor to a Number, instead of text, and used it as my unique record id.  The competitor field is a number unique to each individual.  I'm still getting the same results with this:

DisplayName    Competitor   HS

Bob                      100                96
Bob                      100                95
Bob                      100                94
Julie                      101                99
Julie                      101                99
Julie                      101                99

SELECT t01HalfSupport.DisplayName, t01HalfSupport.Competitor, t01HalfSupport.HS
FROM t01HalfSupport
WHERE ((((select count(*)
From t01HalfSupport as X2
where x2.DisplayName =t01HalfSupport.DisplayName
and x2.HS >=t01HalfSupport.HS and x2.competitor >= t01HalfSupport.Competitor
))<=10))
ORDER BY t01HalfSupport.DisplayName, t01HalfSupport.Competitor, t01HalfSupport.HS DESC;
0

Author Comment

ID: 40626579
Uploading a copy of the database and problem for you to look at
SCORES-TEST.accdb
0

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 40627289
test this, run query Q_TopTen

I added a unique record ID to the table
SCORES-TEST-rev.accdb
0

Author Comment

ID: 40627944
Hi Rey,

This is close, but now it's pulling more than 10 records.  I can only have a max of 10 records.
0

LVL 120

Expert Comment

ID: 40628057
which one is pulling more than ten records?
0

Author Comment

ID: 40628120
Doe, John is pulling 11 records and Smith, David is pulling 12 records.  Doe, Bud is fine because he has less than 10
0

LVL 120

Expert Comment

ID: 40628144
did you check the db i uploaded?
Doe, John is pulling 10 records and Smith, David is pulling 10 records.
0

Author Comment

ID: 40628154
yes that is the one I'm talking about  when I look at it I'm showing doe, john pulling 11 and smith, david pulling 12
0

LVL 120

Expert Comment

ID: 40628201
i mentioned in my post at http:#a40627289  to run query "Q_TopTen"

which query are you running?
0

Author Closing Comment

ID: 40628280
I'm sorry Rey  I obviously can't read.  I have been running the wrong query.  That works perfectly!!

Thanks
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Itâ€™s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrickâ€™s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Whatâ€™s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macroâ€™s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
###### Suggested Courses
Course of the Month7 days, 20 hours left to enroll