Help with setting up database

[peachesflora]

Relatively new to access, just not sure if how I am setting up my database would allow me to do what I need it to. Unless it make sense to do otherwise, I set it up with two tables.

Student Table
First Name
Last Name
Email
Cell Phone
College
Department

School Table
College
Department
School Street Address
City


State
ZIP

What I want is to create a contact like database. I want to use a form to input new student data. Is there a way for it to automatically fill-in the address if I input the college and department? How do I base my form on the two tables so it does this?

Many thanks.

-Access beginner

[TheShabz]

I would add a third table for department, as one college can have many departments. Also, if you consider a double-major, each student could possibly have multiple departments. You would then need a 4th table to facilitate a many-to-many relationship between Student and Table. Here's how it would break down with the m2m relationship.

tblStudent
studentID (primary key)
FirstName
LastName
Email
CellPhone
Department (foreign Key)

tblCollege
collegeID (primary Key)
collegeName
etc

tblDepartment
DeptID (primary key)
DepartmentName
etc
collegeID(foreign key)

tblStudentDept
studentDeptID (primary key)
studentID(foreign key)
departmentID (foreign key)

*Notes*
1. notice that college and department are not in all of the tables anymore. This is due to the relationships you will be creating in the relationship window
2. it is imperative that you create primary keys which are unique to each record. without these, using Access is pointless
3. remove any spaces and special characters (!@#$%) from your table and field names. This makes querying much less tedious.
4. read up on the following terms before continuing your database "relational database", "database relationships", "ms access forms"). That should give you all the info you need to get going and is all available for free on Google.

That's pretty much it. Good luck with your db and post back with any more questions.

Cheers!