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
- SELECT TOP 5 PERCENT
- CollPerf.Colleague, CollPerf.Overall, CollPerf.SalesScore
- FROM CollPerf
- WHERE (((CollPerf.OldP) Like [Forms]![CollPerf]![Prev] or [Forms]![CollPerf]![Prev] Is Null)
- (... I have some other conditions in here, all of them work perfectly in the simple query)
- ORDER BY CollPerf.Overall DESC;
b) show me 5 stores with the highest sales within a specific campaign
Expand|Select|Wrap|Line Numbers
- SELECT TOP 5
- StoreLevel.Name, StoreLevel.ChangeContrib, StoreLevel.SalesScore
- FROM Stores INNER JOIN StoreLevel ON Stores.StoreNo = StoreLevel.StoreNo
- WHERE ( ((Stores.Territory) Like [Forms]![IndivScores]![Territory] Or [Forms]![IndivScores]![Territory] Is Null)
- (... again, some more criteria which works well without the TOP 5)
- ORDER BY StoreLevel.ChangeContrib DESC;
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.