How do I limit the rows returned into Excel from a query

[jpb]

I am trying to return data into excel from a query. The
query was written in MsQuery and selects from an access
database. I need to limit the results of the query to
only return 30 rows into my excel spreadsheet. I can set
the limit records option in MsQuery so that it only
returns 30 rows while in MsQuery, but when I refresh the
data in excel it still retrieves all of the rows.

Any help would be appreciated.
Thanks.

 

[onedaywhen]

My original reply was going to be, "Which 30 rows?" but I decided to
be more helpful.

I'm not too familiar with MS Query so I had a play. Here's what my
query looked like in the SQL window:

SELECT MyKeyCol, MyDataCol
FROM LongTime

I then went to Edit, Options, Limit the number of records returned 30.
I'm immediately suspicious that it didn't ask me which 30 rows I
wanted. I then looked at the SQL window again: no change, it's showing
the same as before. Just as I thought, the query is returning all rows
from the datasource but only showing me an arbitrary 30. No wonder
when I send the results to Shee1 I have many more than 30 rows
populated.

Now to answer your question. To limit the number of records returned
by the datasource you have to change the SQL. For example, to show the
rows with the 30 highest values for MyKeyCol:

SELECT DISTINCT T1.MyDataCol
FROM LongTime T1
WHERE 30 > (
SELECT COUNT(*)
FROM LongTime T2
WHERE T1.MyKeyCol < T2.MyKeyCol)

Note if your datasource is MS (MS Access, Excel, Jet, SQL Server), you
may find the proprietary TOP N syntax to your liking (I prefer ANSI
standard syntax for portability).