If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Youth Group db help
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 a 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 |
#2
|
|||
|
|||
On Thu, 13 Jan 2005 08:09:01 -0800, Max
wrote: 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 a 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). Then I retract my suggestion about the addresses above! g 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Query to calculate difference on group level | Christian | Running & Setting Up Queries | 2 | December 2nd, 2004 07:35 PM |
SECOND REQUEST - Reset Page Numbers by Group | Bev | Setting Up & Running Reports | 1 | July 28th, 2004 05:07 AM |
Mail Merge Problem | MT DOJ Help Desk | General Discussion | 9 | July 20th, 2004 04:55 AM |
Group Mailings without displaying everyone's details | Keith | Outlook Express | 2 | July 8th, 2004 03:14 AM |