?
Solved

Access Select Query Problem

Posted on 2015-02-23
12
Medium Priority
?
152 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 2000 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

765 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