Can't setup a one-to-many relationship

[froot loops]

Hi

I started access and i can't seem to get the relationships working.

i have three tables

table one
professorID (primary)
other fields

table two
CourseNumber(foreign)
Other fields

table three
ProgramID (foreign)
other fields

I have established a one to many relationship between courses and programs table but i can't set up a relationship between professor and courses.

One professor can teach many courses and each program can contain many courses.

Everytime i tried to link up professor table and course table i get an invalid index or relationship message. I don't understand why. Is it because my keys are wrong or i have it set up badly?

Thank you!

 

[David Eagar]

ProgramID is a text field - you'll be struggling to link that to a numeric one

 

[froot loops]

thank you i didn't catch that but still i can't create a relationship between prof and courses table

 

[David Eagar]

So what have you changed?

 

[froot loops]

I change programid field number data type and added a CourseNumber field to professor table. But that does not implement some of the require situtations. Like say two professors teach the same course.

 

[David Eagar]

Does this meet requirements?

 

[boblarson]

Here's my attempt.

And David - You don't need to select Cascade Updates normally. It would possibly be something if you thought your primary key could change (and another reason why I use autonumbers as they wouldn't).

 

[boblarson]

Oh, and I didn't do anything but change the tables and relationships. I didn't touch the forms.

 

[froot loops]

Thank you

What was my problem? Like what did i do wrong? For the program table i can't use my orignal primary key? u can't have an autonumber data type with a number datatype? I know it has the be same but they are both numbers

 

[boblarson]

Thank you

What was my problem? Like what did i do wrong? For the program table i can't use my orignal primary key?

For primary keys, I suggest using a surrogate key (autonumber). The keys are ONLY for the system to maintain the relationships between data. You should not really be concerned about them. With the computing power available today, it really is no big deal to have an extra surrogate field which actually can be indexed and searched more efficiently than perhaps a natural key.

Let Access manage the keys. The primary key - autonumber (long integer) and the foreign key is the same field in the other table but set to long integer. I suggest using the SAME name for the foreign key as it makes it very easy to know what is the primary and foreign keys.

 

[froot loops]

ok thank you

Onto my next question

Now i can create subforms with a main form

I have professor as the main form and courses as a subform but the subform is not populated. I have to enter the info myself?

The relationship is established so that means i have multiple courses taught by one prof.

 

[boblarson]

Did you drag and drop the courses table onto the professors form? If you do, it should link it all together. The key is that the master/child links have to be set up. But you will still need to select the course after entering the professor info.

 

[froot loops]

it won't let me. it will just add more records to the professor form.
I get a msg about the fields for the data i tried to paste don't match the fields on the form
than it asked if i wanted to paste the fields only with the same name

 

[boblarson]

it won't let me. it will just add more records to the professor form.
I get a msg about the fields for the data i tried to paste don't match the fields on the form
than it asked if i wanted to paste the fields only with the same name

What are you pasting? You shouldn't be pasting anything. Just typing in data.

I'm not going to have time to look at the database again and work through it until maybe tomorrow night (although doubtful as I have my son's basketball game to attend). (I don't have Access 2007 at work).

 

[froot loops]

It's ok no rush. It's not due until next week. thank you for all ur help so far


Also when u added the primary key for both the course and programs table. My primary key has to CourseNumber for course table and ProgramID can't be auto number because the program id's have there own code like T127

I didn't paste the records. I tried to type it in but it won't let me it would tell me that i'm duplicating records. Like say for example i have two professors teaching the same course but when i tried to type it in Access will automatically throw me an error. I thought after the setup the relationships it would allow u to enter multiple value. Since it's a one to many relationship.

If it doesn't how to do enter the records than? Is there a way to fix it. Access will tell that i have to change the my primary keys and re index but i can't change any of them it's part of the requirements in my assignment

And also My subform is from the courses table i got an error msg saying i can't change it because ProgramID is in Programs Table.

 

[milehighfreak]

Multiple professors linked to multiple courses, and vice versa, is not one-to-many; it is many-to-many.

Create a third table, include an autonumber PK of "link ID", create a field for professor ID, and a field for course ID.

Make sure the data types are the same, then link the prof ID to prof's IDs, and courses to course IDs. Then create a query drawing all fields from the prof table, all fields from the course table, and both linked fields from the third table. Do not include Link ID from the third.

Then create a form including all but the key prof field, all but the key course field, and both linked fields. Lock and hide the two key fields in the subform, but do not delete them. Congratulations, you now have a working many-to-many relationship.

Edit: Example attached for you. I would appreciate if anybody could help with my multi-value question also, I need to figure it out by monday.

Edited a second time: Copy and paste data into the structure you've set up by using the subform, do not try to copy ID keys and make sure your columns are in the same order. You can copy it directly into the Courses table too but that means you'll manually need to create every link in the intermediate field.