Base: Action Queries

Hi :)  
I'm trying to re-build a database that has become unweildy in Access and because it's Access it lacks scalability and some of the functionality we expect of it.

I've exported a tiny part of the data, and ended up with 2 columns
ClientID
CompanyName
Not all clients have a company name so there are a lot of blanks in the 2nd column.  I've written a query to just show the non-blanks (=non-nulls) but now i want to make that into a table in it's own right.  Is there an easy way to get a query to create the table for me or should i export as a Csv?

It's been a looooong time since i last wrote a database and then it was in Access.  So, please try to forgive me asking a stupid question
Thanks and regards from 
Tom :slight_smile:

Hi Tom,

I've exported a tiny part of the data, and ended up with 2 columns
ClientID
CompanyName
Not all clients have a company name so there are a lot of blanks in the 2nd column. I've written a query to just show the non-blanks (=non-nulls) but now i want to make that into a table in it's own right. Is there an easy way to get a query to create the table for me or should i export as a Csv?

This might help
http://www.techonthenet.com/sql/insert.php

using INSERT ... SELECT

Alex

Hi :slight_smile:
Thanks guys :)  Ok, so i am starting out with the embedded database against all advice but just so that i can keep emailing it to myself easily until i get to grips with it better.  Then i hope to export this table to whatever small back-end seems best.  My Sql coding gets rejected "Syntax error in SQL expression" and then these "SQL Status: HY000   Error code: 1000   Syntax error in SQL expression"
"SQL Status: HY000   Error code: 1000"
"SQL Status: HY000   Error code: 1000   syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE"

Here's 'my' code

INSERT INTO OrgTable
("IdOfOrg", "IdOfPerson", "OrgName")
SELECT ("IdOfOrg", "IdOfPerson", "OrgName")
FROM  "zzQuery1"
WHERE OrgName IS NOT NULL;

Regards from
Tom :slight_smile:

Tom,
One thing that popped out at me is that you used double quotes (") around your names. So I checked one of my queries to be sure and confirmed Base is using the single reverse quote ` ( not '). You might try that. The "...unexpected $end..." is a clue. It sounds like it is finding an end-of-statement prematurely. On my keyboard, the single reverse quote is the quote under the "~" tilde key, not the single quote under the " key.
Hope this helps.
Girvin Herr

Tom Davies wrote:

I wonder about the last line of your code:

WHERE OrgName IS NOT NULL;

Above this line you used "OrgName"
      Your field has upper and lower case letters. When this is the case, you must enclose the field's name in double quotes. So, I'm thinking that this is likely the problem.

      A quote from the HSQLDB Guide (version 1.8):
name
      The character set for quoted identifiers (names) in HSQLDB is Unicode.
A unquoted identifier (name) starts with a letter and is followed by any number of AS-
CII letters or digits. When an SQL statement is issued, any lowercase characters in un-
quoted identifiers are converted to uppercase. Because of this, unquoted names are in
fact ALL UPPERCASE when used in SQL statements. An important implication of this
is the for accessing columns names via JDBC DatabaseMetaData: the internal form,
which is the ALL UPPERCASE must be used if the column name was not quoted in the
CREATE TABLE statement.
      Quoted identifiers can be used as names (for tables, columns, constraints or indexes).
Quoted identifiers start and end with " (one doublequote). A quoted identifier can con-
tain any Unicode character, including space. In a quoted identifier use "" (two double-
quotes) to create a " (one doublequote). With quoted identifiers it is possible to create
mixed-case table and column names.

--Dan

Hi :slight_smile:
I think i need to explore this a bit more tomorrow.  I tried both types of quotes and no quotes.  Then i made the field names unique and all lower-case and i tried using a query as the source rather than a table.  Then i tried editing my Sql code to the most basic example on the page
http://www.techonthenet.com/sql/insert.php
but even once i took out the "Select" part the error message kept grumbling about it.  So i think i might need to write the code in Gedit or something and then copy&paste the code into a fresh query.

I'm not quite sure what i'm doing wrong but that is not a big surprise.  I've got a bit of reading to do and some of that should make more sense now i've played around a bit.
Thanks all and regards from
Tom :slight_smile:

If you are using Ubuntu, you can download MDB Viewer. It will open a Access database and reveal the database. With this you can export the tables. I think it does have some problems with exporting queries, but I'm not sure. It has been some time since I played around with it. Also, I seem to remember that Base can connect to an Access database using a driver (the bottom part of the Base wizard). As I understand it, the driver is part of the windows os. I think it may be AOO. If you use a Windows OS, then it should be in the list of drivers in the bottom drop down list with default value JDBC.

--Dan

Hi Tom,

"SQL Status: HY000 Error code: 1000"
"SQL Status: HY000 Error code: 1000 syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE"

I hope that you are using the Tools > Options > SQL menu instead of trying to do this via the Query designer ?

Alex

Hi :slight_smile:
Nope.  Thanks for the suggestion!  Another thing i think i was doing wrong was to use
Insert - "Query using Sql"

I'm not able to try your suggestion until much later but it's good to have soemthign else to try.
Thanks and regards from
Tom :slight_smile:

Hi Tom,

Nope. Thanks for the suggestion! Another thing i think i was doing wrong was to use
Insert - "Query using Sql"

The Query using SQL and Query Design modes only work for pure retrieval
queries, i.e. SELECTS. You can not insert or update when in these modes.
In order to insert or update using SQL, you have to use the Tools > SQL
menu from the main Base window.

Alex