Top Values not working in a query builder SQL

[Hosacans]

HI All,

I have a report that has a SQL written in the record source.
it's just a simple select statement that picks out records from a table, with it's records sorted by descent from one of it's fields.

i want to limit the amount of records pulled by this query, but the Top Value function somehow doesnt work on it.

i think that it's might have something to do with the query not being a "proper" one, one that is created and saved as an object in the database.

i wonder if there's some SQL code that can limit the number of records being pulled. but my SQL skill is very limited.

Please help

Thanks

 

[Remou]

When you say it is not working, how do you mean? Too many records, odd records? Can you post the SQL, please?

 

[Hosacans]

not working as in it doesnt limit the records being pulled.

for instance, the query still pulls all the records off the table that matches the select statement. but i only want it to pull the top 20 records.

other queries that i made (ones that are created and saved as a database object) will only pull the number of records that i specified in the "top value" drop down box, but this one... it doesnt.

 

[Remou]

And what is the SQL? With a little sample data, please.

 

[Hosacans]

SELECT TOP 20 Main_Midweek.Loc_No, Main_Midweek.Loc_Name, Main_Midweek.Season, Main_Midweek.Div_No, Main_Midweek.Div_Name, Main_Midweek.Style, Main_Midweek.Style_Desc, Main_Midweek.Sales_U, Main_Midweek.Sales_R, Main_Midweek.Day_Sales_R_Pen, FROM Main_Midweek
WHERE (((Main_Midweek.Div_Name)="Women"))
ORDER BY Main_Midweek.Day_Sales_R_Pen;


This is what i use and it will return all the records in Main_Midweek that has Div_Name as "women". somehow the Top 20 part doesnt work...

thanks

 

[Remou]

Are there duplicate values in Main_Midweek.Day_Sales_R_Pen?

Top Values
[tt]Typically, you use the TopValues property setting together with sorted fields. The field you want to display top values for should be the leftmost field that has the Sort box selected in the query design grid. An ascending sort returns the bottommost records, and a descending sort returns the topmost records. If you specify that a specific number of records be returned, all records with values that match the value in the last record are also returned.

For example, suppose a set of employees has the following sales totals.

Sales Salesperson
90,000 Leverling
80,000 Peacock
70,000 Davolio
70,000 King
60,000 Suyama
50,000 Buchanan


If you set the TopValues property to 3 with a descending sort on the Sales field, Microsoft Access returns the following four records.

Sales Salesperson
90,000 Leverling
80,000 Peacock
70,000 Davolio
70,000 King


Note To return the topmost or bottommost values without displaying duplicate values, set the UniqueValues property in the query's property sheet to Yes.[/tt]

From: Microsoft Help

 

[Hosacans]

ahhh... nice...

it worked... some how if i move the field being sorted to the left most field the Top value was able to shave off all the things that i didnt need...

weird.

Thanks Remou!!