Table Design HELP

[spartansFC]

Hi

So i've created a nursery database that basically has a list of families and the children who go to various nurseries. It stores family address, parents names, children details (date of birth, age, who the children are funded by. It also allows for letters to be sent out via reports.

The database works great but now they've asked my to add occupancy info, which means i need to try to predict on a month by month basis the number of sessions per day a child does.

So i started by creating 2 tables:

tblChildCurrentSessions		
ChildCurrentSessionID	autonumber	PK
ChildID	Number	FK
Nursery	Number	
sessions	Number	
strplacementMonAM	Text	
strplacementTueAM	Text	
strplacementWedAM	Text	
strplacementThurAM	Text	
strplacementFriAM	Text	
strplacementMonPM	Text	
strplacementTuePM	Text	
strplacementWedPM	Text	
strplacementThurPM	Text	
strplacementFriPM	Text	
currentsessionstartdate	date/time	
currentsessionenddate	date/time

tblChildPlacement (Occupancy)		
lngPlacemendID	autonumber	PK
ChildID	Number	FK
lngPlacementMonthYear	Number	
lngNursery	Number

I use both tables in a query and i can predict for the entire year the number of sessions a child does, but this leads me to a problem.

At the moment the session info is a single form via tblChildCurrentSessions so if a child does 10 sessions per week for half the year and then changes the sessions to only 5, the query thinks the child has only done 5 sessions the entire year and not 10 sessions for 6 months and 5 sessions for 6 months.

I do record session histoy via:

tblPlacement (Session History)	
PlacementID	autonumber
ChildID	Number
placementcurrent	yes/no
NurseryID	Number
startdate	date/time
leavedate	date/time
sessions	Number
placementMon	Text
placementTue	Text
placementWed	Text
placementThur	Text
placementFri	Text
placementMonPM	Text
placementTuePM	Text
placementWedPM	Text
placementThurPM	Text
placementFriPM	Text

So you can see straight away that i'm duplicating things which isn't good database design, i know that i might have to re-do the occupancy data on a continuos form and not a single form, but if i do that i would have to have 12 rows from Apri-11 to Mar-12 and then fill in the StrplacementMonAM, strplacementPM..... for every row which is alot of data entry. Then i would have to repeat that process for Apr-12 to Mar-13.

If that's the only way i can do then thats fine, but there has to be a better way.

Maybe since i'm using tblPlacement(Session History)to record a history i could add another field to record the month. I'm not sure what to do, i've been trying to cover every variation of what could happen and i can't see the wood for the trees now.

Here's 1 variation:

When a user selects a month, that month has associated fields

lngChargeableWeeks
lngTermTimeWeeks
lngNonTermWeeks
AdditionalMonBankHolidays
AdditionalFridayBankHolidays

in Apr-11 there are 3 bank holidays whereby the child doesn't attend nursery and therefore the sessions won't be included. So if a child leaves mid april in the query i would have to have something like:

ActualSessions: IIf lngPlacementMonthYear ="Apr-11" and sessionEndDate < 21/04/11,[calculatedsession], iif lngplacement ="Apr-11" and sessionsEndDate < 23/04/11, [calculatedsession]-[AdditionalFridayBankHolidays]......

the above code would have to be worked out for all the other months that have bank holidays, this is just one of the problems i'm facing

Other problems that i need to overcome are:

New table design or modify existing one
Work out how to record extra sessions
Work out how to calculate if a child leaves mid month, not to count the sessions for the rest of the month

I have managed based on the current design a query to predict
the number of sessions so i know that i'm on the right track, i just think i need to re-think my table design a bit better.

I've attached a jpg with the data i can get from the database, i'm summarised it in excel as i haven't even started the report yet plus it was just to see if the data was calculating properly.

Can anyone please help

Mikie

 

[PHV]

Replace this:
lngWedAM & ", " & ")"
with this:
lngWedAM & ")"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[spartansFC]

Thanks for your help PHV, the forms works perfectly

 

[spartansFC]

And thanks for all your help Duane, the database works perfectly now, just got to train the user up......

.... now thats difficult