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
|
|||
|
|||
New Database
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. |
#2
|
|||
|
|||
New Database
Not sure you need another table for active students. This implies you would
delete the student from that table if no longer active. Deletion of records is something to avoid. This data just seems like more info about the student (like phone number). Add a field for the status entity that you can use to filter for active students. Kris D wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200908/1 |
#3
|
|||
|
|||
New Database
Also suggest you not use # symbol in field name, could cause problems. Use
CaseNum or CaseNo. June7 wrote: Not sure you need another table for active students. This implies you would delete the student from that table if no longer active. Deletion of records is something to avoid. This data just seems like more info about the student (like phone number). Add a field for the status entity that you can use to filter for active students. I recently posted a question under general questions and was given great advise, but I am still a little confused. [quoted text clipped - 50 lines] Thank you to anyone that can help. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200908/1 |
#4
|
|||
|
|||
New Database
Also suggest you not use # symbol in field name, could cause problems. Use
CaseNum or CaseNo. June7 wrote: Not sure you need another table for active students. This implies you would delete the student from that table if no longer active. Deletion of records is something to avoid. This data just seems like more info about the student (like phone number). Add a field for the status entity that you can use to filter for active students. I recently posted a question under general questions and was given great advise, but I am still a little confused. [quoted text clipped - 50 lines] Thank you to anyone that can help. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200908/1 |
#5
|
|||
|
|||
New Database
Another caution. Spaces in field names require that you enclose field names
in brackets ([ ]). I don't allow spaces or punctuation or reserved words in my field names so I don't have to remember to use the brackets. June7 wrote: Also suggest you not use # symbol in field name, could cause problems. Use CaseNum or CaseNo. Not sure you need another table for active students. This implies you would delete the student from that table if no longer active. Deletion of records [quoted text clipped - 7 lines] Thank you to anyone that can help. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200908/1 |
#6
|
|||
|
|||
New Database
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? "June7 via AccessMonster.com" wrote: Not sure you need another table for active students. This implies you would delete the student from that table if no longer active. Deletion of records is something to avoid. This data just seems like more info about the student (like phone number). Add a field for the status entity that you can use to filter for active students. Kris D wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200908/1 |
#7
|
|||
|
|||
New Database
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. Kris D wrote: 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? Not sure you need another table for active students. This implies you would delete the student from that table if no longer active. Deletion of records [quoted text clipped - 56 lines] Thank you to anyone that can help. -- Message posted via http://www.accessmonster.com |
#8
|
|||
|
|||
New Database
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. June7 wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200908/1 |
#9
|
|||
|
|||
New Database
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 "June7 via AccessMonster.com" wrote: 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. June7 wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200908/1 |
#10
|
|||
|
|||
New Database
On Sun, 23 Aug 2009 17:59:01 -0700, Kris D
wrote: 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|