OpenOffice BASE seems to require some unusual form of SQL syntax that does not seem to be the the same standard used by Microsoft Access, Sybase, Oracle or even HSQL. I've been trying to replicate, to no avail, a SELECT query used in a Microsoft Access DB, using the HSQLDB SQL syntax from that website. But OpenOffice doesn't seem to know what to do with INTO TEXT temptable in a SELECT statement.
I particularly need to be able to combine the data from multiple fields of the same record in a table [with separating commas], into ONE temporary list field, to be the source for selecting into only ONE field in an entirely different table. Where can I obtain the official complete syntax for the SELECT statement that OpenOffice BASE happily accepts (including some examples of correct usage using wildcards)? [The structure of SELECT DISTINCT "fieldname" FROM "tablename" "tablename" ORDER BY "fieldname" ASC in which the names must be quoted and the tablename repeated seems awkward.] I'm studying the feasability of moving a single-user DB from Microsoft Access on a Windows PC eventually to OpenOffice on a Mac using OS 10, with OpenOffice on a PC as the intermediate stage. The Access DB in question has no forms [yet] but is loaded with table field queries and numerous ad hoc queries for specialized extractions. Some very large tables consist entirely of fields related to and filled in by fields in other tables via pull-down list queries. Inserting large amounts of data into such an Access DB, where most of the fields in a record grouping are the same, is more easily achieved in a table format using the Ctrl-Quote feature in Access that automatically repeats the field immediately above. Thank you. Bill
