I have a spreadsheet (Running under OpenOffice Calc) which is regularly updated (daily at times) and therefore set-up the linked database. The connection works fine and all the sheets in the .ods doc are carried across into the database. One of these sheets / tables is a listing of a large number of glider flights and I can successfully run a query against this table to give a subset of original flights dependant upon various criteria: speed, distance turnpoints used etc. I then want to find which of this subset, flew the longest distance and so attempt to run a query against the first query. This where the problem starts...
Using the Design View it's not possible to build a new query using information from an existing query, the only option available is to add Tables - the radio button to select Queries is just not there. However when using an unlinked HSQL database it's possible to build queries using both Tables and Queries so why not when linking to a spreadsheet?
If then by-pass the Design View and attempt to create the new query using the SQL command:
SELECT MAX( `Handicapped Distance km` ) AS `MMM_Max_km` FROM `xMMM_Qualifiers_1` AS `xMMM_Qualifiers_1`
where 'xMMM_Qualifiers_1' is the first Query and `Handicapped Distance km` is a column within that query, I get a error condition stating that "
The query can not be executed. It contains no valid table."
This is getting most annoying as this works successfully within Access and I can't understand that the simple difference of linking tables to a spreadsheet should restrict the operation of queries to such an extent.