SQL (MS Access) - Tilted Forum Project Discussion Community

Artsemis · 02-03-2004, 04:05 PM

Working on a class assignment and I have just a couple questions here...

1) Is there any way to create multiple tuples and add them to a table using the same Query? If I do the add command and end it with a semi-colon, I get a message about text being after the end of the query; if I dont use a semicolon, it says it expects one after the first "add" statement.

2) Okay, the book gives many examples about relationships. Lets use this for example... say we have Students( sid, age, gpa ), and Enrolled( sid, courseID, grade ).
Now, here's the problem... the book has the following as Primary keys:
Students - sid
Enrolled - sid, courseID

And the following Foreign Key:
Enrolled - sid (References Students)

Now, this causes a problem in my eyes. As it appears, it seems a single student is unable to be "enrolled" in more than one course. It seems to me that when you add a student for the second time to the table to be Enrolled for a course (different courseID) it violates the Primary Key (sid) because that students sid hasn't changed.

For now, I deleted my two Primary Key's in "Enrolled" and just left the Foreign Key to keep the two tables in sync... am I missing something or is the book not compatible with Access's SQL?

Thanks~!

cheerios · 02-03-2004, 04:48 PM

ok can't help you on the first one, but the second one I can.

here's the deal. a PK has to be unique for each entry, right? so with the primary key of Students being sid and the PK of Enrolled being BOTH sid and courseID, then both sid & courseID have to match to cause a key constraint.

so you could have
sid_______CourseID
9887--------1235
9887--------1236
both in enrolled and not cause an error. theo only time you'd have problems is if the same student tried to enrol in the same course multiple times. am i making sense?

Artsemis · 02-03-2004, 04:53 PM

Ahh, thanks! I thought when they was together (sid, courseID) it meant one or the other, not both =)

Anyone on the first question? =)

gigawatz · 02-07-2004, 12:17 AM

Unfortunately, I cannot provide any good news. Everytime I attempt to create multiple SQL statements into a single SQL instance in Access it gets pissed.

I have not thoroughly researched doing this, cause I don't rely on access to do my db work, however, through experience, I do not think that is possible. It is perfectly valid in any other database application that I've ever run into.

I wish you luck.

02-03-2004, 04:05 PM	#1 (permalink)
Artsemis Insane Location: West Virginia	SQL (MS Access) Working on a class assignment and I have just a couple questions here... 1) Is there any way to create multiple tuples and add them to a table using the same Query? If I do the add command and end it with a semi-colon, I get a message about text being after the end of the query; if I dont use a semicolon, it says it expects one after the first "add" statement. 2) Okay, the book gives many examples about relationships. Lets use this for example... say we have Students( sid, age, gpa ), and Enrolled( sid, courseID, grade ). Now, here's the problem... the book has the following as Primary keys: Students - sid Enrolled - sid, courseID And the following Foreign Key: Enrolled - sid (References Students) Now, this causes a problem in my eyes. As it appears, it seems a single student is unable to be "enrolled" in more than one course. It seems to me that when you add a student for the second time to the table to be Enrolled for a course (different courseID) it violates the Primary Key (sid) because that students sid hasn't changed. For now, I deleted my two Primary Key's in "Enrolled" and just left the Foreign Key to keep the two tables in sync... am I missing something or is the book not compatible with Access's SQL? Thanks~! __________________ - Artsemis ~~~~~~~~~~~~~~~~~~~~ There are two keys to being the best: 1.) Never tell everything you know

02-03-2004, 04:53 PM	#3 (permalink)
Artsemis Insane Location: West Virginia	Ahh, thanks! I thought when they was together (sid, courseID) it meant one or the other, not both =) Anyone on the first question? =) __________________ - Artsemis ~~~~~~~~~~~~~~~~~~~~ There are two keys to being the best: 1.) Never tell everything you know

02-03-2004, 04:48 PM	#2 (permalink)
cheerios Banned Location: 'bout 2 feet from my iMac	ok can't help you on the first one, but the second one I can. here's the deal. a PK has to be unique for each entry, right? so with the primary key of Students being sid and the PK of Enrolled being BOTH sid and courseID, then both sid & courseID have to match to cause a key constraint. so you could have sid_______CourseID 9887--------1235 9887--------1236 both in enrolled and not cause an error. theo only time you'd have problems is if the same student tried to enrol in the same course multiple times. am i making sense?

02-07-2004, 12:17 AM	#4 (permalink)
gigawatz Crazy Location: Pittsburgh	Unfortunately, I cannot provide any good news. Everytime I attempt to create multiple SQL statements into a single SQL instance in Access it gets pissed. I have not thoroughly researched doing this, cause I don't rely on access to do my db work, however, through experience, I do not think that is possible. It is perfectly valid in any other database application that I've ever run into. I wish you luck.