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

 

[dhookom]

I'm not sure I agree with the un-normalized data structure. However, it's your application and you don't seem to want lots of data entry.

What are these fields used for in tblChildCurrentSessions:

currentsessionstartdate    date/time    
currentsessionenddate      date/time

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi Duane

No, its fine, i don't mind data entry as long as i manage to get this problem sorted.

Do you mean by un-normalized data structure in that i'm duplicating fields in more than 1 table?

As for

currentsessionstartdate    date/time    
currentsessionenddate      date/time

they are just to show the user when a child has started at the nursery and when they leave, or if a child changes their sessions, we'd move the data from tblcurrentsession into tblPlacement.

the start and end date isn't used in any sort of calculation, it's just there for info only.

If you were to design a table based on my problem, how would you have gone about it?

thanks for your help

Michael

 

[dhookom]

It might be difficult to modify your system but I would probably not have all of these fields. To me, this might represent 10 records, not values in 10 fields. You are storing a day of the week and a time of the day in field names. I like to store data in field values, not field names.

strplacementMonAM    Text    
strplacementTueAM    Text    
strplacementWedAM    Text    
strplacementThurAM    Text    
strplacementFriAM    Text    
strplacementMonPM    Text    
strplacementTuePM    Text    
strplacementWedPM    Text    
strplacementThurPM    Text    
strplacementFriPM    Text

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

In addition, I was thinking

currentsessionstartdate    date/time    
currentsessionenddate      date/time

might be the effective dates so when a schedule changes, a new record would be added with the next range of dates.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi Duane

thanks for taking all your time trying to sort out another mess of mine. I don't mind if i have to totally redo tables, forms or modify the database in any way in order for this to work.

I sort of understand the bit about storing a day of the week and a time of the day in field names but i got lost at the I like to store data in field values, not field names.

i then got even more lost about using currentsessionstartdate and currentsessionenddate as effective dates when a schedule changes.

So with all that in mind, would the form be a continuous form? Am i jumping ahead as i still don't understand the new table structure. So would a basic structure be:

tblCurrentSessions

lngCurrentSessionID (PK)
lngChildID (FK)
dtecurrentsessionstartdate
dtecurrentsessionenddate
lngNurseryname
lngHoursperSession

how do i then show what sessions a child did in a day/week in both AM/PM. the child can only do 1 session in either AM/PM.

If a child does an extra session on AM/PM on any week day, how do i show this?

This has to be the most complex thing i've been asked to do yet in access.

Mikie

 

[dhookom]

I'm not quite sure how you are using the tables. If you want to estimate future attendance in tblChildCurrentSessions and there schedule might change, then you need one record per child per change. These would be "stamped" with From and To dates. That's what I thought the CurrentSession Dates were for. If Johnny Adams had one schedule from 9/1/2011 to 12/31/2011 and different schedule form 12/31/2011 to 5/31/2012, then there would be two records for Johnny in the table.

Every time a child's schedule would change, there would be a new record with new From and To dates.

I might consider creating tblChildCurrentSessions with a table structure like:
[tt]
ChildCurrentSessionID
ChildID
Nursery
SessionDate
AmPM A or P or B
Status S for Scheduled, A for Attended,...
[/tt]

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

I'm not quite sure how i explain how i'm using the tables, but it's probably not the right way. Do you mean what the relationships are or how i record the data?

Anyways in your tblChildCurrentSessions table:

ChildCurrentSessionID
ChildID
Nursery
SessionDate
AmPM A or P or B
Status S for Scheduled, A for Attended,...

Does this mean you would have a record for each day of the month? I still quite like the idea plus all the nurseries use the MonAm, TueAM.... etc method and i understand now why it's not such a good idea but i think i might have to use a bit of my method and alot of the way you might have done it.

So i've tried to create some test data in excel as i'm more a visual person of your method and a bit of mine, i took a screen shot and attached it, and also did some test data of what i thought you meant.

In my test data you can see that in April, Johnny Adams changed his sessions from 5 sessions in the morning to 2 sessions in the morning.

Also in may he changed them again, the rest of the year was the same until Mar-12.

I've added start and end dates and also a month field, i'm not sure if this is a good idea?

If i used the method from the attached, i could get rid of the duplicate tables which basically do record the same sort of info and get my database more normalised.

Mikie

 

[dhookom]

I can't get at your file from work.
My basic system would suggest that every child that attends a morning or afternoon session on a day would create a record. These might all be prepopulated for future "scheduled" attendance.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

I like the words prepopulated for future scheduled attendance

so how do i prepopulate fields for an entire year, if a child started in say September 19 2011, would it then populate the data to the end of the financial year march 31 2012?

Would prepopulating work for months only, say if the user selected a month "Sept-11" from a combo box, would it then fill in the records up to "Mar-12"?

 

[dhookom]

I have a sample for setting up recurring events at Rogers Access Library

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

I downloaded and had a quick look a the recurring events database at work, looks really good and just what i'm after.

It seems to have solved most of my problems in one go, although i haven't tried to merge it with my database yet.

No doubt i'll have more questions but thanks again for all your help Duane

Mikie

 

[dhookom]

Glad to hear you found some of my work useful.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi Duane

So i've finally got round to modifying your Schedule Building dbase so that it works for me, i've created my own frmMultiSchedule and tblTempSchedDates and i've changed the code to suit my field names but now i'm a bit stuck.

In your database, you give the user 2 options via grpRepeats, i don't need this so the code on cmdBuildSchedule needs changing, i've started to change it but it's the repeating function of the code is where i'm getting stuck.

Private Sub cmdBuildSchedule_Click()

Dim strSQL As String
Dim lngChildID As Long
Dim NurseryID As Long
Dim strMonSessionAM As Variant
Dim strTueSessionAM As Variant
Dim strWedSessionAM As Variant
Dim strThurSessionAM As Variant
Dim strFriSessionAM As Variant
Dim strMonSessionPM As Variant
Dim strTueSessionPM As Variant
Dim strWedSessionPM As Variant
Dim strThurSessionPM As Variant
Dim strFriSessionPM As Variant
Dim lngSessionHours As Long
Dim lngSessionTermType As Long
Dim lngFundedType As Long
Dim lngFundedMain As Long
Dim lngFunded Sub As Long
Dim db As DAO.Database


‘   Dim intDOW As Integer 'day of week
‘   Dim intDIM As Integer 'Day in month

‘   If Me.grpRepeats = 2 Then
‘      If Not CheckDates() Then
‘           Exit Sub
‘       End If
‘   End If
‘   If Not CheckTimes() Then
‘       Exit Sub
‘   End If


    If IsNull(Me. cmbChildsNameID) Then
        MsgBox "You must select an Child’s name.", vbOKOnly + vbInformation, "Enter Child’s name"
        Me.cmbChildsNameID.SetFocus
        Me.cmbChildsNameID.Dropdown
        Exit Sub
    End If
    If IsNull(Me.cmbNurseryNameID) Then
        MsgBox "You must select a Nursery.", vbOKOnly + vbInformation, "Enter Nursery"
        Me.cmbNurseryNameID.SetFocus
        Me.cmbNurseryNameID.Dropdown
        Exit Sub
    End If
    If IsNull(Me.cmbTermTypeID) Then
        MsgBox "You must select a Term Type.", vbOKOnly + vbInformation, "Enter Term Type"
        Me.cmbTermTypeID.SetFocus
        Me.cmbTermTypeID.Dropdown
        Exit Sub
    End If
    If IsNull(Me.cmbFundedTypeID) Then
        MsgBox "You must select a Funded Type.", vbOKOnly + vbInformation, "Enter Funded Type"
        Me.cmbFundedTypeID.SetFocus
        Me.cmbFundedTypeID.Dropdown
        Exit Sub
    End If
    If IsNull(Me.cmdFundedMainID) Then
        MsgBox "You must select a Main Funding.", vbOKOnly + vbInformation, "Enter Main Funding"
        Me.cmdFundedMainID.SetFocus
        Me.cmdFundedMainID.Dropdown
        Exit Sub
    End If
If IsNull(Me.cmbFundedSubID) Then
        MsgBox "You must select a Sub Funding.", vbOKOnly + vbInformation, "Enter Sub Funding"
        Me.cmbFundedSubID.SetFocus
        Me.cmbFundedSubID.Dropdown
        Exit Sub
    End If

 
lngChildID = Me.cmbChildsNameID
lngNurseryID = Me.cmbNurseryNameID
lngSessionTermTypeID = Me.cmbTermTypeID
lngFundedTypeID = Me.cmdFundedMainID
lngFundedMainID = Me.cmbFundedTypeID
lngFundedSubID = Me.cmbFundedSubID
Set db = CurrentDb

    If Me.grpRepeats = 2 Then 'need to loop through dates
        For datThis = Me.txtStartDate To Me.txtEndDate
            intDIM = GetDIM(datThis)
            intDOW = Weekday(datThis)
            If Me("chkDay" & intDIM & intDOW) = True Or _
                    Me("chkDay0" & intDOW) = True Then
                strSQL = "INSERT INTO tblTempSchedDates (" & _
                    "tscDate, tscActID, tscLocID, " & _
                    "tscStartTime, tscEndTime,  tscNotes ) " & _
                    "Values(#" & datThis & "#," & lngActID & ", " & _
                    lngLocID & ", #" & Me.txtStartTime & "#, #" & _
                    Me.txtEndTime & "#," & _
                    IIf(IsNull(varNotes), "Null", """" & varNotes & """") & ")"
                db.Execute strSQL, dbFailOnError
            End If
        Next
     Else  'dates are there, just add the title, notes, times, location, Activity
        strSQL = "Update tblTempSchedDates Set tscActID = " & lngActID & _
            ", tscLocID = " & lngLocID & ", tscStartTime =#" & Me.txtStartTime & _
            "#, tscEndTime = #" & Me.txtEndTime & "#"
            
        If Len(varNotes & "") > 0 Then
            strSQL = strSQL & ", tscNotes = " & IIf(IsNull(varNotes), Null, """" & varNotes & """")
        End If
        db.Execute strSQL, dbFailOnError
    End If
    Me.sfrmTempScheduleEdit.Requery
    MsgBox "Temporary schedule built. " & _
        "You can now edit the schedule and " & _
        "append to the permanent schedule.", vbOKOnly + vbInformation, "Temp schedule complete"
End Sub

As you can see i've added all the definitions at the begininning
which are for my fieldnames, i've also changed and added more If statements to catch when the user doesn't select an option.

I don't think i need the following

‘   Dim intDOW As Integer 'day of week
‘   Dim intDIM As Integer 'Day in month

‘   If Me.grpRepeats = 2 Then
‘      If Not CheckDates() Then
‘           Exit Sub
‘       End If
‘   End If
‘   If Not CheckTimes() Then
‘       Exit Sub
‘   End If

as i'm not using the group options on the form.

On sfrmTempSchedule (tblTempSchedDates) you use tscDate to allow the user to enter the dates but i want the user to select months-year (Apr-11, May-11 .... etc) so i've got lngOccupancyMonthYear, when the user selects the month, the month is associated to the dates (Apr-11 01/04/11 to 29/04/11) i want to pull these dates through onto tblTempSchedDates but i'm not sure how to do that. I have created fields in my tblOccupancyBuildNewTemp (dteSessionStartDate and dteSessionEndDate)

A way i thought might work was when a user selects a Month-Year on the row source i have

SELECT tblOccupancyNewBuildMonthYearsDescriptions.lngPlacementMonthsID, tblOccupancyNewBuildMonthYearsDescriptions.dteMonthYear, tblOccupancyNewBuildMonthYearsDescriptions.strPlacementMonthYear, tblOccupancyNewBuildMonthYearsDescriptions.dteMonthStartDate, tblOccupancyNewBuildMonthYearsDescriptions.dteMonthEndDate FROM tblOccupancyNewBuildMonthYearsDescriptions ORDER BY tblOccupancyNewBuildMonthYearsDescriptions.dteMonthYear DESC;

and on sfrmTempSchedule i have in a textbox

=cmbOccupancyMonthYear.Column(3)

i was then going to say that dteSessionStartDate = TempMonthStartDate_txtbox. Not sure that would work though.

So the problem i'm having is which part of the code do i use to loop through all the dates.

I think your code which starts

 If Me.grpRepeats = 2 Then 'need to loop through dates
        For datThis = Me.txtStartDate To Me.txtEndDate
            intDIM = GetDIM(datThis)

is for when a user ticks all the boxes but i need to use

  'dates are there, just add the title, notes, times, location, Activity
        strSQL = "Update tblTempSchedDates Set tsclngChildID = " & lngChildID & _
            ", tsclngNurseryID = " & lngNurseryID & ", tscstrMonSessionAM =" & Me. MonAM_txtbox & “

I need to expand the above code to use all my fields, i was just filling in part to show you what it would look like compared to yours.

So how do i get it to loop through all the dates?

Mikie

 

[dhookom]

I don't understand your new table structure(s). Are you still planning on using multiple day fields?

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi

Yep, still using multiple day fields, or thats the idea.

On your recurring Dbase on frmMultiSchedule, you have sfrmTempSchedule which is using tblTempSchedDates and you record the multiple dates by using tscDate which the user has to enter.

I'm just slightly changing that bit by allowing the user to select via a combo box the month/year (Apr-11, May-11, June-11... etc)

So my forms are identical to yours apart from i'm not using your grpRepeats, and your tscDate on mine is cmbOccupancyMonthYear which has the code on row source of

SELECT tblOccupancyNewBuildMonthYearsDescriptions.lngPlacementMonthsID, tblOccupancyNewBuildMonthYearsDescriptions.dteMonthYear, tblOccupancyNewBuildMonthYearsDescriptions.strPlacementMonthYear, tblOccupancyNewBuildMonthYearsDescriptions.dteMonthStartDate, tblOccupancyNewBuildMonthYearsDescriptions.dteMonthEndDate FROM tblOccupancyNewBuildMonthYearsDescriptions ORDER BY tblOccupancyNewBuildMonthYearsDescriptions.dteMonthYear DESC;

everything else is the same as your dbase, all the unbound forms etc, the bit i'm struggling on is when the user clicks cmdBuildSchedule, it's how to get the dbase to loop through all the dates cmbOccupancyMonthYear and create the fields in the tblTempSchedDates.

Mikie

 

[dhookom]

So you are going against my advice

I might consider creating tblChildCurrentSessions with a table structure like:

ChildCurrentSessionID
ChildID
Nursery
SessionDate
AmPM A or P or B
Status S for Scheduled, A for Attended,...

If this is the case, please provide your table and field names as well as any significant relationships. Also, provide a specification of what you want to do.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi Duane

I'm sort of going against your advice, i know your way would be far better but i need to have the database suit existing forms and the way data is paper recorded, i'm still going to use your recurring.dbf.

So below i've listed the fields (unbound and bound) of what you use and what fields i'm using (in brackets)

frmMultiSchedule

cboActID
txtStartTime
txtEndTime
cboLocID

(cmbChildsNameID)
(cmbNurseryNameID)
(MonAM_txtbox)
(TueAM_txtbox)
(WedAM_txtbox)
(ThurAM_txtbox)
(FriAM_txtbox)
(MonPM_txtbox)
(TuePM_txtbox)
(WedPM_txtbox)
(ThurPM_txtbox)
(FriPM_txtbox)
(SessionHours_txtbox)
(cmbTermTypeID)
(cmbFundedTypeID)
(cmdFundedMainID)
(cmbFundedSubID)

on sfrmTempSchedule

tscDate
tscActID
tscStartTime
tscEndTime
tscTitle
tscNotes
tscLocID

(tsclngOccupancyMonthYear)
(tsclngChildID)
(tsclngNurseryID)
(tscysnDeactivate)
(tscysnCurrentSession)
(tscstrMonSessionAM)
(tscstrTueSessionAM)
(tscstrWedSessionAM)
(tscstrThurSessionAM)
(tscstrFriSessionAM)
(tscStrMonSessionPM)
(tscstrTueSessionPM)
(tscstrWedSessionPM)
(tscstrThurSessionPM)
(tscstrFriSessionPM)
(tsclngSessionHours)
(tsclngSessionTermTypeID)
(tsclngFundedTypeID)
(tsclngFundedMainID)
(tsclngFundedSubID)
(tscdteSessionStartDate)
(tscdteSessionEndDate)

So the bit of coding that i need help on is below:

  If Me.grpRepeats = 2 Then 'need to loop through dates
        For datThis = Me.txtStartDate To Me.txtEndDate
            intDIM = GetDIM(datThis)
            intDOW = Weekday(datThis)
            If Me("chkDay" & intDIM & intDOW) = True Or _
                    Me("chkDay0" & intDOW) = True Then
                strSQL = "INSERT INTO tblTempSchedDates (" & _
                    "tscDate, tscActID, tscLocID, " & _
                    "tscStartTime, tscEndTime,  tscNotes ) " & _
                    "Values(#" & datThis & "#," & lngActID & ", " & _
                    lngLocID & ", #" & Me.txtStartTime & "#, #" & _
                    Me.txtEndTime & "#," & _
                    IIf(IsNull(varNotes), "Null", """" & varNotes & """") & ")"
                db.Execute strSQL, dbFailOnError
            End If
        Next
     Else  'dates are there, just add the title, notes, times, location, Activity
        strSQL = "Update tblTempSchedDates Set tscActID = " & lngActID & _
            ", tscLocID = " & lngLocID & ", tscStartTime =#" & Me.txtStartTime & _
            "#, tscEndTime = #" & Me.txtEndTime & "#"
            
        If Len(varNotes & "") > 0 Then
            strSQL = strSQL & ", tscNotes = " & IIf(IsNull(varNotes), Null, """" & varNotes & """")
        End If
        db.Execute strSQL, dbFailOnError
    End If

Because i'm not using your grpRepeats i'm not sure how i'd rework the code.

I think i know how to to do the qappTempSchedToPermanentSched query so that shouldn't be a problem, its just creating the tblTempSchedDates that i can't do.

Mikie

 

[spartansFC]

Oops, forgot to add, i've listed the forms and tables

sfrmTempSchedule via tblTempSchedDates
frmOccupancyBuildNewSubMonthYear via tblOccupancyBuildNewTemp

the one in red is my form and table name. So i basically want the user to enter all the data in all the unbound forms, then enter the months in frmOccupancyBuildNewSubMonthYear, the user then clicks on cmdBuildSchedule which then creates the tblTempSchedDates and then cmdAppendToPermanent to transfer the data to tblSchedule which is what your amazing reccuring dbase does.

Mikie

 

[dhookom]

Are you suggesting you only want to store one schedule record per child per month? Or, do you have one schedule record that is in effect from tscdteSessionStartDate to tscdteSessionEndDate?

Since you have start and end dates I assume the second solution. If this is the case, there is no need to do anything with recurring. Just append a single record.


Duane
Hook'D on Access
MS Access MVP