Thank you very much for all your help.
You're welcome. That's why I volunteer here.
So far I have a table that lists students by id # (the key field,)
name(first and last,) address and phone, the school district from which they
come, and the school district rep. I've used combo boxes for those fields
that have just a few possible answers (MA, NH, ME for instance,) input masks
for the phone numbers and zips, and a yes/no field to sort those currently
enrolled from the alumni. I made a form corresponding to this table for my
assistants to input data. They've been at it full blazes.
I have figured out already I should have put the school district info into
its own table but will tackle the problem of how to abstract that data later.
Shouldn't be hard: you can use a MakeTable query selecting distinct
districts. We'll get to that.
I spent time today in Word just trying to list the data fields I need and
sort them into categories. I thought I knew what I wanted but realized as I
got deeper into this that the design, as you wrote, needs a solid foundation.
That process made me think about the key fields. It seems to me that every
unique person or thing needs its own key id. (Sorry if I'm being thick-headed
but things which should be obvious often only hit me when I've had to use
them.) So each kid has a key number, but each school district should have a
key number, each school district rep, each education plan, each assessement
and set of scores, each class (English 9, quarter 3, 2005) needs its own key
id #. Right? And every time I have a new key I have a new table?
Well... every Table must have a Primary Key. But it need not be a
number, and it need not be an Autonumber, and it need not be a single
field. If you have unique government-assign district numbers, or
district names, by all means use that name as the Primary Key: the
criteria for a PK are that it must be unique (no two districts with
the same name) and it should be stable and reasonably short. A 25 byte
text field that isn't likely to change would be just fine.
I'm having the most trouble trying to understand this part. > making the
StudentID the Primary Key for the Enrollment table is simply WRONG. A Primary
Key is, by definition, unique; if you want a student to be enrolled in
multiple courses, it *CANNOT* be the primary key. Instead, the StudentID
would be a *foreign* key - not the primary key - in the related tables. Each
table would have its own Primary Key, which might be an autonumber or might
be a combination of fields.>
A foreign key is key field data used in a subsequent tables, but not
identified in the new table as a key?
well, a foreign key isn't visibly distinguished in the table
datasheet. It doesn't have any key icon by it, as the PK does. It's
how it's USED that makes it a foreign key. In the Enrollment table
it's just a long integer (or whatever datatype matches the StudentID
primary key in the Students table) field, in the table like any other
field in the table.
In a one-to-many relationship, the
student is the one and the classes he takes is the many. So it all needs to
be set up so that the key fields which identfy the particular class are
records in a table which details all the classes the student takes while
enrolled at our school.
Not really. The Students to Classes relationship is NOT "one to many".
If it were you could put the StudentID in the Classes table - but
whoops, a field can only have one value, so that class could have only
one student! It's a *many to many* relationship; so you need an
Enrollment table modeling the relationship. It would have the
StudentID and the ClassID as a joint, two field Primary Key. This
table basically contains nothing but key fields (unless you need to
record other information about *this* student's enrollment in *this*
class, in which case you need other fields).
I don't know; this whole part blows my mind.
Especially when I try to think about setting up forms for teachers to input
assignments and grades and attendance.
It's all doable. It's a lot of work and will take some time, but it
can be done!
You know, I could buy a software package to do all this for us for $8k, with
a $2k annual tech assistance fee. But that's my book and supply budget for
the year so I'm on a mission here!
"It'll be a great day when schools have all the money they need and
the Pentagon needs to hold a bake sale to buy a new fighter jet!"
I'll confess, that if I were bidding a full school
enrollment/grades/reports etc. application, it would cost at least
that much. As I say... a lot of work. But I (and the other good folks
here on the newsgroup) should be able to help you do it for
free-or-cheap, and you'll get what YOU want rather than what some
committee thought would be a good idea. The downside is that you will
(to some extent) be reinventing the wheel, putting in a lot of time,
and forgoing the many person-years of time that have been invested in
the commercial apps.
I agree with this: If the ID number is an Autonumber then the ID should be
concealed. They are not helpful to my staff or me, I don't want to be
distracted by them.
I agree with this, too: The assistants likewise should never see a table
datasheet. Again, guaranteed to freak my assistants out. They already think
Access if voodoo.
Subforms. I tried to use my How to do Everything with Access 2002 book. I
ended up in a coma. I'm just going to have to take a copy of my table, get
into the wizards available, get down and dirty, and try it.
That's the best way to learn for me, certainly. There are other books
out there but there's nothing like doing it for real (with information
that is meaningful TO YOU, not hypothetical examples).
Thanks again, John. You've been a bit of sanity in this endeavor. I hope I
don't get defeated.
Just don't get snowed under! <g>
John W. Vinson[MVP]
