Create Relationships

[snowboarder234]

Clearly, I'm not getting this. I've created the first 2 tables of a total of six I planned for this database. However, after creating the first two I can't get them to "relate" like I had envisioned. I don't want to proceed breaking down the rest of the excel import into more tables until I get the first two tables up and running.

tblAdvisor is one to many -- one advisor can have many students
tblStudents is one to one -- one student can only have one advisor

My goal is to open the tblStudent and have the drop down field below each student display their assigned advisor. Right now the drop down fields are empty, even though I've linked the files.

Help please. Database attached. Thank you.



EDIT: I reloaded the database as an mdb file. See 3rd posting with new attachment.

[orange]

I have acc2003 so can not open accdb.

As for your tables as I see it

You have Advisors and Students,
then the rules
-one advisor can have many students
-one student can only have one advisor

To show which Advisor "advises" which Students, you need a junction table.

For example, let's say we have
Advisors (Id,ProfName) ( could have more fields - this is just for concept)
01 ProfBob
02 ProfSammy
03 ProfAssam

And
Students (Id, StudentName)
01 Jim Smith
02 Jane Doe
03 Bunny Rabbit
04 Easter Bunny
05 Hans Zoff

where
ProfBob advises Jim Smith and Jane Doe and Hans Zoff
ProfSammy advises Bunny Rabbit
ProfAssam advises Easter Bunny

The junction table StudentAdvisors(SA_Id,ProfId (FK to Advisors),StudentId (FK to Students))

StudentAdvisors
01,01,01
02,01,02
03,01,05 <--iD =3,ADVISORs.ID=1,Students.ID=5 {Interpreting this record ProfBob advises Hans Zoff}
04,02,03
05,03,04

An advisor can advise >1 Student
A Student has 1 Advisor.

You would need code to ensure unique Student-Advisor pair (only 1 Advisor for any given Student) and/or
unique compound index on ( StudentId + ProfId)

[snowboarder234]

I reloaded the database as an mdb file to reach a wider audience. Unfortunatly, I can't figure out how to delete the Access 2010 attachment.

[orange]

Just looking at your mdb quickly.

You're bringing in data from Excel, you have a bunch of data basically in a flat file. You don't show any queries about how you extract things from the excel import into your other tables.

You have 118 records and 104 distinct advisors.

It does not appear to me that your tables are normalized, but you know your environment and needs better than I do. I see Grants, Contacts, project.... If an advisor can "advise" many students, WHY do you have studentid in the Advisor table?

I strongly suggest you get a data model of what you are trying to achieve/support. Test that model and make sure it is what you need.
What other things do you use the data for?

The sooner you get a model of what you need, the easier it will be to create the extraction, reformat whatever routines. I think you're only showing a piece of what you are trying to accomplish---just my interpretation based on what I'm seeing.

I think you could benefit from reviewing some online videos related to database design.
Here are some links to videos and site that should help. These are geared to database design.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

[snowboarder234]

Just looking at your mdb quickly.

You're bringing in data from Excel, you have a bunch of data basically in a flat file. You don't show any queries about how you extract things from the excel import into your other tables.

You have 118 records and 104 distinct advisors.

It does not appear to me that your tables are normalized, but you know your environment and needs better than I do. I see Grants, Contacts, project.... If an advisor can "advise" many students, WHY do you have studentid in the Advisor table?

I strongly suggest you get a data model of what you are trying to achieve/support. Test that model and make sure it is what you need.
What other things do you use the data for?

The sooner you get a model of what you need, the easier it will be to create the extraction, reformat whatever routines. I think you're only showing a piece of what you are trying to accomplish---just my interpretation based on what I'm seeing.

I think you could benefit from reviewing some online videos related to database design.
Here are some links to videos and site that should help. These are geared to database design.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

This was my original data model.