Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Oct 2014
    Posts
    34
    Provided Answers: 1

    Unanswered: database to track enrollment across several years

    Hi, I have started a new job and have been handed down a database set up to track the kids and families enrolled in our religious ed program. Right now, each year, we make a copy of the whole database, delete some stuff, like tuition payments, and then start the new year. The whole process seems a bit awkward to me as well as causing some problems, like I can't see who was enrolled last year, but not this year without some pretty big workarounds.

    So, I'm trying to figure out a way to fix this. I want to be able to see that StudentA was enrolled in 2012 in the 2nd grade, 3rd in 2013, and 4th this year. Or that Student B was enrolled in the 5th grade last year, but hasn't returned this year. So are there any suggestions on how to set this up? Or any examples of a database that more or less does this? I may add stuff on to our current database or I may more or less start over, so anything that points me in the general direction of what I want to do would be helpful. Thanks!

  2. #2
    Join Date
    Jan 2003
    Location
    Irvine, CA
    Posts
    38
    You can do what you are looking for, but it will require upgrading to a SQL server environment.

    There are several free options out there and allow for a lot more data storage, but you will still potentially run in to issues unless you do partitioning of your tables, which can be keyed off of the year in question.
    Jeff Jones
    Software Architect
    http://www.linkedin.om/in/jeffjones1972
    jeff@lordjester.com
    425-345-8293

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by jdierking View Post
    You can do what you are looking for, but it will require upgrading to a SQL server environment.
    I'm just curious, but why do you think that a different database engine will be needed? I'm pretty confident that the database engine that is provided with the Microsoft Access product will handle this request. I agree that a SQL Server would be better for many technical reasons, but I can't see a single church requiring that much power.

    -PatP

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jan 2003
    Location
    Irvine, CA
    Posts
    38
    I was thinking of a smaller scale solution, like MySQL or even SQL Server Express.

    Access could handle the request, but as the dataset grows, as enrollment databases do, it would be inefficient. That was part of the reason for the suggestion.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im curious too...
    why would Access be inefficient
    why would switching to a SQL make any difference, especially as in plain vanilla Access underneath the hood lies SQL
    granted if this is going to become a massive shared multi on line user db then Access talking to a server based SQL engine would be a smarter call than Access talking to plain vanilla JET SQL. where tht transition point is is tricky to define it depends on so many things, but usually its around about 15..30 concurrent users
    Access can handle upto 2gb of data, multiple millions of rows in a table
    ..yes there are arbitrary limits but its unlikely on what you have said so far that you will hit them
    http://office.microsoft.com/en-gb/ac...005186808.aspx
    however if the design isn't normalised its possible to hit the 250 odd columns in a table / query






    there is no reason why you cannot modify your current db to handle concurrent years
    as identified you'd need a mechanism to know what time period a row belongs to, thats not just the registration but also other time dependant data such as wht fees were due (im guessing you have already recorded when a fee was paid).

    also anything else that say could change over time,. I don't know if your programme has say a graduating completion regime (ie a consumer must complete so many courses, or get so many marks. anything that may change form year to year will need careful revisiting. It may well be sensible to take a time out and consider if you need a complete redesign taking into account stuff that you have learned, things that have changed and so on.

    the only thing at the back of my mind with these sort of projects is that often they are started with the greatest of good will and best intentions. but that can change over time leaving the developer irritated and the organisation exposed. be concious of your exit strategy (ie when you think you want to cease involvement, and make certain its largely complete by then. make certain it is maintainable (whoever follows you will almost certainly look at your code and throw their hands up in horror. so document everything INSIDE the db).

    start small but have a clear idea of what you need over time.


    however if \ALL you really, really need is to know what has been dong what over the years (IE what they enrolled), then its fine to delete all the historic fees stuff ASSUMING that all such stuff is not carried over from year to year. (IE fees are not outstanding, there is no need to know that Mr & Mrs Smith are always late paying and so on.

    but in essence its as simple as adding a financial year code to each time dependant piece of information. I'd suggest that code links to another table, aslled say AcYears
    Code (PK)
    Description
    YearStart
    YearEnd
    4 2104-2105 15 Sept 2014 22 May 2015
    13 2013-2014
    ...
    Last edited by healdem; 10-25-14 at 05:25.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2014
    Posts
    34
    Provided Answers: 1
    Thanks everyone for your thoughts! I don't think moving out of Access is an option. I don't have the programming knowledge to do that without a lot of work, and I'm by far the most tech knowledgeable person around. Note about me, I'm pretty computer literate, but this is my first time having to make use of Access, so I'm learning as I go. But I'm pretty good at figuring things out and googling to figure out how to do what I want.

    Quote Originally Posted by healdem View Post
    It may well be sensible to take a time out and consider if you need a complete redesign taking into account stuff that you have learned, things that have changed and so on.
    Ya, I'm really leaning towards starting over at this point, it is pretty old and I've already had to do things like add an e-mail address column, and it may be easier to just start with a blank slate.

    the only thing at the back of my mind with these sort of projects is that often they are started with the greatest of good will and best intentions. but that can change over time leaving the developer irritated and the organisation exposed. be concious of your exit strategy (ie when you think you want to cease involvement, and make certain its largely complete by then. make certain it is maintainable (whoever follows you will almost certainly look at your code and throw their hands up in horror. so document everything INSIDE the db).

    start small but have a clear idea of what you need over time.
    Thanks for the warning. I'm officially the secretary, but like I said earlier, I'm the only rather tech savvy person there, so I'm coming up with some side projects. I figure I'll do what I can and if it works out great, but if not, I've made a few improvements to the existing database so we are already better off than we were. And being as I got pretty much no documentation on the current database and everything I've done has been a combination of poking at stuff and googling, again, if I leave them with any documentation, they will be better off.


    however if \ALL you really, really need is to know what has been dong what over the years (IE what they enrolled), then its fine to delete all the historic fees stuff ASSUMING that all such stuff is not carried over from year to year. (IE fees are not outstanding, there is no need to know that Mr & Mrs Smith are always late paying and so on.

    but in essence its as simple as adding a financial year code to each time dependant piece of information. I'd suggest that code links to another table, aslled say AcYears
    Code (PK)
    Description
    YearStart
    YearEnd
    4 2104-2105 15 Sept 2014 22 May 2015
    13 2013-2014
    ...
    Thanks, I'll play with that next week.

  7. #7
    Join Date
    Oct 2014
    Posts
    34
    Provided Answers: 1
    Ok, I've been playing around a bit. I can have a years table and then add a multiple value look up field to the students table. That would allow me to see who is registered each year and make queries based on that. The one thing I'm not sure about is being able to see which grade they are in in a specific year, for example in 2012-2013, StudentB was in 2nd grade. I'm not quite sure this is necessary, but I do think it would be useful. I could add another multi-value lookup field with grades and could count backwards, but it seems like there should be an easier way? Any ideas? Thanks again!

  8. #8
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    I believe the easiest way to do it would be to include a Grade field with your years table. I am taking this from a standpoint of trying not to assume what grade a student may start in if they are new, or assuming that they will pass one grade to the next each year, students skipping a grade or skipping a year, etc.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I don't know how your grading system works. If a pupil hoes up a grade each year then just store the current grade. You know what last year's grade was based on the academic year and the point in time.

    If you need to know what a pupil studied in a specific year then store an end of year transcript for each academic year
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Oct 2014
    Posts
    34
    Provided Answers: 1
    Quote Originally Posted by Brian Cermak View Post
    I believe the easiest way to do it would be to include a Grade field with your years table. I am taking this from a standpoint of trying not to assume what grade a student may start in if they are new, or assuming that they will pass one grade to the next each year, students skipping a grade or skipping a year, etc.
    Right now the years table is just dates (2013-2014, 2012-2013...) for a multi-value look-up field on the Students table, so I don't think adding grades there works.


    Quote Originally Posted by healdem View Post
    I don't know how your grading system works. If a pupil hoes up a grade each year then just store the current grade. You know what last year's grade was based on the academic year and the point in time.

    If you need to know what a pupil studied in a specific year then store an end of year transcript for each academic year
    That is the issue, for most of the students that works fine, they start and work their way up one grade at a time. There are exceptions however, we have a 4th grader in the 2nd grade class for First Communion, but next year she will jump back up to 5th grade where she belongs. So that is what I'm trying to figure out. If I'm fiddling around with this database, I want to make cover as many of those funny exceptions as possible without being overly complicated. So while I don't really need to know what class they were in last year right now, I'm not sure if that could be something that would be useful if there was a way to do it without too much headache.

    Thanks again everyone!

  11. #11
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    Interesting exception. So given that example, would the follow scenario be true:

    Year | Grade
    10-11 | 4
    11-12 | 2
    12-13 | 5

    Part of my question is whether a grade constitutes an entire year because if it does, you can still add Grade to the years table as I suggested, but you would have to query grade separately (through the MAX function) to determine the logical progression for a partiuclar student.

    If grade is simply a marker for where a student is in their curriculum, you will probably need to have it as a separate table (or tables). If this is the case, then I would have some questions about the detail of the coursework (is grade sufficient, or in your case for First Communion, whether you need to have a list of classes for students and then associate a grade to the class), as well as how often coursework is likely to change in the future in order to give some decent suggestion on how you might structure it.

  12. #12
    Join Date
    Oct 2014
    Posts
    34
    Provided Answers: 1
    Quote Originally Posted by Brian Cermak View Post
    Interesting exception. So given that example, would the follow scenario be true:

    Year | Grade
    10-11 | 4
    11-12 | 2
    12-13 | 5
    Almost, 10-11 would be blank, they didn't start coming until 11-12.

    Ok, just a couple of quick clarifications in between stuff, I'll play around some more later.

    This is just a short one hour a week program, think Catholic Sunday School. We have class on two days, but the kids only come to one class, on the same day each week. Each kid is in one grade the whole year. Most start and work their way up one grade at a time, but like I said, there are occasional exceptions. If a kid comes in and hasn't had their First Communion, they will go in the second grade class when that usually happens and then the next year jump up to where ever they belong age wise. It isn't divided up into various classes beyond that. Some years we will have two 1st grade classes, 1A and 1B, both on Sunday but that is about as complicated as it gets.

    Does that help you understand better what I'm trying to do? Thanks again for all your help!

  13. #13
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    The numbers in my example were arbitrary to illustrate the point of a grade spanning over a year time period.

    I think your clarification helped though. Let me me restate it see if I am on the right track.

    Grades do span the course of a year timeframe (1 grade per year) but seem to be more associated with age rather than progression through a stated curriculum. The only exception is with First Communion where the student would attend a 2nd grade class (which also spans the full year).

    Assuming what I have stated is true, you can determine a student's grade if you include their birthdate in the student table. In the same table, you would have a flag/boolean field to identify completion of the First Communion class.

    This way, grade would be determined dynamically without the need of another table and allowing for the exception.

  14. #14
    Join Date
    Oct 2014
    Posts
    34
    Provided Answers: 1
    Almost, but the exact age part isn't going to be very accurate. The problem is, like regular schools, you have some kids starting early, or sliding in because their birthday is just past the cut off, and some parents holding their kid back a year in regular school, so they would also be back a year here. So more or less based on age, but there are too many exceptions to that to use their birthday to assign classes.

  15. #15
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    I can certainly understand, however, you could probably still build it this way by employing a fudge factor. So the student table would have general information, birth date, the flag for the First Communion class, and age adjustment. For example (and these values again would be abritrary):

    StudentA would have a birthday of 06/14/2003 making them 11. Say this would normally put them in grade 3 and there is no adjustment.
    StudentB would have a birthday of 10/29/2003, just turning 11, but the parents want to hold them back a grade. Birthday would determine grade 3, but with an adjustment value of -1, they would be placed in 2nd grade.
    StudentC has a birthday of 1/20/2002 with an adjustment value of 1. This would have them in grade 5, however, the First Communion flag is not checked. This exception would have them in the 2nd grade by default.

    It might seem a bit convoluted, this method would have a couple of benefits:

    1. Student's grades would be determined dynamically using relatively simple logic in the query.
    2. Since the fudge factor is a value in a table associated with a student, it can be updated pretty easily if the need arises.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •