Creating a Database for Church Camp with Courses

[chasetherev]

Hello everyone! I hope you will be patient with me. This is my first post and I only found this forum yesterday. I have searched the forums for posts about camp and summer camp data bases but I haven't found anything that answered my question. I am new to Access as well.

Is it possible to create a database that can parse student data and sort students into classes they have chosen?
Our camp has 10 classes. The classes each have 3 meeting times. Students may choose 3 classes to participate in.



Example: Art 1, Archery 1, and Drama 1 all meet at the same time. A student who chooses all three needs to be sorted into all three classes at different times. One option would be Art 1, Archery 2, and Drama 3. (Other combinations would be fine.)

Also, is there a way to cap the roster for a class?
Example: Art 2 has 20 students and is now full. Any future registrants who choose Art must be sorted into either Art 1 or Art 3.

I'm using Access 2016 and importing registrant data from a Google sheet that is populated by a form on our website. I'm converting the sheet to excel and importing directly to Access and converting the data into a table. Not sure if that info is necessary but there it is.

I know what I am asking is likely difficult to build and probably costs quite a bit to pay someone to get up and running. I just want to know if this sort of thing is possible or if I am wasting time trying to build something so complex in Access.
I have scoured the internet for a template for summer camps. I believe part of my problem is I am so new I don't know the correct terms to search with to find what I am looking for. Either that or templates for things like this don't exist because they are expensive and time consuming to make, which I understand.

Currently we do this by hand on big poster boards and it is time consuming to say the least. I know this database idea would be time consuming as well but I hope I can set it up and use it for future years of camp.

Thank you for your consideration!
Chasetherev

[Minty]

You have written an excellent description of your needs, in plain non database speak, which is a great starting point.
What you need is not particularly easy for a beginner to do, however there may be someone here that has something you could use as a basis to start from.

Table design will be the first consideration - and I guarantee your current spreadsheets probably are not the way to start, but will have some of the data points you need to allow for.
Have read up on normalisation, and table design, then think about your current process and what you write down to enable it to happen.

Also have a read of the links that appear below this thread, in similar threads.

[chasetherev]

After I posted this I noticed the Similar Threads section. Taking a dive through those to see if anything helps. Thank you for your reply Minty!

[davegri]

GenericManyToManySimple.zip
Take a look at this. It will do the scheduling, but not handle the "Class full" situation. That needs to be added.

[Micron]

I have searched the forums for posts about camp and summer camp data bases but I haven't found anything that answered my question.

If the questions are what you posed in the first post, then Yes, and Yes. OK, I realize that's simplistic and may not be what you are searching for. I'm wondering if your searches were not quite focused because there are TONS of sample db's based on student/class design. It would be surprising if not one of them was even close to what you need. Not understanding Access very well might be a disadvantage when trying to determine if one is close enough.

Class full - would require a table that lists classes that contains at least a field for maxEnrollment. It might be a sub of a table or field that lists courses, because one day (if not now) it may be possible to run overlapping classes for the same thing (subject/course). These classes would need separate identification. This brings home the point of how important design can be, as anticipation is a good ingredient to bring to the design process.

If a student wants to enroll in a class, you will need to search tblClasses for that student id and see if the start date/time or the end date/time for the class lies between the start date/time and end date/time of any records found for that student id. If yes, there is a conflict.
Not sure if any of this helps. Suggest you try a search for "ms access student class database template" or similar. Even if you don't find one that exactly fits the bill, researching will likely expose you to ideas/concepts that you would otherwise not have thought of.