• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

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, 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
Hiken
Asked:
Hiken
  • 6
  • 5
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
hnasrCommented:
This a repeated issue. Try to search for it in EE solutions.
I commented on a few in EE.
0
 
HikenAuthor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
HikenAuthor Commented:
Uploading a copy of the database and problem for you to look at
SCORES-TEST.accdb
0
 
Rey Obrero (Capricorn1)Commented:
test this, run query Q_TopTen

I added a unique record ID to the table
SCORES-TEST-rev.accdb
0
 
HikenAuthor Commented:
Hi Rey,

This is close, but now it's pulling more than 10 records.  I can only have a max of 10 records.
0
 
Rey Obrero (Capricorn1)Commented:
which one is pulling more than ten records?
0
 
HikenAuthor Commented:
Doe, John is pulling 11 records and Smith, David is pulling 12 records.  Doe, Bud is fine because he has less than 10
0
 
Rey Obrero (Capricorn1)Commented:
did you check the db i uploaded?
Doe, John is pulling 10 records and Smith, David is pulling 10 records.
0
 
HikenAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
i mentioned in my post at http:#a40627289  to run query "Q_TopTen"

which query are you running?
0
 
HikenAuthor Commented:
I'm sorry Rey  I obviously can't read.  I have been running the wrong query.  That works perfectly!!

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now