Using SQL in Acess???

[doj]

Hi all,
I am creating tables in SQL view in Acess, Have managed to create the tables, but am having a little trouble understanding what to do next?

I have an INSERT statement to add names, addresses etc, but cant seem to get it to work.

I am trying to insert into the SQL view in queries, thats where i have created the tables.

Should i use the same querie view or another one, or simply imput the information manually.

INSERT INTO Student ( Student_No, Name, Address, Tel_No, Course_No )
SELECT 'A001', 'Jones B', '12 New Road', '469006', 'HNC34';
this is the statment that i am trying to use:

hope someone can help
doj

 

[KeithG]

Create a seperate query with your SQL

 

[tehNellie]

INSERT INTO Student ( Student_No, Name, Address, Tel_No, Course_No )
SELECT 'A001', 'Jones B', '12 New Road', '469006', 'HNC34';

Should be:

INSERT INTO Student ( Student_No, Name, Address, Tel_No, Course_No )
VALUES( 'A001', 'Jones B', '12 New Road', '469006', 'HNC34');

 

[doj]

thanks keithG and Technellie,
I did what you said and found that technellie was right it didnt work.
And my lecturer gave me that as an example.
Anyway another question.
Can i enter more than one line at a time?I'm trying this but it doesnt seem to work

INSERT INTO Student ( Student_No, Name, Address, Tel_No, Course_No )
VALUES ('A016', 'Davis T', '25 Powys Ave', '417851', 'HNC60',
VALUES 'A029', 'Lewis S', '24 High Street', '256985', 'HND12');

If I have to do them one at at time it will take forever.
thanks again
doj

 

[KeithG]

Have you thought about adding your records though a recordset?

 

[doj]

HI Keith,
I've only been study database for a very short time and am not upto speed on all the things that i should
what actually is a record set?
Would it help me to imput more than one line of data at a time?

or would one query at a time make things easier in the long run?

thanks again

doj

 

[tehNellie]

What format are the records that you want to insert into your table in?

There are numerous ways you can INSERT records into your table. You might, if the records are in, say, a spreadsheet, just be able to import them directly into your table. You might need to drop them into a temporary table while you do some formatting and then insert them or you could create a form that will let you enter the details you need to populate the INSERT query without having to go to all the bother of writing the whole thing out every time.

 

[doj]

HI Technellie,
thanks for all the help. All i am aware of at the moment is that i have to add the code for the names addresses etc in the way shown.
It seems to be very time consuming, i will create the information in excel and try and send it over?
hope i can come back and get more advice.

Another question if you dont mind? What code do i need to imput for date and time? Also email and postcode?

I had a list of most of the codes but they dont seem to be there.

thanks again,
Doj.

 

[tehNellie]

What I was trying to get at is that if you already have this information in, for example, Excel then you can dump the record en masse into your table rather than having to add them record at a time. You can either attempt to drop them straight into your table, or you can import them into a temporary table in Access do any necessary formatting on the columns if needed and then perform a single query to drop all the records into your table.

However if you are getting single records coming through every now and then, you might be better off looking at creating a form. You'll still need to enter all the values into text boxes (or select values from a listbox maybe) but it will remove the need to have to type the whole INSERT INTO gubbins every time.

Another question if you dont mind? What code do i need to imput for date and time? Also email and postcode?

I'm not sure I follow, but there is a date/time format on the column you can set for the date.

With regards to the email column you can set a validation rule that the data must be in the format"LIKE *@*.*" which will at least ensure that the data going into that column must be something@something.something which is, broadly speaking, an email format or the insert will fail.

 

[doj]

Another problem

I've been trying to create another table, it requires two primary keys
this is the code that I have inserted


CREATE TABLE Defendant_and_Case (Case_Number_No TEXT(5), Defendant_Number TEXT (5) CONSTRAINT Case_Number_pk PRIMARY KEY CONSTRAINT Defendant_Number_pk PRIMARY KEY);


It accepts it,but when i go to insert it into a relationships table it say that a primary key allready exists.

What am i doing wrong, otherwise all my tables are fine?
doj

 

[neileg]

You can't have two primary keys. You can have two fields that are combined into a primary key, but it's only one key.

 

[doj]

Hi again,
How would you set up a primary key with two fields?
The notes I have had off my lecturer werent very good i am afraid.

thanks again

doj

 

[tehNellie]

If you highlight the two columns that you want in table design view and then click on the primary key icon (looks like a key), it will turn both those columns into your composite primary key

 

[doj]

Hello again,
I am now trying to insert information into my tables.
1 I have checked that all spellling is correct,
2 made sure all fields are the same length,
the code acceptable: I think?

INSERT INTO Booking (Booking_No, Crown_Court_No, Case_No, Startdate, Enddate)
VALUES ('B001'. 'C001', '0001','27:Apr:07', '31:May:07,');

But it keeps saying that my fields are wrong.

any ideas what it might be?

doj

 

[Premy]

Hi Doj, try this Add-query:

INSERT INTO Booking ( Booking_No, Crown_Court_No, Case_No, Startdate, Enddate )
SELECT "B001" AS Expr1, "C001" AS Expr2, "0001" AS Expr3, "27:Apr:07" AS Expr4, "31:May:07" AS Expr5;

Regards,
Jaime

 

[doj]

Well I am completley flummoxed now.
I created all my tables again in a new database,got all the relationships set up ok.
went to insert information and i get these four faults:

type conversion failure,
key violations,
lock violations
validation rules violations.

The code that i am using came from a good source, all my fields are set the same field size,

any ideas guys please

doj

 

[Premy]

Hi DOJ, could u attach a tiny sample mdb? I'd take a look for u.

Regards,
Jaime