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
|
|||
|
|||
Is Access even the right idea?
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. |
#2
|
|||
|
|||
Is Access even the right idea?
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/conrad...resources.html beginning with the Database Design 101 and Starting Out links. hth "BMB" wrote in message ... 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. |
#3
|
|||
|
|||
Is Access even the right idea?
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. "BMB" wrote: 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. |
#4
|
|||
|
|||
Is Access even the right idea?
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" wrote in message ... 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. |
#5
|
|||
|
|||
Is Access even the right idea?
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. |
#6
|
|||
|
|||
Is Access even the right idea?
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 "BMB" wrote in message ... 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. |
#7
|
|||
|
|||
Is Access even the right idea?
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 "BMB" wrote in message ... 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. |
#8
|
|||
|
|||
Is Access even the right idea?
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 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! "BMB" wrote in message ... 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. |
#9
|
|||
|
|||
Is Access even the right idea?
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 "PC Datasheet" wrote in message nk.net... 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 |
#10
|
|||
|
|||
Is Access even the right idea?
"PC Datasheet" schreef in bericht nk.net... 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. to the OP: Before even considering contacting Steve: Look at: http://home.tiscali.nl/arracom/stopsteve.html -- There even has been a 'Scam-alert' about him which has been explained recently in the thread 'To all': http://groups.google.com/group/comp....954261f9?hl=en -- Also recently it became clear that he has been spamming innocent people asking questions: http://groups.google.com/group/comp....3e5f58ad?hl=en So why would ANYBODY ever trust a person like him and hire him? ************************************************** ****** 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. These 1000 (if at all a real figure..) is only the result of -- 4 years abusing the newsgroups. -- 4 years blatantly advertising and job hunting. To Steve: You only care about making money, and you act as if the groups are your private hunting ground. So why would ANYBODY ever trust a person like you and hire you? ************************************************** ****** Need a month calendar or 7 day calendar? Need appointment scheduling? Need room reservations scheduling? Need employee work scheduling? Contact me! To Steve: Need a psychiatrist ...Contact me! Arno R |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
The "Right" web hosting for data access pages?? | Ron Ehrlich | General Discussion | 9 | May 6th, 2005 05:49 AM |
Encrypt AccesS File? | milest | General Discussion | 2 | February 9th, 2005 07:58 PM |
Hiding Access | Mark A. Sam | Using Forms | 4 | December 1st, 2004 09:09 PM |
Access 2000 -vs- Access 2003? | Mark | General Discussion | 5 | November 30th, 2004 06:36 AM |
MICROSOFT INVESTING HEAVILY IN ACCESS | Mike Painter | General Discussion | 39 | October 15th, 2004 03:56 PM |