Select a specified number of records

[ITBill01]

Hello all,

Is there a way in a single query to select a specified number of records in a table? For example, I would like to append the first 5 records in one table to a second table. I'm not interested in "Top" or "Bottom" or other things as criteria... just the next 5 records. I'm using Access 2000 in WinXP.

Thanks.

ITBill01

 

[Golom]

Can you clarify a bit?

the [red]first[/red] 5 records ...

the [red]next[/red] 5 records

First of all, SQL queries do not have any guaranteed ordering without an ORDER BY clause so the "first 5" is undefined. The selection of the "next 5" implies that you want 5 records starting at some point other than the first record. Is that the case?

Second "TOP" is not a "criteria" in the sense that it appears in a WHERE or HAVING clause which is where criteria are defined. Why are you "not interested" in using TOP 5, for example?

The general approach for the "first 5" would be something like

INSERT INTO myTable (... field names ...)

Select TOP 5 ... field names ...
From SomeOtherTable
WHERE ... some criteria ...
ORDER BY ... field names ...

 

[ITBill01]

Hi Golom,

I think I actually meant First and Last as they apply to an Access query, rather than Top & Bottom. I understand about the record order (or lack thereof) in a table. All I want is from whatever record I am pointing to at a given moment, I want to select the next 5 records,regardless of order or other criteria.

Thanks for your quick response.

ITBill01

 

[Golom]

I assume that you mean that you have a form or a recordset where you are positioned on some particular record. A query returns zero or more records with some apparent ordering but it isn't "pointing to" any particular one of them.

Similarly, "the next 5 ... regardless of order" is somewhat contradictory. Without an ORDER BY clause, there isn't any way to determine what the "next 5" might be because the returned order is arbitrary.

If you were pointing to some record in a display that had some ordering then

INSERT INTO myTable (... field names ...)

Select TOP 5 ... field names ...
From SomeOtherTable
WHERE [red]SomeField > That_Field_In_Selected_Record[/red]
ORDER BY [red]SomeField ASC[/red]

 

[ITBill01]

Hi Golom, Sorry for the delay getting back.

Yes, I have a form and would be pointing to the first record in the recordset. By clicking on a button, I want to select the next 5 records in the recordset. I don't care what order they are in... I just want 5 records to be selected. If they are sorted, that's fine, but if they are not, that's fine, too. Whatever physical or logical order the records exist in is perfectly OK.

I can do this with a Visual Basic routine, but I was hoping for a simple query. I'm beginning to think that it's going to end up not so simple.

Thanks again.

ITBill01

 

[Golom]

I want to select the next 5 records ...

As I said, "next" is undefined without some notion of ordering. For example, the SQL I posted above assumes an order by looking for a value greater than some specified value. Without such an assumption you won't find SQL that can do what you want.

If you don't care about ordering then the only thing you can do is retrieve the query into a recordset and then iterate through the recordset to pick up the "next 5" in whatever order the system has returned the records.

If you are prepared to accept some implicit ordering then

INSERT INTO myTable (... field names ...)

Select TOP 5 ... field names ...
From SomeOtherTable
WHERE SomeField > (Select TOP 1 SomeField From SomeOtherTable)

What you will get from that is somewhat arbitrary because there is no guarantee about which record will be the TOP 1 or which records will appear in the TOP 5, absent an ORDER BY clause.

 

[ITBill01]

OK Golom,

Thanks for your help. I appreciate it.

ITBill01