MS Access SQL helped needed for SQL beginner..

[c0sie]

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!!

[Kopite]

can you post the table design?

[Kopite]

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');

[c0sie]

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

[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));

[Kopite]

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');

[DaveG28]

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!!

[chris_dono]

Originally Posted by c0sie

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!!

Access doesn't like the semi colon character

[chris_dono]

Here's one way to do it:
Create a module and paste the following:

 
Sub CreateRecs()
DoCmd.RunSQL "INSERT INTO STAFF (STAFFID, FIRSTNAME, LASTNAME, TEAM, JOINDATE, WAGEPERHOUR, AMBID) " & _
"VALUES('S01','Steve','Carrow', 1,'14-Apr-1980', 12.54,'A01')"
DoCmd.RunSQL "INSERT INTO STAFF (STAFFID, FIRSTNAME, LASTNAME, TEAM, JOINDATE, WAGEPERHOUR, AMBID) " & _
"VALUES('S02','John','Parry', 3,'23-Mar-1999', 9.44,'A02')"
DoCmd.RunSQL "INSERT INTO STAFF (STAFFID, FIRSTNAME, LASTNAME, TEAM, JOINDATE, WAGEPERHOUR, AMBID) " & _
"VALUES('S03','Hannah','Jones', 7,'12-Dec-2006', 10.38,'A03')"
End Sub

This will insert the three rows. It will prompt you for each time it inserts.

You can't insert multiple items using the VALUES specification of the INSERT INTO syntax. For multiple items, you have to use INSERT INTO with SELECT

 
INSERT INTO table1 (id, value)
SELECT id, value FROM table2

is an example.

Let me know how you get on.
Cheers
Chris