New Database

[Kris D]

I recently posted a question under general questions and was given great
advise, but I am still a little confused.

I need to create a database for a college program that I am responsible for.
I currently work out of many spreadsheets. A database will make my job
easier.

Here is what I currently do out of excel:

I have a workbook for an academic year (2009/2010) with three worksheets
(Summer 2009, Fall 2009, Spring 2010) within the workbook. Each worksheet
contains everything about an active student for that semester. I have
columns past "Z" which includes some of the following:

Date
Student ID
Case #
Last Name
First Name
Case Worker
Address
City
State
Zip
Phone Number
On TANF (yes/no)
FAFSA on file (yes/no)
Referral for Workstudy (yes/no)
Referral for Childcare (yes/no)
Referral for Bookloan (yes/no)

Now a student must meet with an academic counselor once a semester to be
active in the program each semester. For example, a student comes in for a
supportive service (bookloan) during Fall 2009; I would need to verify there
status in the program for the Fall 2009 semester prior to giving that
service.

I currently set up tables for the following:

Students (ID #, case #, name, address, phone number, and Date of birth)
Case Workers (ID #, name, phone number, and fax number)
Semesters (Semester/year, Start date, End date)

Not sure if I am on the right track. But I think I need another table that
is for active students? With fields like; Student, Semester/year, TANF
(yes/no), FAFSA (yes/no), Workstudy Referral (yes/no), Childcare Referral
(yes/no), Bookloan Referral (yes/no)....

Now that I have written all this information I don't think my database is
going to be a simple database? Sounds like this is going to take me months
to create. Any idea on how much it would cost for someone to create
something like this for me?

Thank you to anyone that can help.

 

[Kris D]

How would I keep track of how many semesters a student was active? For
example, I need to create an end of year report and need to know how many
semesters Jane Doe was active. I would need to know if Jane was active Fall,
Spring, and Summer, or just Fall Semester, or just Fall and Spring, etc.

I also want to track the total semesters Jane has been in the
program....Could be 8 semesters starting back from Fall 2009. Or it could be
Jane started Fall 2008 and stopped in Spring 2009 then returns in Fall 2009.

Would I then need a table for active students to track this data?

 

[Kris D]

Ok, I created two tables:

Students Table:
ID - Auto Number (primary key)
Student ID (school ID)
Social
Case ID
Last Name
First Name
Date of Birth
Home Phone
Mobile Phone
Address
City
State
Zip

Student Status Table:
ID - Auto Number (primary key)
Student (look up field from students table)
Counseling Contact (Yes/No)
Semester (combo box listing fall 2009, spring 2010, summer 2010, fall 2010,
and so on. not sure if I should create a table listing semesters with start
and end dates and then have a look up field for SEMESTERS in this table?)
TANF (Yes/No)
Childcare Referral (Yes/No)
Workstudy Referral (Yes/No)
Bookloan Referral (Yes/No)

Am I on the right track now? Also, do I need to relate any of these tables
together?

Thank you

Just looked at your original post again. Any data that will change each
semester and you want to keep that historical info then yes put in another
table for semester enrollment. But if you don't care about it from historical
point, just what is current (like PhoneNumber) then put in the student info
table. Still would have a status field (Active - yes/no) and not delete
records.

That does add a level of complexity to the data that could call for another
table if you want to maintain attendance records beyond one school year.
Would still have a status field in the students table. Then another table
that will store records for the attendance periods to maintain historical
data. Fields could be StudentID, Year, Semester. Again, the table will retain
ALL records regardless of current enrollment status.

How would I keep track of how many semesters a student was active? For
example, I need to create an end of year report and need to know how many

[quoted text clipped - 12 lines]

Thank you to anyone that can help.

 

[John W. Vinson]

Ok, I created two tables:

Students Table:
ID - Auto Number (primary key)
Student ID (school ID)
Social
Case ID
Last Name
First Name
Date of Birth
Home Phone
Mobile Phone
Address
City
State
Zip

Student Status Table:
ID - Auto Number (primary key)
Student (look up field from students table)
Counseling Contact (Yes/No)
Semester (combo box listing fall 2009, spring 2010, summer 2010, fall 2010,
and so on. not sure if I should create a table listing semesters with start
and end dates and then have a look up field for SEMESTERS in this table?)
TANF (Yes/No)
Childcare Referral (Yes/No)
Workstudy Referral (Yes/No)
Bookloan Referral (Yes/No)

Am I on the right track now? Also, do I need to relate any of these tables
together?

Getting there.

Using a Lookup Field (Student) *DOES* create a relationship... and then
conceals it from your view, and conceals the index, and conceals the actual
content of the field. See

http://www.mvps.org/access/lookupfields.htm

for a critique of what many of us consider a Very Bad Idea on the part of
Microsoft.

A better (more transparent, more flexible) alternative would be to use the
StudentID (without the blank in the name, fieldnames shouldn't have blanks or
special characters) as the primary key of the Student table; remove the
autonumber, and use StudentID in the StudentStatus table (again, best not to
use blanks).

You may also want a separate Referrals table with fields StudentStatusID and
ReferralType, rather than a different field for each kind of referral; you'ld
have a small table of ReferralTypes.