I’m trying to create a new table in a subquery by feeding it part of the data from an already existing table.
In Access the SQL code would be:
select * into ODIVELAS_TEAM
from Formandos
where LOCALIDADE = “ODIVELAS”;
This doesn’t seem to work in Base.
Am I doing something wrong? Is there another way to do this?
Hello,
Select * into is not a function of Base or Access. This is a database SQL statement of which Base and Access are only front ends to. You need to know the database you are using in order to determine if that SQL is allowed. Most commonly in Base, new users tend to create a Base file with the default HSQLDB embedded v1.8 database. This SQL is not available there. It is available in a database such as PostgreSQL.
Hi! I don’t know where to check if I’m using HSQLDB or another but this code doesn’t work either:
select Formandos.* into ODIVELAS_TEAM
from Formandos
where LOCALIDADE = “ODIVELAS”;
there seems to be a syntax error?
On the main Base screen, at the bottom is the status line. In the center is the database used and on the left is the connection type.
The SQL presented in your comment is basically the same as in your question. Won’t work with HSQLDB embedded. Refer to the database documentation (when you discover which one is used) for capabilities. Most likely you are using HSQLDB embedded. Manual here → Hsqldb User Guide
@Ratslinger,
I’ve confirmed I’m using HSQLDB.
Is there another way to create a new table using a subquery?
Do not understand why you want this for a sub form. Possibly better explain what you are doing. You can filter data with a sub form.
@Ratslinger,
It’s for a class. We’re learning how to use Access but since I don’t have it, I wanted to know if there was a similar way to do it in Base.
The goal was to create a new table and put the filtered data in it at the same time.
Although you can use the database from Access in Base, the remainder of Base (front end) is quite different from that of Access. The Access front end has many more functions than Base. Base can accomplish many of those functions but it then requires macro code. Can’t see this being used for what you need.