Access Select Query Problem
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
FROM t01HalfSupport
WHERE ((((select count(*)
From t01HalfSupport as X2
where x2.DisplayName =t01HalfSupport.DisplayNam
and x2.HS >=t01HalfSupport.HS
))<=10))
ORDER BY t01HalfSupport.DisplayName
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
FROM t01HalfSupport
WHERE ((((select count(*)
From t01HalfSupport as X2
where x2.DisplayName =t01HalfSupport.DisplayNam
and x2.HS >=t01HalfSupport.HS
))<=10))
ORDER BY t01HalfSupport.DisplayName
This a repeated issue. Try to search for it in EE solutions.
I commented on a few in EE.
I commented on a few in EE.
ASKER
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
FROM t01HalfSupport
WHERE ((((select count(*)
From t01HalfSupport as X2
where x2.DisplayName =t01HalfSupport.DisplayNam
and x2.HS >=t01HalfSupport.HS and x2.competitor >= t01HalfSupport.Competitor
))<=10))
ORDER BY t01HalfSupport.DisplayName
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
FROM t01HalfSupport
WHERE ((((select count(*)
From t01HalfSupport as X2
where x2.DisplayName =t01HalfSupport.DisplayNam
and x2.HS >=t01HalfSupport.HS and x2.competitor >= t01HalfSupport.Competitor
))<=10))
ORDER BY t01HalfSupport.DisplayName
ASKER
Uploading a copy of the database and problem for you to look at
SCORES-TEST.accdb
SCORES-TEST.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Rey,
This is close, but now it's pulling more than 10 records. I can only have a max of 10 records.
This is close, but now it's pulling more than 10 records. I can only have a max of 10 records.
which one is pulling more than ten records?
ASKER
Doe, John is pulling 11 records and Smith, David is pulling 12 records. Doe, Bud is fine because he has less than 10
did you check the db i uploaded?
Doe, John is pulling 10 records and Smith, David is pulling 10 records.
Doe, John is pulling 10 records and Smith, David is pulling 10 records.
ASKER
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
ASKER
I'm sorry Rey I obviously can't read. I have been running the wrong query. That works perfectly!!
Thanks
Thanks
SELECT t01HalfSupport.DisplayName
FROM t01HalfSupport
WHERE ((((select count(*)
From t01HalfSupport as X2
where x2.DisplayName =t01HalfSupport.DisplayNam
and x2.HS >=t01HalfSupport.HS and x2.ID <=t01HalfSupport.ID
))<=10))
ORDER BY t01HalfSupport.DisplayName