Select Nth value in access database (SQL)

Question

I'm trying to select specific values in an Access database. So the database contains many colums, I'm only able to select the first values, or last:

SELECT Column1, First(Column7) AS Column7_1, First(Column8) AS FirstOfColumn8, [Column1] & [Column7_1] AS [Column1&Column7]
FROM [Table1]
GROUP BY Column1;

Now what I'm trying to figure out is how to get the nth values in those columns. What would be perfect is if SQL would recognize third(Column7)... I tried the following:

SELECT Column1, First(Column7) AS Column7_1, First(Column8) AS FirstOfColumn8, [Column1] & [Column7_1] AS [Column1&Column7]
FROM [Table1]
WHERE Column7 > (SELECT First(Column7) FROM [Table1]) AND Column8 > SELECT First(Column8) FROM [Table1]) 
GROUP BY Column1;

But this is not getting me there. the values from the different columns do not correspond anymore. Any guess on how I could get this? Thanks

Answer 1

AFAIK, selecting a column by column number with the aforementioned syntax is not really supported with SQL.

Something similar to the following could do the trick (with dynamic SQL):

get list of column names in given table*
for each column name
  do this
end

*The below query could be of use!

SELECT s.NAME SchemaName, 
       t.NAME TableName, 
       c.NAME ColumnName 
FROM   sys.columns c 
       INNER JOIN sys.tables t 
               ON c.object_id = t.object_id 
       INNER JOIN sys.schemas s 
               ON t.schema_id = s.schema_id

Answer 2

thanks for the replies, what I forgot to specify is that I'm actually not trying the nth cell in a column; it's more about getting the nth value contained in a specific colmn. For example, take the following table

Column1  Column2  Column3
A1       Prop1     20
A1       Prop1     20
A2       Prop2     15
A2       Prop1     20
A3       Prop2     15
A3       Prop3     5 
A3       Prop3     5 
A4       Prop1     20
A4       Prop2     15
A4       Prop3     5
A4       Prop4     10

I would therefore like to select the nth values from column 2 for each column1id, so that to get for first element:

Column1  Column2  Column3
A1       Prop1     20
A2       Prop1     20
A3       Prop2     15
A4       Prop1     20 

for second element:

Column1  Column2  Column3

    A2       Prop2     15
    A3       Prop3     5 
    A4       Prop2     15

and for the fourth:

Column1  Column2  Column3

    A4       Prop4     10

hope it makes sense