MS Access SQL helped needed for SQL beginner..
 

Can someone tell me what is wrong with the following code please?
Im trying to insert 3 rows of data into table STAFF in Access 2003 and keep getting an error saying "Charectors found at the of SQL statement"

INSERT INTO STAFF
VALUES ('C01', 'Steve', 'Zion', 'Worcester', 'WR25PF', 1, 'T01');
VALUES ('C02', 'John', 'Scarry', 'Worcester', 'WR25PF', 3, 'T01');
VALUES ('C03', 'Paul', 'Andrews', 'Worcester', 'WR25PF', 7, 'T01');

Any help will be greatly appreciated!!

can you post the table design?

you also need to put in brackets the field names you're inserting into

for e.g.

insert into dbo.STAFF
(col1, col2, col3, col4, col5, col6)
VALUES ('C01', 'Steve', 'Zion', 'Worcester', 'WR25PF', 1, 'T01');
VALUES ('C02', 'John', 'Scarry', 'Worcester', 'WR25PF', 3, 'T01');
VALUES ('C03', 'Paul', 'Andrews', 'Worcester', 'WR25PF', 7, 'T01');

CREATE TABLE STAFF (
STAFFID CHAR(3) NOT NULL UNIQUE,
FIRSTNAME VARCHAR (15) NOT NULL,
LASTNAME VARCHAR (20) NOT NULL,
TEAM INT NOT NULL,
JOINDATE DATE,
WAGEPERHOUR NUMBER,
AMBID CHAR(3) NOT NULL,
PRIMARY KEY (STAFFID));
------

INSERT INTO STAFF (STAFFID, FIRSTNAME, LASTNAME, TEAM, JOINDATE, WAGEPERHOUR, AMBID)
VALUES ('S01', 'Steve', 'Carrow', 1, '14/04/1980', 12.54, 'A01');
VALUES ('S02', John', 'Parry', 3, '23/03'1999', 9.44, 'A02');
VALUES (S03', 'Hannah', 'Jones', 7, 12/12/2006', 10.38, 'A03');
------

Still returns errors :(

Thanks for your help so far thougth Kopite :)

try this to create the table...



CREATETABLE dbo.STAFF (
STAFFID CHAR(3) NOT NULL UNIQUE,
FIRSTNAME VARCHAR (15) NOT NULL,
LASTNAME VARCHAR (20) NOT NULL,
TEAM INT NOT NULL,
JOINDATE datetime,
WAGEPERHOUR int,
AMBID varchar(20)
PRIMARYKEY(STAFFID));

then this to insert...

------
INSERT INTO dbo.STAFF (STAFFID, FIRSTNAME, LASTNAME, TEAM, JOINDATE, WAGEPERHOUR, AMBID)
VALUES('S01','Steve','Carrow', 1,'14-Apr-1980', 12.54,'A01');


INSERT INTO dbo.STAFF (STAFFID, FIRSTNAME, LASTNAME, TEAM, JOINDATE, WAGEPERHOUR, AMBID)
VALUES('S02','John','Parry', 3,'23-Mar-1999', 9.44,'A02');


INSERT INTO dbo.STAFF (STAFFID, FIRSTNAME, LASTNAME, TEAM, JOINDATE, WAGEPERHOUR, AMBID)
VALUES('S03','Hannah','Jones', 7,'12-Dec-2006', 10.38,'A03');

Sorry if this is noddy answers, but are your dates and numbers definitely formatted as dates and numbers in the spreadsheet (excel?) your pasting from?

Also, was the date error in the middle row(' not /) a typo on the forum? Definitely no spaces in the data either?

God it's years since I did much in Access!!

Didnt work :(

Again, thanks for youe help!

what's the error?

Dave,

Typo was a mistake, sorry :(

I typed the code in by hand, rather than importing from Excel (have to for Uni).
I cant define the NUMBER in Acess for some reason. I wanted to define it as NUMBER(4,2) but it errored.

Weirdness....

@Kopite - Same error dude.

Itll let me enter single rows of data, but not multiple rows ??!!

Are you doing it as an import or paste into the table?

Just wondering if for some reason it's not picking up the rows? Could be worth creating a new table with that data set rather than importing to the existing one, and seeing what that new table looks like (number of collumns, formats?)

so you can do it row by row?

Yeah but I need to:
"Write two SQL statements to add 3 rows of data to each of the two tables you created above. You can create any type of specific data for each cell. Use a DBMS to actually enter the data and provide a screen print of both tables as well as the two SQL statements."

Ive created two tables; STAFF and AMBULANCE and need to obviously type the coding to either add 3 rows of data to STAFF and AMBULANCE seperately, or add the data to both at the same time.

I find it weird that I can type single lines of code and it works, but not multiple lines?

Who said learning database management was easy eh lol

There's a "mr excel" help guide somewhere online, might be worth seeing if there's an access one?

Did you try creating the new table?

Im doing all through Access, typing all the code rather than imprting anything (have to type the code in for Uni).

Ive created two tables so far with no problems, and I can add single rows of code with no issue, but I cannot add multiple code for some weird reason?

Damn databases..

Deleted post - pointless question

Im typing it in Access through the 'Create query in design view' method, in SQL view.
I can type code to work out all kinds of joins, aliases, constraints and conditional formatsw...but cant add 3 rows of data to a table :D lol

Stoopid.

Easiest way to do this, is create an insert query using the query maker (in design view) for access, then just use the SQL view (to actually look at the code behind the GUI made query!

This will provide the query for you, in SQL text which you can then provide as evidence in your write-up.. (been here done this about 4 years ago lol!)

Access doesn't like the semi colon character