473,320 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

[SOLVED] SELECT TOP query not working

Hi All,

I have a form where the user can fill out different search criteria and find specific sales data in the database.

I have 2 queries;
a) finding colleagues' performance scores
b) finding sales for specific campaigns and/or specific stores

They both work perfectly, so I decided to also add a little modification next to these two basic queries:

a) show me the top 5% best performing colleagues

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 5 PERCENT 
  2. CollPerf.Colleague,  CollPerf.Overall, CollPerf.SalesScore
  3.  
  4. FROM CollPerf
  5.  
  6. WHERE (((CollPerf.OldP) Like [Forms]![CollPerf]![Prev] or [Forms]![CollPerf]![Prev] Is Null)
  7.  
  8. (... I have some other conditions in here, all of them work perfectly in the simple query)
  9.  
  10. ORDER BY CollPerf.Overall DESC;

b) show me 5 stores with the highest sales within a specific campaign

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 5 
  2. StoreLevel.Name, StoreLevel.ChangeContrib, StoreLevel.SalesScore
  3.  
  4. FROM Stores INNER JOIN StoreLevel ON Stores.StoreNo = StoreLevel.StoreNo
  5.  
  6. WHERE ( ((Stores.Territory) Like [Forms]![IndivScores]![Territory] Or [Forms]![IndivScores]![Territory] Is Null)
  7.  
  8. (... again, some more criteria which works well without the TOP 5)
  9.  
  10. ORDER BY StoreLevel.ChangeContrib DESC;
My problem is that the query seems to completely ignore my TOP 5 PERCENT/TOP 5 ORDER BY bits.

All the criteria specified in the WHERE statement works, but it just selects ALL records that match the criteria and it doesn't even order it by the column specified in the ORDER BY clause.

(The queries pull through in a report, not in a table.)

Any ideas what the problem could be?

Thanks for your help.

UPDATE:
So if I just run the query on its own, it works, but when I try it with the button on the form that opens it up in a report, it doesn't work.
I think I know why the ORDER BY clause is ignored (I specified in the report wizard that I want the data to be displayed A-Z), but I don't understand why it cannot just display the required number of records.

SOLVED:
I realised the Control Source of the report wasn't correct.
Oct 21 '15 #1
1 1597
zmbd
5,501 Expert Mod 4TB
> easy fixes are the best fixes.
I've found all too often that putting the problem in writing or even just talking with someone will often result in finding the solution.

My other method, lunch, nap, and then back at it. :)
Dec 5 '15 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Vic Spainhower | last post by:
Hello, I am new to php and MySQL and I'm attempting to run a select query on a MySQL database which is working fine except prior to displaying the table with the results from the select query it...
2
by: Craig | last post by:
Acces 2000: I have several 'lookup' type tables with a standard field naming convention of id(pk) and name in a medium sized database. When I create a select query involving 3 or 4 of these tables...
9
by: MLH | last post by:
I have a table (tblCorrespondence) holding records with fields like , , , , , , , etc... About a dozen 's are defined and I often use queries to extract records of a given . That's pretty easy....
3
by: stevecat | last post by:
Hi there, I have created a form, "search" and a query "search_product". The query returns the product information based upon the criteria for three of the fields, author, title or isbn. The field...
5
by: Frank Jovi | last post by:
I am working on a website with ASP.NET 2.0 I started with a SQL database .mdf. After further discussions with my client, we decided to switch to an Access database. I created a Select query...
2
by: cherylwalsh35 | last post by:
I am working in access - using a select Query. I am including a memo field. The table has all of the data in the memo field displayed - however when I run a select query and include the memo...
2
by: runway27 | last post by:
hi i have an enquiry table which collects information about users making an online travel enquiry the fields in the table are = StoryTitle, EndCity, mode, PricedFrom, numAdults, numChildren,...
3
by: josh456 | last post by:
I am having trouble with the following expression in a select query not working. Expr5: IIf(!!="All","All", Or ) If I change the query to the following I am able to retrieve data from my query...
1
by: atishrg | last post by:
Hello All, I am facing one strange problem.. I am using a select query in my function which will return specified product details according to product code selected by user.. here is the...
0
by: GEETHA LAKSHMI | last post by:
I want to use where conditions in both update and select query cmd1.CommandText = "Update AttRegister set Present= (Select count(Working) from Attendance where Attendance.Working='P' and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.