reference columns by number in queries

[gemeye]

Greetings, columns or fields can be referenced by numbers (as variables)
programmatically in a recordset, but can columns be referenced by a number
indicating it's postion in a table within a Query? Thank you for your time
and expertise.

 

[John W. Vinson]

Greetings, columns or fields can be referenced by numbers (as variables)
programmatically in a recordset, but can columns be referenced by a number
indicating it's postion in a table within a Query? Thank you for your time
and expertise.

Yes.

SELECT [LastName], [FirstName], [Address], [Postcode] FROM tablename
ORDER BY 4;

will present the list sorted by postcode.

I haven't experimented much with this so I'm not sure what its limits are;
what is your recordset, and what are you trying to accomplish?

 

[gemeye via AccessMonster.com]

Thank you, I didn't expect your answer but it is relevent and I learned a bit
more about queries. However, closer to what I had in mind was, using your
example:

SELECT Field(1), Field(2), Field(5), Field(6) FROM tablename ORDER BY 4;

Field(n) , n= 1,2,3,..... represents the nth field as listed in the table
definition.

I tried using Field(n), n being some number, and Column(n), etc. with no
success.

Thanks again for your time and expertise.

Greetings, columns or fields can be referenced by numbers (as variables)
programmatically in a recordset, but can columns be referenced by a number
indicating it's postion in a table within a Query? Thank you for your time
and expertise.

Yes.

SELECT [LastName], [FirstName], [Address], [Postcode] FROM tablename
ORDER BY 4;

will present the list sorted by postcode.

I haven't experimented much with this so I'm not sure what its limits are;
what is your recordset, and what are you trying to accomplish?

 

[John W. Vinson]

Thank you, I didn't expect your answer but it is relevent and I learned a bit
more about queries. However, closer to what I had in mind was, using your
example:

SELECT Field(1), Field(2), Field(5), Field(6) FROM tablename ORDER BY 4;

Field(n) , n= 1,2,3,..... represents the nth field as listed in the table
definition.

Not to my knowledge. The order of fields in a Query is in principle completely
independent of the order of fields in the table definition, and valid SQL
syntax (again, AFAIK) refers to them by name, and only by name.

The number syntax refers to the position of the field in the query's SELECT
clause (and can be useful, for instance, in a UNION query where you're
unioning tables with different fieldnames).

What are these tables??? Do you have a whole bunch of tables of the same
structure, the same kind of data, and just different fieldnames? Are they
native Access tables or linked from somewhere else?