CODEWITHSUNDEEP
ms-accessselect
Christos Pras
Christos Pras

Reputation: 35

MS Access select fields from multiple rows

I have a table like:

id|---|First|---|Second

1  ---  10   ---  100
2  ---  20   ---  200
3  ---  30   ---  300
4  ---  40   ---  400
5  ---  50   ---  500

I want to make a select query that returns:

1 -- 10 -- 200
2 -- 20 -- 300
3 -- 30 -- 400
4 -- 40 -- 500

Get the (i) data from the id and First column and the (i+1) data from the Second column. How can I do that? 

i -- i -- i+1

Thank you....

Upvotes: 1

Views: 1215

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

You can use this SQL statement:

SELECT TableName.ID, TableName.First, TableName_1.Second FROM TableName AS TableName_1 INNER JOIN TableName ON TableName_1.ID = TableName.ID+1;

where TableName is name of your table.

Or with WHERE clause:

SELECT TableName.ID, TableName.First, TableName_1.Second FROM TableName, TableName AS TableName_1 WHERE TableName_1.ID = TableName.ID+1

Upvotes: 1

Related Questions