Solved

Access Select Query Problem

Posted on 2015-02-23
12
137 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

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

Accepted Solution

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

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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

756 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