Help with designing tables for new kindergarten db

[StarFish]

Hi

I'm embarking on my first proper Access db to replace the numerous xl spreadsheets we have previously used. The db is to store and manage information for a kindergarten, I'm doing OK with most of the basic requirements but not sure how to go about designing the tables and updating them for the following:

Children attend for varied sessions during the week eg. Mon 9-3, Tue 8-5.30, Wed 9-3..... these sessions are fixed in the short term but may be changed with 4 weeks notice.



The children may continue with the same sessions during the school holidays or not attend or have reduced attendance.

I have a table to capture general info about the children but I'm struggling to think of the best way to go about the session details, this will hopefully be used for invoicing and forecasting (staff requirements, available spaces).

I'm thinking that I will need to make a table for each child with: Date (first day of the week),M,T,W,T,F (session details). These tables would need to be populated 3-6 months in advance for forecasting purposes and updated as changes occur.

If I have to have a table for each child I'm hoping that the following will be possible:

• Batch copy weeks forward from a single form button for all tables
• Stop a batch copy for records when a leaving date has been entered for a child
• Update a change of sessions from a given date, updating all records forward of this date
• Update holiday sessions when required (from a separate table)

I hope all this makes sense and would welcome any guidance about possible approaches to take or things to consider.

Many Thanks

[June7]

DO NOT have a table for each child - not if you want to keep your sanity.

How individualized can each child's enrollment plan be? In my experience with childcare programs (I worked for CampFire), parents chose from standardized monthly plan options (morning only, afternoon only, both) and each option had a fixed cost.

[StarFish]

Very Individualised. There's a core day 9-3 which can be split am,pm at additional cost then outside of core times sessions can be booked hourly at a different rate 8-9 & 3-5:30.

I thought individual tables seemed like a lot of work but I don't know what the alternative would be, hence the question.

[June7]

Aside from my advice about not having separate table for each child, really don't know what to offer. Description of 'sessions' is reminiscent of time & attendance reporting. T&A is not an easy concept for relational database. Mostly because time sheets people routinely use to record their daily time do not reflect normalized data entry structure.

Projection (what if analysis) is not Access strong suit because records must be created for the analysis to run on.

When I was with CampFire, they contracted a professional software developer to customize a database to track enrollments. I seem to remember the cost was about $50,000, and that was 15 years ago. It was a proprietary app, meaning any mods had to be done by the developer.

You might find this discussion of interest https://www.accessforums.net/databas...ses-18459.html

[StarFish]

Thanks for your responses June7. It sounds like this will still need to be managed by excel in that case, which isn't good for your sanity either! At least I will save some time by not pursuing an unworkable solution.

[StarFish]

Is there the possibility that a single table could be used that contains all session information, but when a change is entered with a from date Access could apply those changes going forward?

Date Child M T W T F
1/5/14 A 9-3 9-3
4/6/14 B 8-5 8-4
1/8/14 C 9-12 9-12


So would it be a possibility to write a query that would return the session results above between 2 dates eg. 1/5/14- 1/12/14 so that children in on each day could be viewed on a weekly basis. ChildA sessions would be fully populated weekly between those dates, ChildB from 4/6/14-1/12/14 and ChildC from 1/8/14.

Then if ChildC increases her sessions from 1/10/14 to 9-3 both days would it be possible for Access to read 9-12 1/5/14-1/10/14 and 9-3 1/10/14 onwards

Date Child M T W T F
1/5/14 A 9-3 9-3
4/6/14 B 8-5 8-4
1/8/14 C 9-12 9-12
1/10/14 C 9-3 9-3

[June7]

I didn't mean to imply it couldn't be done, just that it would not be easy. Especially for someone with little to no programming skills. It can certainly be done, as demonstrated by the CampFire case. All depends on how much time and money want to throw at it.

I helped build a database for our laboratory test data and it took 4 years from inception to full implementation. It isn't even as complex as the CampFire app. Whereas all the other databases I have built took only days to a couple weeks.

EDIT: Just saw your latest post. All I can say is that pretty much anything is possible with enough code.