Is Access even the right idea?

[BMB]

I have recently had a large amount of data dumped on me and was asked to
organize it. If the data were finite, it wouldn't be a problem; but it is
subject to weekly updates with new records being added. This might take some
explaining. . .

I work in a university program office and am trying to figure out the best
way to keep up with which students are in which degree concentrations, each
of which has its own set of requisite courses. For example, there are 5
different degree concentrations with the program; one has only 6 required
courses, one has 16. I have projected dates that students anticipate taking
the courses that are required for them (e.g., "Spring, 2007").

Keeping up with the basic data (name, address, phone, program, etc.) is
fairly simple. I have it set up in Access and Excel. But what I want is
some kind of smart form that will know which classes are required of which
students based on their concentrations and can create detailed reports
showing how many students are projected to take each class during upcoming
semesters.

Does this make sense to anyone? I'm looking at it and still not sure what
to do. Any help with deciding which application (and how) to use would be
most appreciated.

 

[tina]

well, i'd say Access is the place to house, organize, update, and analyze
the data - but it doesn't sound like a trivial undertaking. how comfortable
are you with the Access software? and how familiar are you with the concepts
of data modeling/normalization/table relationships? if your answer to the
second question is "not very" or "not at all", then i recommend you start
there. this is definitely not a project that you want to throw together
willy-nilly, especially since you want to use the data to project future
business needs.

to learn relational data modeling methodology and the principles of
normalization, an excellent text is Database Design for Mere Mortals by
Michael Hernandez. there are also a number of links to helpful material at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
beginning with the Database Design 101 and Starting Out links.

hth

 

[Klatuu]

Excel would definitely not be the correct tool for this. Access is well
suited to this sort of data storing and mining; however, it will require your
database be set up correctly.
Here are some table you should have:
Students - All the student info including what concentration they are in.

Concentration - Defines all the concentration programs offered

Classes - Defines all classes that may be used in any concentration

ConcentrationClasses - This is a join table between Concentrations and
Classes. Since a many to many relation exists between Concentrations and
Classes, this table resolves it. It will contain a row showing each class
included in each concentration.

Class Sessions - Defines the date, time, location, each class is offered.

Enrollments - Defines which students are either enrolled in or projected to
be enrolled in a class. You can identify whether it is a projection or an
enrollment, or any other status you need with a status field. It sould
contain the Student ID, the Class Id, and the Session Id

Once you have your database structured correctly, the rest is easy.

 

[Ed Warren]

I have built a similar program for advising graduate students. I used
access. The key is in building your tables and relationships.
I have the following tables, you will most likely want something different
in specifics, but similar in structure. I probably should have a table of
'people' and pull both the students and faculty advisors from that table,
but since I only had to deal with less than five faculty members, I chose to
let that slip by.

Using these tables and some tabbed pages, with subforms, I got a reasonable
user interface simple enough that even a college professor with advanced
degrees can understand ;>

Ed Warren

---------------------------------------------------------------------------sample
table structure--------------
Admission Data (stuff about the student's admission)
AdmissionID (key)
StudentID (fk)
DateAdmitted
DateWithdrew
DateGraduated
ProgramID (fk)
Comment
Course Availability (stuff about when courses are
offered)
CourseAvailabilityID (key)
CourseID (fk)
Semester date/time --> had to fight with the professors on this,
they think semesters start 'in September' and want to use two
fields (Spring, winter, fall) (year). Using just one field as a date you
can show the semesters in the proper, logical, temporal order.

Course Lookup (stuff about the course)
CourseID (key)
Department
CourseNumber
CourseCredits
CourseName

Course To Program (this maps a many:many relationship)
(stuff about courses and study programs)

ProgramID
CourseID (combined programid and courseid as key)

Email lookup (this is a lookup for types of email, e.g. school, home,
office) (a simple list of types of email)
EmailTypeID key
emailType

Phone Numbers (stores the student's phone numbers 1:M) (allow 0:m
telephone number for each student.
PhoneId (key)
StudentID (fk)
PhonetypeID (fk)
PhoneNumber

Phone Type Lookup (lookup table for types of telephone numbers (home,
office, cell, fax, etc) (a simple list of type of telephone)
PhonetypeID (key)
Phonetype

Plan of Study (ties a 'plan' of study (student/courses) to course
availability, completion, and date)
PlanID (key)
StudentID (fk)
CourseAvailabilityID (fk)
DateCompleted
Grade

Program Lookup (programs offered)
ProgramID (key)
ProgramType

Semesters (Semester List)
SemesterStartDate Date/time (key) (Note: I use a rather
arbitary date like Sept 01, 2005)
Semester (text) e.g. Spring, Fall, Summer

Student Demographics (student specific stuff)
StudentID (key)
FirstName
MiddleInitial
LastName
Address1
Address2
City
State
ZipCode
Comments
CurrentStudent (yes/no)
FacultyAdvisor

Student Email (allows 0:n student emails)
EmailID (key)
StudentID (fk)
EmailTypeID (fk)
EmailAddress

 

[BMB]

Thank you for the suggestions. They have helped. I think that I have all
the tables constructed (maybe), but the assimilation part is proving more
complicated than I had thought. As I establish relationships between the
tables, I seem to get less functionality and information retention than
before. The following is my table structure. Further assistance would be
greatly appreciated.

Table structure-----

ConcentrationLookup
ConcentrationID (key) - autonumber
ConcentrationTitle - text
CourseLookup
CourseID (key) - autonumber
CourseNumber - text
CourseToConcentration
ConcentrationID - autonumber
CourseID - text
EmailLookup
EmailTypeID (key) - autonumber
EmailType - text
HomeSchoolLookup
HomeSchoolID (key) - autonumber
HomeSchool - text
PhoneNumbers
PhoneID (key) - autonumber
StudentID - text
PhoneTypeID - text
PhoneNumber - text
PhoneTypeLookup
PhoneTypeID (key) - autonumber
PhoneType - text
ProgramData
ProgramID (key) - text
StudentID - text
DateAdmitted - date/time
DateUpdated - date/time
ConcentrationID - text
StatusID - text
Comment - memo
SemesterLookup
SemesterID (key) - autonumber
SemesterStartDate - date/time
Semester - text
StatusLookup
StatusID (key) - text
StatusType - text
StudentDemographics
StudentID (key) - text
FirstName - text
MiddleInitial - text
LastName - text
ProgramID - text
ConcentrationTitle - text
HomeSchool - text
Address1 - text
Address2 - text
City - text
State - text
ZipCode - text
PhoneType - number
PhoneNumber - text
EmailType - number
EmailAddress - memo
Advisor - text
DateUpdated - date/time
StudentEmail
EmailID (key) - text
StudentID - text
EmailTypeID - text
EmailAddress - memo
CourseStatusLookup
CourseStatusID - autonumber
CourseStatus - text

The following information may be necessary: ProgramID is a four-digit number
that we assign to students as they apply for our program as a method of
internal tracking; all courses are available each semester; there are 7
course requirements that are common to all 5 concentrations.

I would like to create a tabbed form that contains one tab for basic student
information and subsequent tabs that contain scheduling data for the
requisite courses based upon the student's chosen concentration (i.e., one
tab for each concentration, but only the appropriate tab accessible based on
the "Concentration" selection on tab 1).

If you're still even reading this, thanks.

 

[tina]

i noticed that in some cases primary/foreign key pairs did not have matching
data types. and in a few cases, you did not use a Lookup table's primary key
as the foreign key in another table - instead, you used another field from
the Lookup table. take a look at the following to compare with your listed
table/fields setup. (note that i didn't really analyze the table design,
just addresses the keys issues.)

ConcentrationLookup
ConcentrationID (primary key - Autonumber)
ConcentrationTitle - text

CourseLookup
CourseID (primary key - Autonumber)
CourseNumber - text

ConcentrationCourses
ConcentrationID (foreign key from ConcentrationLookup - Long Integer)
CourseID (foreign key from CourseLookup - Long Integer)

EmailLookup
EmailTypeID (primary key - Autonumber)
EmailType - text

HomeSchoolLookup
HomeSchoolID (primary key - Autonumber)
HomeSchool - text

PhoneNumbers
PhoneID (primary key - Autonumber)
StudentID (foreign key from Students - Text)
PhoneTypeID (foreign key from PhoneTypeLookup - Long Integer)
PhoneNumber - text

PhoneTypeLookup
PhoneTypeID (primary key - Autonumber)
PhoneType - text

ProgramData
ProgramID (primary key - Text)
StudentID (foreign key from Students - Text)
DateAdmitted - date/time
DateUpdated - date/time
ConcentrationID (foreign key from ConcentrationLookup - Long Integer)
StatusID (foreign key from StatusLookup - Text)
Comment - memo

SemesterLookup
SemesterID (primary key - Autonumber)
SemesterStartDate - date/time
Semester - text

StatusLookup
StatusID (primary key - Text)
StatusType - text

Students
StudentID (primary key - Text)
FirstName - text
MiddleInitial - text
LastName - text
ProgramID (foreign key - Text)
ConcentrationID (foreign key - Long Integer)
HomeSchoolID (foreign key - Long Integer)
Address1 - text
Address2 - text
City - text
State - text
ZipCode - text
Advisor - text
DateUpdated - date/time

StudentPhones
PhoneID (primary key - Autonumber)
StudentID (foreign key - Text)
PhoneTypeID (foreign key - Long Integer)
PhoneNumber - text

StudentEmail
EmailID (primary key - Autonumber)
StudentID (foreign key - Text)
EmailTypeID (foreign key - Long Integer)
EmailAddress - memo

CourseStatusLookup
CourseStatusID (primary key - Autonumber)
CourseStatus - text

hth

 

[Ed Warren]

Regarding your user interface (UI)

After you get all the tables right, you will need a form to update/maintain
each of the tables.

You are going to want to have a form to lookup a student (based on the
studentdemographics table) with a lookup box to "find student"
the Main part of this form will have the student demographics stuff in it.
Then you will want to have a set of tabbedpages to display subforms based
on the related tables for that student.
examples:
Telephone numbers
Email Addresses
Plan of Study
etc.

Then you are going to want to look at the entries from the course/semester
perspective

For a selected course who is planning to enroll in that course in which
semester.
For a selected course who is actually enrolled in that course for the
semester.

And from the Concentration perspective:
who is currently enrolled in each concentration
when will they complete the course work?

etc. etc.


Ed Warren

----------------------------table comments-------------------

What's missing is a way to hold the relationships of a student to course
enrollment (planned: course/semester) and/or course enrollment (actual
course/semester) and course completion date/grade.

Also if each student can have one and only one 'program' then the program
data 'could' go into the student table. However, my experience is that each
student will start several programs of study, before completing any one of
them. In which case your application needs to have the ability to record
the courses completed and identify those outstanding in the new study
program.



Here are some of the issues with your tables:

Tables Fields Comments
ConcentrationLookup
ConcentrationID (key) - autonumber
ConcentrationTitle - text
CourseLookup
CourseID (key) - autonumber
CourseNumber - text
CourseToConcentration
ConcentrationID - autonumber
CourseID - text CourseID (match from courseLookup)
EmailLookup
EmailTypeID (key) - autonumber
EmailType - text
HomeSchoolLookup
HomeSchoolID (key)- autonumber
HomeSchool - text
PhoneNumbers
PhoneID (key) - autonumber
StudentID - text StudentID (key) - text
PhoneTypeID - text
PhoneNumber - text
PhoneTypeLookup
PhoneTypeID (key) - autonumber
PhoneType - text
ProgramData
ProgramID (key) - text
StudentID - text
DateAdmitted - date/time
DateUpdated - date/time
ConcentrationID - text ConcentrationID -- should match the
ConcentrationID from Concentration Lookup --Number
StatusID - text FK from StatusLookup.
Comment - memo
Probably want to add a DateCompleted and/or DateWithdrawn
and or Yes/no field to document that the program of study was
completed or student withdrew (this may already be there in the statusID
field)






SemesterLookup
SemesterID (key) - autonumber
SemesterStartDate - date/time
Semester - text
StatusLookup
StatusID (key) - text May want the key to be autoincrement number
StatusType - text
StudentDemographics
StudentID (key) - text Are your student ID's really text or numbers??
FirstName - text
MiddleInitial - text
LastName - text
ProgramID - text not a student characteristic data in
ProgramData Table
ConcentrationTitle - text not a student characteristic in ProgramData
Table
HomeSchool - text
Address1 - text
Address2 - text
City - text
State - text
ZipCode - text
PhoneType - number not a student characteristic
information in Phone Numbers table
PhoneNumber - text not a student characteristic information in Phone
Numbers table
EmailType - number not a student characteristic
information in StudentEmail table.
EmailAddress - memo not a student characteristic information in
StudentEmail table.
Advisor - text not a student characterisitc should be in the
ProgramData table
DateUpdated - date/time
StudentEmail
EmailID (key) - text Probably should be number and autoinc
StudentID - text
EmailTypeID - text Should match EmailTypeID in Email (number)
EmailAddress - memo
CourseStatusLookup
CourseStatusID - autonumber
CourseStatus - text
Add CourseStatusDate -- date/time

 

[PC Datasheet]

If the complication is becoming too frustrating, please consider that I can
build the database for you for a very reasonable fee. If you want help,
contact me at my email address below.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!

 

[John Marshall, MVP]

Soliciting again. You really do not care about solving his problem, just
making money.

These newsgroups are provided by Microsoft for FREE peer to peer support,
not for your own private hunting ground.

John... Visio MVP

 

[Ed Warren]

I'm sure that with your vast expertise you could actually provide BMB some
help in trying to sort all this out then he/she could learn to solve the
problem.

Then the next time BMB is asked to solve a data problem he/she will have
devloped the knowledge and experience needed, leading eventually to
promotion and tenure and a life of luxury and fullfillment. A life, your
offer, will deny him/her.

I'm not sure you are aware of the terrible financial and psychological
damage your offer to sell could cause BMB.

I'm passionate about data. Microsoft Access is a great tool for the newbie
to 'access' and display data. Therefore, I enjoy trying to share what
little I have learned with others struggling to get a start with relational
databases. I have also benefited greatly from some of the 'stupid
questions' I have posted with this group and gotten valued advice.

In short I think it better to "teach one to fish" rather than to "give one a
fish".

It is obvious that you want to sell fish, not teach fishing!

I recommend you set up your 'fish monger shop' in an appropriate dark
alleyway rather than in the middle of the school grounds.



Ed Warren

 

[John Marshall, MVP]

Thanks Warren.

It is nice to see that others understand the spirit of free support.

John... Visio MVP

 

[PC Datasheet]

Ed,

If you go back over time you will see that I provided advice and support at
a ratio of ten to one or more to responses where I asked the poster to
contact me if he needed help. I judiciously chose where to post my offer to
help to posters where it was highly unlikely that they would get the
solution they needed from the newsgroup. Over the years over 1050 Access
users from the newsgroups have contacted me asking for help and willing to
pay a fee for it. Don't get me wrong, I always encourage them to seek help
from the newsgroups telling them it is free. Arno R and his dimwit sidekick
John Marshall want to steal away this option for help from anyone who comes
to the newsgroup looking for help. Go back over time and see how many
responses of help were posted by Arno R and his dimwit friend. The dimwit
friend isn't even an Access MVP!

Steve
PC Datasheet

 

[John Marshall, MVP]

Oh I'm so hurt. Name calling from a failed plumber, just won't do it. So
I'll just add this to the long list of items you will never apoligize for.

It has nothing to do with 10 to 1, 100 to 1, or a million to 1. You still
post messages soliciting work. The issue is the 1 not the 10, 100 or
million.

These newsgroups were set up my Microsoft to allow free peer to peer help,
solicitions was not one of the intended "options for help".

So by posting a few answers, some of which you have plagerized, absolves you
from following the spirit of the newsgroups?

The number of responses I or Arno make to these newsgroups is immaterial to
the issue. It's like saying that a pedestrian can not comment on a traffic
accident because he was not driving.

So when will your following of 1050, jump to your defence?

John... Visio MVP

 

[Ed Warren]

Your job hunting posts are annoying in and of themselves, no matter what
Arno and/or John Marshall think and/or say!

As I said I'm a data freak, math major, and statistician, I took up your
challenge.
For the most recent posts under your name:
Out of 41 posts:

You were asking for help 12
You were providing Help 4
You were advertising and or defending advertising 25


--> 9.76% help

Maybe in Washington DC this could be spun as a 10:1 Providing help record.

-----by the way I will not respond to any further posts on this subject on
this thread-------I'm going back to and find someone to actually help !!
Ed Warren.

 

[Ian]

No Access is not right for you. From what I read you are going to use a lot
of data. Access can only do 10,000 transactions at a time. So if you have
multiple users updating and deleting and whatever, Access will crash if
10,000 users access.
Oracle can handle the whole world's population if they decide to access it
at the same time. Value -- Access - $100 Oracle - $100,000 for your school
Oracle is totally web, so no need for software on your computer. And that
means
ease. I'm against ACCESS because I've seen it in action with large
organization like yours, and "Pane Freezing" is common. Microsoft is aware of
this, and that's why Mr. Ellison from Oracle is #2 next to Bill Gates

 

[Ed Warren]

What universe are you living in? The poor soul is currently managing the
data with excel, and does weekly updates. That is a far far cry from
needing 10,000 concurrent transactions.
Matter of fact I would be surprised it their entire database was over a few
100 records in length.

They need a bucket, not a freight train ;>

Ed Warren

 

[BMB]

Good Morning, Gentlemen,

OK, so I'm still plugging away at this database, and while I'm a big fan of
the 1st Amendment, could you please find somewhere else to argue? Not that I
don't thoroughly enjoy the disappointment of logging on after receiving an
e-mail that someone has posted a reply and finding out that it's from some
schmuck who has nothing better to do that plug his own business. . . Steve,
if I wanted to (or even could. . .) pay someone for this, wouldn't it make
more sense for me to explore that option up-front instead of wasting my time
trying to learn how to do this for myself? I haven't written to you eagerly
expressing my rapacious desire for your oh-so-unselfish services, so please
find someone else's post to fill with nonsensical garbage. That will be all.

 

[BMB]

First of all, I'd like to thank everyone who has helped with this project so
far. You have all been a great help (except for Steve, anyway). Here's my
next question - which, of course, proves how much of a novice I am with
Access: when creating a form, is there a way that the answer in one field
(chosen from a drop-down list) can lock other tabbed pages and require that
the user input data on others? For example, if I answer the question
concerning student concentration, can the form itself mandate that the
pertinent data required by that concentration be input while simultaneously
disallowing input of other data for different concentrations (perhaps by
locking out the tabbed pages on which that data would be input if relevant)?
After reading up on database issues on sites that have been recommended here,
I've tried playing with the Master and Child fields, but nothing that I've
tried works. If I need to provide more information, just let me know.

Again, thank you all for the help.

 

[Ed Warren]

Yes
You will need to:
a. Spend some amount of time defining exactly the behaviors you want to
implement in your software.
b. Write Visual Basic Code to implement your vision.
You will start with one of the events in your combobox, most likely
on_exit then in English
"When I exit the combobox, if it is set to concentration
number 1 then I want show tabs 1,5,7 and not allow the user to exit until
all of these are filled in."

Ed Warren.