Relationships: I don’t seem to have any

V

VA Work-Study

Limited knowledge here, all I know about Access comes from an intro class.

Wondering if my database is a smart setup. What I have is a table that
contains information about students (such as ID number, address, GPA, major,
etc.), and then I have a table that contains all the majors at the college. I
made a form with all the Student info on it, and then in design, I inserted a
combo box using data from the majors table (this is to indicate the students
major).

When a major is added to the major’s table, it shows up on the student form
okay. Everything seems to work fine, but I am wondering if my setup is wrong.
I also have tables for Minors and certificates that work the same way.

I’ve made various reports that seem to work as well. I just feel I’m doing
something wrong.

Thanks for any help, and I suspect this won’t be my first question here?
 
C

Cheese_whiz

Hi VA,

Looks sort of like homework to me....

That aside, I'd suggest you consider removing the major/minor/cert info from
the student table and find (create) a better place for it.

If the place you create for that info included a place for the studentID
number (primary key from students table), then you'd have something you could
'relate' ...

Main form students, subform ???

CW
 
T

tina

most "introductory" Access classes teach you the basics of using the
software, but little or nothing about how to design a relational database -
and that's at least half the job. if you really want to learn how to use the
Access tool to build relational database software programs, you need to
learn at least the basics of relational design principles. for more
information, go to http://home.att.net/~california.db/tips.html#aTip1.

hth
 
V

VA Work-Study

Thanks for the link.

I still question whether the way I am doing it is wrong.
 
D

Dale Fye

It sounds like you have it right, given your table structure, which I agree
sounds like it needs some "relational design" work. As an example of this,
instead of having a Majors table and a Minors table, I would probably only
have a "Degrees" table, which might contain fields like: (College,
Degree_Name, Major(Y/N), Minor(Y/N), Bachelors(Y/N), Masters(Y/N), PHD(Y/N).
You could then use this table to create a query (qry_Bach_Majors) which only
includes the degrees that award bacheloreate majors.

When you create a form, you identify the RecordSource for that form, usually
a table or query. In your case, it should be your Students table. In most
cases, each control on that form will have a "control source" (you can locate
this on the data tab of the properties window), which binds that control to a
field in your table.

When you add a combo box to the form, it contains a "Row Source" (also
located on the data tab of the properties window), which should be your
"Majors" table (or my qry_Bach_Majors query). And the "Control Source" for
this should be the Major field from your Students table.

HTH
Dale
 
G

Guest

Thanks for any help, and I suspect this won't be my first question here?

If the questions are all as well written as this, and have truly
great snappy funny relevant titles like this -- I suspect you
will wind up writing for a living instead of database design :~)

I agree that there seems to be too many tables.
Also, you should consider defining the relationship between
Student and Majors, using the relationship window, but it
may not add any immediate benefit to this small example.

A defined relationship can be used to define the indexes
on the matching key fields, required in Access for updateable
joins, but you may have indexes already, and Access automatically
indexes some fields guessing from the name.

A defined relationship can also be used to control declarative
referential integrity -- deleting students when you delete
majors, updating students when you modify programs --
and the most used feature is that it prevents you from having
students attached to non-existent majors. Since you are
using a form to create the students, you probably haven't
had that problem yet.

(david)
 
Top