Limit Number of Records Returned

[kjedwards]

Hi

I would be grateful if anyone can tell me if there is a way to limit the number of records returned by a query.

I have a database with approx 4000 records. I wish to do a search and the print out say the first 50 records which meet the search criteria.

A general search will result in about 500 records being returned. Is there any way to return say records 1 to 50 then 51 - 100, etc.

You can do this with other DB Apps because you can set a limit and an offset in the sql. Is there an equivalent in Access?

Many Thanks

Kevin

 

[JerryDennison]

Try using the Top X property. This can be done at the SQL level or you may have noticed a text box on your toolbar that currently says 'All'. This is the top X value property. Select the word All and type in 50. Now run the query, you should get the result you're looking for.

 

[cascot]

Hi Kevin,

As Jerry says, you can use the 'Top Values' property to return the top or bottom x number of records, however you also mention a wish to be able to view records 1 to 50, then 51 to 100, etc. I'm not aware of functionality within Access that would enable you to do this, however you could do this...

Create a Temp type table in your database with ALL the fields you want to display on your report. Ensure that any AutoNumber type fields are set as Long Integer types, then add a column called TempId. Set this to be an AutoNumber.

From code, run code to clear out the Temp table. Then run your query, but as an append type query, appending to the Temp table. Using Code, find the TempId of the first record in the Temp table.

Use this as a parameter for the query your report will be based on. This query should make use of the Temp table and should include TempId. That way you can say something like:

" WHERE TempId BETWEEN " & FirstTempId & " AND " & FirstTempId + 49.

Say you use command buttons on a form to 'flick through' the reports, you can then control the value of FirstTempId in a way that will determine which chunk of 50 records the report displays.

 

[kjedwards]

Hi

Thanks for the replies.

Can you please give a coding example how how to use the Top Values property - I am not familiar with this. I am used to using the mySQL database and returning records in small sets is a breeze.

I have linked Access via ODBC to the mySQL database. I guess there should be a way of sending sql direct to mySQL via Access but again haven't figured out if this is possible or how to do it.

If you can give me a sql code example of using Top Values I may be able to figure it out.

Thanks for the tip of using a temp table

Yours

Kevin

 

[cascot]

Hi Kevin,

A basic example:

SELECT TOP 5 TableName.Field1, TableName.Field2, TableName.Field3 FROM TableName

 

[kjedwards]

Hi

Thanks for the example - it seems to work ok

Yours

Kevin