Solved

Access Select Query Problem

Posted on 2015-02-23
12
130 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

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…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

747 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

13 Experts available now in Live!

Get 1:1 Help Now