Limit Query Results 1

[Backlund1975]

Hello, I am new to both this forum and Access in general. I am running MS Access 2000 on a Win2k Server. I am wondering if it is possible to limit the number of records returned in a query. I have a table which has 64 different zip codes which consits of 17,000 records.

What I would lke to be able to do is return 200 records from each of the 64 different zip codes.

I appreciate any help or suggestions.

Mike

 

[alvechurchdata]

If you want the first 200 records then you can add a TOP predicate to a query:
[TT][blue]
SELECT TOP 200 MyTable.* FROM MyTable ORDER BY ZipCode
[/blue][/TT]
The Query Designer doesn't offer TOP as an option but if you create the query then switch to SQL view you'll see the SQL code generated by the query:
[TT][blue]
SELECT MyTable.* FROM MyTable ORDER BY ZipCode
[/blue][/TT]
and all you have to do is add the words TOP 200

Normally when you are building a query you will design it in Design View and see the results in Datasheet View. SQL View is the third option and shows the actual code behind the query.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[ZmrAbdulla]

The Query Designer doesn't offer TOP as an option ......:

Query designer offers!! Just select or type the value into the combo from the Query design toolbar. You may need to add "Sort Descending" to get the highest values.



________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal

 

[Backlund1975]

Thank you both for the help, unfortunately when I enter TOP 200 into the query string it returns 387 records??!@# and only of one zip code.

 

[r937]

show your query please

r937.com | rudy.ca

 

[Remou]

Hi
Maybe:

SELECT (Select Count(*)
    FROM MyTable O
    WHERE O.PostCode=MyTable.PostCode And O.ID<=MyTable.ID) AS Occurance, MyTable.ID, MyTable.PostCode
FROM MyTable
WHERE ((((Select Count(*)
    FROM MyTable O
    WHERE O.PostCode=MyTable.PostCode And o.ID<=MyTable.ID))<201));