Solved

Access Select Query Problem

Posted on 2015-02-23
12
131 Views
Last Modified: 2015-02-24
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
Comment
Question by:Hiken
  • 6
  • 5
12 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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

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

Author Comment

by:Hiken
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

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

Accepted Solution

by:
Rey Obrero 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

by:Hiken
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 119

Expert Comment

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

Author Comment

by:Hiken
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 119

Expert Comment

by:Rey Obrero
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

by:Hiken
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 119

Expert Comment

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

which query are you running?
0
 

Author Closing Comment

by:Hiken
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now