SQL DDL Help!!!!!!!!

[Brain]

I want to practice making queries but it doesn't seem to work.
I can do basic SQL, but SQL DDL doesn't work. Same applies for DML although I am following the correct steps. Please help.
For example CREATE TABLE. This is what I have done thus far:

CREATE TABLE
NO ORDERS
(Sname varchar(20),
Fname varchar(20),
Address varchar(30),
PostCode varchar(20),
Phone number(15));

Is it something about the amount of spaces I have or the use of brackets? I have the correct field names. Help will be much appreciated.

 

[Rabbie]

Try looking in Access Help for "Create Table" .

Change varchar to String to make a valid Access datatype. Also enclose NO ORDERS in square brackets if you must have a space in the name otherwise use an underscore instead.

 

[Brain]

Try looking in Access Help for "Create Table" .

Change varchar to String to make a valid Access datatype. Also enclose NO ORDERS in square brackets if you must have a space in the name otherwise use an underscore instead.

Hi sir. Thanks for your help. Regarding looking for access help; they all follow the same layout and it goes wrong. I have no spaces while I'm not familiar with String and what it means. Thanks.

 

[Rabbie]

Hi sir. Thanks for your help. Regarding looking for access help; they all follow the same layout and it goes wrong. I have no spaces while I'm not familiar with String and what it means. Thanks.

The information in Access Help about Create table includes the following

Syntax

CREATE [TEMPORARY] TABLE table (field1 type [(size)] [NOT NULL] [WITH COMPRESSION | WITH COMP] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])
The CREATE TABLE statement has these parts:
PartDescriptiontableThe name of the table to be created.field1, field2The name of field or fields to be created in the new table. You must create at least one field.typeThe data type of field in the new table.sizeThe field size in characters (Text and Binary fields only).index1, index2A CONSTRAINT clause defining a single-field index. multifieldindexA CONSTRAINT clause defining a multiple-field index.

The query you posted had NO ORGERS where the table name should be so you either needed to remove the space between NO and Orders or enclose it in square brackets [NO ORDERS]. If you are going to use Access you need to learn which datatypes are valid. String is equivalent to varchars in other languages

 

[Brain]

Okay thanks. I have done what you have recommended. The create table worked but I can't open the query again as it says the table has been made, in this case the Nrders table. I can open that new table but not the query I made it with.

 

[Banana]

Are you saying that you put the DDL statement in a query, saved it or executed it but you can't open it in SQL view again?


Also as a general FYI, Access help files has a 'Comparsion of Data Types', which lists all possible data types for different access methods. It notices that while TEXT is the preferred word, it accepts CHAR, VARCHAR, ALPHANUMERIC, STRING, and CHARACTER as synonyms.

 

[Rabbie]

Okay thanks. I have done what you have recommended. The create table worked but I can't open the query again as it says the table has been made, in this case the Nrders table. I can open that new table but not the query I made it with.

Your query is an action query and when it is opened it will attempt to create a new table. As that table already exists it errors which is correct. If you want to change the query then open it in design mode.

 

[Brain]

Are you saying that you put the DDL statement in a query, saved it or executed it but you can't open it in SQL view again?

I executed it and it worked. I have now opened thanks but there is no data. I know I haven't specified it but is there a way where you can insert data from different tables using the same DDL statment?
CREATE TABLE
NO_ORDERS
(
Sname varchar(20),
Fname varchar(20),
Address varchar(30),
PostCode varchar(20),
Phone String(15)
);

This is my relationship below. I want to list the customers who haven't ordered yet, which is identified by thge OrderID which will equal zero if that is the case. Thanks.

 

[Banana]

You would not actually use CREATE TABLE for that. Rather you'd use SELECT INTO...

 

[Brain]

You would not actually use CREATE TABLE for that. Rather you'd use SELECT INTO...

So I would have to do:
INSERT INTO Nrders (the table)
SELECT Customer ID, Sname, Fname, Address, PostCode, Phone, OrderID
FROM Customer, Orders

I don't know the next step but I thik I have to do some sort of INNER JOIN. This is getting confusing!!!!!

 

[Banana]

No.

That's appending.

SELECT 
   [Customer ID], Sname, Fname, Address, PostCode, Phone, OrderID 
INTO 
   No_Orders 
FROM 
   Customer 
INNER JOIN 
   Orders ON Customer.[Customer ID]=Orders.[Custoemr ID];

(The join is a guess).

 

[Brain]

No.

That's appending.

SELECT 
   [Customer ID], Sname, Fname, Address, PostCode, Phone, OrderID 
INTO 
   No_Orders 
FROM 
   Customer 
INNER JOIN 
   Orders ON Customer.[Customer ID]=Orders.[Custoemr ID];

(The join is a guess).

That seems so simple. I'll try it and thanks very much. But there is no INSERT Into.

 

[Banana]

Just so you know, I'd expect it to crap out if there's already a table named Nrders.

Best of luck.

 

[Brain]

Just so you know, I'd expect it to crap out if there's already a table named Nrders.

Best of luck.

Yeah it did crap out. Dang.

 

[Banana]

It'll work if you delete the original Nrders.

Or are you actually wanting append query?

 

[Brain]

Yeah I was trying to append it yes. BTW what is the answer to your signature (PORSCHE QUESTION) aswell. It's really bugging me. (LOL).

 

[Banana]

Then INSERT INTO is actually what you want.

To summarize-

We use CREATE TABLE to create a blank table.
We use SELECT ... INTO to create a new table filled with existing data from other table.
We use INSERT INTO to add new/more data to a existing table either with ad hoc values (VALUES clause) or data from other tables (SELECT ... FROM clauses).

Did that make sense now?

 

[MSAccessRookie]

Yeah I was trying to append it yes. BTW what is the answer to your signature (PORSCHE QUESTION) aswell. It's really bugging me. (LOL).

I am not sure this is the answer that he is looking for, but if Bill drove the Porsche off of a cliff, It would accelerate to 9.8 m/s² before it hit the ground, and I do not think it will use much gas in the process.

 

[Brain]

Then INSERT INTO is actually what you want.

To summarize-

We use CREATE TABLE to create a blank table.
We use SELECT ... INTO to create a new table filled with existing data from other table.
We use INSERT INTO to add new/more data to a existing table either with ad hoc values (VALUES clause) or data from other tables (SELECT ... FROM clauses).

Did that make sense now?

Uh huh. Thanks that cleared a lot up and will hopefully ensure me to do things by myself now. The only problem is sometimes I have to get the format correct i.e. where the breackets go, which line etc.
This is one of the queries I done with your help. I don't know how it worked but it did. Thanks for your help.

CREATE TABLE
NO_ORDERS
(
Sname varchar(20),
Fname varchar(20),
Address varchar(30),
PostCode varchar(20),
Phone String(15)
);

 

[Banana]

Maybe you already knew but you know that you can use Query Builder for all those queries, right? That you can just do it graphically then toggle to SQL view to see how Access writes it. (Unfortunately, the SQL Access writes isn't always pretty, with lot of extras ()s, []s and verbose definition (tablename.columnname even when columnname is good enough, but as long you know about its quirks you still can learn thing or two about the SQL it writes for you).

HTH.

BTW, MSAR is correct.