You can't do this in a query without knowing the name of the column. You
will need to write VBA code using OLE to interrogate the spreadsheet to
identify the last populated column. You can then obtain its "Name" from the
first row of the spreadsheet (assuming that the spreadsheet has column names
in the first row). Finally, you can build an SQL string with the
information obtained and run it or use it as the RecordSource of a form or
Report. You will need to alias the column name when you build the SQL
string otherwise, you'll also need to change the form or report.
I think you should consider making Access the entry point of the data and
then exporting data to spreadsheets if they users aren't happy with using
pivot tables in Access to play with their data.
If you decide to develop the data entry in Access, don't make the mistake of
simply importing the spreadsheets. You will need to normalize them. The
spreadsheet has a repeating group that causes new columns to be continually
added to the sheet. Once you convert this data into tables, that will no
longer be necessary. The new "column" will actually end up being just
another row of data and will not require ANY changes to the application.
This is the biggest advantage to a RDBMS over a spreadsheet. You Never need
to add new columns on the fly so you never need to modify your calculations.
