Youth Group db help

[Max]

Here is what I have so far (on paper, not in practice):

STUDENTS (table)
StudID (PK autonumber)
StudFName, StudLName, StudAdd ....& other details, also, I included other
fields that I would like to format in certain ways, they are:
HSGradYr
Grade (I want to automatically increase the grade each June, and remove the
HS Graduates to another d/b)
StudYthGrp (students might belong to zero, or more than one)
StudChrch (might belong to none or one)
StudCC (might belong to none or one Community Center)
StudReg (Geographic region, there are 11, or it might be not listed or it
might be out of this area completely)
ActStud (don't know how to set this up; would be the activities the student
attended)

PARENTS/GUARDIANS (table)
PGID(PK autonumber)
PGStudID (FK = StudID # from STUDENTS tbl.)
PG1 (One parent/guardian's details)
PG2 (Second par/guar details)

ACTIVITIES (table)
ACTID (PK autonumber)
ACTName
ACTDATE
ACTDesc (Text field: targeted age/grade group, gender, etc.)
StudAct (don't know how to set this up; would be students who attended the
activity)

ORGANIZATIONS (table)
OrgID (PK autonumber)
SvcREG (same regional names as in the STUDENTS table)
ORGName, OrgAdd, etc.

CONTACTS
ConID (PK autonumber)
ConOrgID (FK = OrgID from Organizations table)
ConName, ConAdd, etc. (details)

What I don't know how to do is set this up so that we can see what
activity/ies each student attended, and conversely, see attendance lists for
each activity.

Students can attend zero, 1, or many activities. Students can belong to
zero, 1 or many organizations. Students can have zero, 1 or 2
parents/guardians. Also, students as well as parents/guardians can have
several phone/cell/fax numbers.

Par/Guar can have 1 or more students, so I am not sure how to set up the FK
field here. Some phone numbers may have extensions. Some Par/Guar may have
a different address than their student(s).

An Activity will have many Student attendees. An Organization can be one of
several types (a church, youth group, community center, school; in addition,
some churches or community centers might have a school). An Organization
could have one or many contacts.

Does this design look like it will work efficiently?

I was also told I should have a "front end" and a "back end", and a
switchboard. I know the basic idea, but how do I set those up?

In Peace, Max

 

[John Vinson]

Here is what I have so far (on paper, not in practice):

Sorry I haven't responded to your earlier post as promised, Max! it
got buried under other work.

STUDENTS (table)
StudID (PK autonumber)
StudFName, StudLName, StudAdd ....& other details, also, I included other
fields that I would like to format in certain ways, they are:
HSGradYr
Grade (I want to automatically increase the grade each June, and remove the
HS Graduates to another d/b)

Well... don't. A person is a person, whether they're in school or not.
You can run an Update query to update the Grade field to Grade+1 once
a year if you insist.

StudYthGrp (students might belong to zero, or more than one)

In that case you need TWO MORE TABLES: YouthGroups and
GroupMembership. YouthGroups would identify the group by name (and
location, and other relevant information about the group as a whole);
GroupMembership would have fields StudID and GroupID (with perhaps
other information about this student's membership in this particular
group, i.e. Role (is she the President?) or date joined).

StudChrch (might belong to none or one)

You'll want a table of Churches; you can use a Combo Box on a form
based on the Churches table to update this field.

StudCC (might belong to none or one Community Center)
Ditto

StudReg (Geographic region, there are 11, or it might be not listed or it
might be out of this area completely)
Ditto

ActStud (don't know how to set this up; would be the activities the student
attended)

This should NOT exist in the Students table. As with YouthGroups, you
need another table with ActivityID and StudID.

PARENTS/GUARDIANS (table)
PGID(PK autonumber)
PGStudID (FK = StudID # from STUDENTS tbl.)

That's the wrong direction for the relationship: it would limit each
parent to one and only one student. Instead put the PGID in the
Students table; the foreign key always goes into the "many" side
table. A Family may have several children, but (for these purposes
anyway I'd guess) a student is a member of only one family.

You might also want to consider putting the address information in
this table instead of the Student table, if your students all live at
home.

PG1 (One parent/guardian's details)
PG2 (Second par/guar details)

ACTIVITIES (table)
ACTID (PK autonumber)
ACTName
ACTDATE
ACTDesc (Text field: targeted age/grade group, gender, etc.)

You may want to split this up for easier searching - i.e. a Gender
field with values "M", "F", "A" for male, female, or anybody; AgeLow
and AgeHigh integer fields, so you can put AgeLow = 12, AgeHigh = 120
for "anyone over 12".

StudAct (don't know how to set this up; would be students who attended the
activity)

Again StudAct needs *ITS OWN TABLE*. It should not exist in either the
Student table nor in the Activities table. Instead you would have a
StudAct table:

StudAct
StudID <link to Students>
ActID <link to Activities> <both these are Long Integer foreign
keys, and jointly constitute the two-field Primary Key of StudAct>
<any other fields about *this* student's participation in *this*
activity>

ORGANIZATIONS (table)
OrgID (PK autonumber)
SvcREG (same regional names as in the STUDENTS table)
ORGName, OrgAdd, etc.
ok

CONTACTS
ConID (PK autonumber)
ConOrgID (FK = OrgID from Organizations table)
ConName, ConAdd, etc. (details)

What I don't know how to do is set this up so that we can see what
activity/ies each student attended, and conversely, see attendance lists for
each activity.

You'll want several Forms with Subforms. For example, you could have a
Form based on Students, with subforms for the StudAct table and the
Membership table.

Students can attend zero, 1, or many activities. Students can belong to
zero, 1 or many organizations. Students can have zero, 1 or 2
parents/guardians. Also, students as well as parents/guardians can have
several phone/cell/fax numbers.

Then add a Phones table related one-to-many to Students (and perhaps
consider having a People table for *all* people, whether they are
students, organization contacts, or parent/guardians); then your
Students table could contain, rather than a StudentID, just the
PersonID fields of those people who happen to be students.

Par/Guar can have 1 or more students, so I am not sure how to set up the FK
field here. Some phone numbers may have extensions. Some Par/Guar may have
a different address than their student(s).

An Activity will have many Student attendees. An Organization can be one of
several types (a church, youth group, community center, school; in addition,
some churches or community centers might have a school). An Organization
could have one or many contacts.

Does this design look like it will work efficiently?

Should be. It'll be a good bit of work still but you're off to a great
start!

I was also told I should have a "front end" and a "back end", and a
switchboard. I know the basic idea, but how do I set those up?

The back and frontend are important - I'd say vital - if this database
will be used simultaneously by two or more users. If it's going to sit
on one machine with only one user, then it's not essential to split
it. Tools... Database Utilities... Database Splitter Wizard will take
a unitary database and split it into a "backend" containing just the
tables, and a "frontend" containing everything else, if you want to go
that way.

A Switchboard is convenient; I am not that fond of Microsoft's
implementation of the switchboard, but there's a wizard to create it
as well. I did a Google search for "Access Switchboard replacement"
and was mildly surprised to find an old post of my own archived:

http://www.archive-one.com/new-2440851-2916.html

Good luck and God bless!

John W. Vinson[MVP]