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

 

[spartansFC]

I'm a bit confused now, i want a schedule record that is from Apr-11 to Mar-12, i've attached a screen shot with some test data of my version of the recurring database to hopefully show you what i mean.

How do i go about appending a single record, via a query?

Mikie

 

[dhookom]

I can't view your file from work. "Apr-11 to Mar-12" could mean "April 11th, 2011 to March 12th, 2011" or "April 2011 to March 2012".

If you only have 1 record to append, why even use a separate temporary table?

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi Duane

i think the penny's finally dropped or at least i think it has, you're right about the need to do away with the temporary table, it's not needed, although i might just keep it just so that the forms work just like yours do.

So the way i've figured this out now is:

1. the user enters all the data in the unbound text/combo boxes
2. data is entered onto sfrmTempSchedule
3. then clicks on cmdBuildSchedule

Because all the months/years in my case or dates in yours is all in the tblTempSchedDates (sfrmTempSchedule), then this piece of code kicks in

strSQL = "Update tblTempSchedDates Set tscActID = " & lngActID & _
            ", tscLocID = " & lngLocID & ", tscStartTime =#" & Me.txtStartTime & _
            "#, tscEndTime = #" & Me.txtEndTime & "#"

the above adds all the unbound text/combo box data to all the fields in tblTempSchedDates where a month/year or date has been entered.

The field names in the code will have to be changed to mine, but have i figured out what i need to do.

I've tried to attach the picture as an embedded pic to show you what my form looks like.

Mikie

 

[dhookom]

I think you want code to append values from your form controls into a new record in either a temporary table or a permanent table. This would depend on if you need to edit the temp record before appending to the permanent. Since there is only one record, I would probably just add the record to the permanent schedule table.

The whole purpose of using the recurring functionality was to implement a more normalized table structure and automate the insertion of multiple schedule records. You haven't normalized and you don't have multiple records to insert.

I don't know exactly what your current table structures are or what determines when a new record is added or how you want to render these records or whatever.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Ok, so the whole reason for the occupancy builder is to try to predict for the entire year the number of sessions in 1 week a child will do.

So from my embedded picture, i hope you can see it, John Doe is doing 10 sessions per week at 3hrs per session from April 2011 to March 2012.

This would then be transferred to a permanent schedule table. Now if John Doe was to leave say in Decemeber 2011, the user could go into the permenent table and delete rows January 2012, February 2012.... etc and change the leave date to his exact leave date, the permenent schedule table would be a continuous form design.

If John Doe was to change the number of sessions he did, then the user could go into the permenent schedule table and change the number of sessions quite easily.

Then in March 2012 if the child was staying on at nursery, we could then repeat the steps in the schedule builder and build another 12 months worth of session date from April 2012 to March 2013.

I know that by using your method below:

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

that the table would be normalized and i realise my way of storing data as shown below isn't the right way

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

but that's the way i'd like to work my data. It's not the worst way of doing it but it's probably not the right way either.

So table structure wise on the perm/temp table would be:

tblSessionHistory

lngSessionHistoryID
lngChildID
lngNurseryID
ysnDeactivate
ysnCurrentSession
strMonSessionAM
strTueSessionAM
strWedSessionAM
strThurSessionAM
strFriSessionAM
StrMonSessionPM
strTueSessionPM
strWedSessionPM
strThurSessionPM
strFriSessionPM
lngSessionHours
lngSessionTermTypeID
lngFundedTypeID
lngFundedMainID
lngFundedSubID
dteSessionStartDate
dteSessionEndDate

so the above table would be shown as a continuous form for each child, so one child might have 6 records (April 2011 to October 2011), or another child might have 12 records (April 2011 to March 2012)

What determines whether a new record is added is if the child were to change his current sessions or if they've had an extra session.

I hope i'm making sense.

Mikie

 

[dhookom]

What do you expect to store in the fields: dteSessionStartDate and dteSessionEndDate?

Why are you using text fields to store session information? Shouldn't this be a number of hours or something other than a string?

Is each record in the main table representing a week or month or what?

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi

What do you expect to store in the fields: dteSessionStartDate and dteSessionEndDate?

The above dates are pulled from the month/year combo box in sfrmTempSchedule, when a user selects Apr-2011, that month is associated to start and end dates, so there just there for information purposes.

Why are you using text fields to store session information? Shouldn't this be a number of hours or something other than a string?

I guess i should change the session information (MonAM, TueAM... etc) to a number instead of string field. The session field will only ever be a 0 or 1. A child can only do 1 session in the AM or 1 session in the PM.

There is a field lngSessionHours which records the number of hours a child does per session

Is each record in the main table representing a week or month or what?

Each record that is input in the unbound fields represents a week, when the user selects a month/year from sfrmTempSchedule, that month again has an associated "number of weeks in that month" which i use in my calculation.

I have tried to to use the code in my database and it seems to work the way i want it.

strSQL = "Update tblOccupancyBuildNewTemp Set tsclngChildID = " & lngChildID & _
             ", tsclngNurseryID = " & lngNurseryID & ""
    db.Execute strSQL, dbFailOnError

what happens is, it will display the month/year, and at the minute just the child Name, Nursery Name, i need to add all the extra fields to the above code to pull them all through.

Mikie

 

[dhookom]

I don't know your business but apparently you are stuck with "lngSessionHours which records the number of hours a child does per session" for the entire week. Can't the number of hours vary by day? Why not store the number of hours in your un-normalized structure for greater functionality.

Again, I expected you would be appending records, not updating records.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

With lngSessionsHours, it's a standard number of hours per week, so if a child was doing 2 sessions per day, 1 in the AM, 1 in the PM and the hrs were 5 hrs per session, they'd be there for 10hours, even if they were to leave early, they'd still be charged for the 10hours.

So the hours wouldn't need to vary in a week, but if a child changes their sessions/hours the user can insert a new row and change the data to suit i.e. increase/decrease number of sessions or hours.

Again, I expected you would be appending records, not updating records.

Does this mean that in the code i've used Update instead of Insert?

I'm nearly there though with all your help and patience.

Mikie

 

[dhookom]

Your stated your code is:

strSQL = "Update tblOccupancyBuildNewTemp

Clearly this is and update query. An append record to add new records would begin with

strSQL = "[red]INSERT INTO[/red] tblOccupancyBuildNewTemp

However, I see you have manually typed in the dates into the subform. This would be an update of the temporary schedule records that already exist.

Consider creating an update query with the temporary table and the form controls. Use the sql generated to create your actual SQL statement in code (that's how I do it).


Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi

However, I see you have manually typed in the dates into the subform. This would be an update of the temporary schedule records that already exist.

This isn't exactly true, on the subform the month/year field is a combo box which has the following row source:

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

the above means that each Month-Year has a relevent Start date and end date, i thought this would be quicker than having the user having to re-enter the start and end dates, so when a user selects a month-year, i've set up a text box via:

=cmbOccupancyMonthYear.Column(3)

so the start and end date fill the text box up automatically based on the selection.

so this is the strSQL code i'm using which works nearly (having a few little problems:

strSQL = "Update tblOccupancyBuildNewTemp Set tsclngChildID = " & lngChildID & _
             ", tsclngNurseryID = " & lngNurseryID & _
             ", tsclngMonSessionAM = " & lngMonAM & _
             ", tsclngTueSessionAM = " & lngTueAM & _
             ", tsclngWedSessionAM = " & lngWedAM & _
             ", tsclngThurSessionAM = " & lngThurAM & _
             ", tsclngFriSessionAM = " & lngFriAM & _
             ", tsclngMonSessionPM = " & lngMonPM & _
             ", tsclngTueSessionPM = " & lngTuePM & _
             ", tsclngWedSessionPM = " & lngWedPM & _
             ", tsclngThurSessionPM = " & lngThurPM & _
             ", tsclngFriSessionPM = " & lngFriPM & _
             ", tsclngFundedTypeID = " & lngFundedTypeID & _
             ", tsclngFundedMainID = " & lngFundedMainID & _
             ", tsclngFundedSubID = " & lngFundedSubID & _
             ", tsclngSessionTermTypeID = " & lngSessionTermTypeID & _
             ", tsclngSessionHours = " & lngSessionsHours & _
             ", tscdteSessionEndDate = " & Me.frmOccupancyBuildNewSubMonthYear.Form.TempMonthEndDate_txtbox & _
             ", tscdteSessionStartDate = " & Me.frmOccupancyBuildNewSubMonthYear.Form.TempMonthStartDate_txtbox & ""
            db.Execute strSQL, dbFailOnError

so everything is pulled through via the above code onto tblTempSchedDates but i can't seem to pull the start and end dates through.

on tblTempSchedDates, i have tsclngOccupancyMonthYear which is linked to tblOccupancyNewBuildMonthYearsDescriptions which has

lngPlacementMonthsID
strPlacementMonthYear
strMonthSort
dteMonthYear
lngNumberWeeks
lngClosureWeeks
lngChargeableWeeks
lngTermTimeWeeks
lngNonTermWeeks
lngAdditionalBankHolidays
lngAdditionalFridayBankHolidays
dteMonthStartDate
dteMonthEndDate

so when i try to test that the form works, i fill in all the unbound fields, select April-2011 (start Date 04/04/2011, end date 29/04/11), when i build the temporary schedule, everything is pulled through correctly, except the start and end dates which display 30/12/1899..... not sure why that's happening?

Mikie

 

[dhookom]

Your dates are in the wrong format. They should be m/d/y and must be delimited with "#".

If you don't have your temporary schedule date bound to records in a subform, then you will need to append records.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi Duane

Thanks for all your help on my database problem, i've finally sorted it. I'm not addiding the start and end dates, the user will add those manually. Everything else works perfectly thanks to you.

You've got a lot of patience so thanks for putting up with my stupid questions.

Thanks again

Mikie

 

[spartansFC]

Hi Duane

I really thought i'd cracked this but then realised that the design doesn't capture everything. So i need to prepopulate the permenent schedule by weeks, so each year will have 52 weeks with the respective data.

So i was trying out the recurring database, i changed the option to repeating entered the start date and end date by the calender function, they were displayed as:

Start date: 04/01/2011
End date: 04/29/2011

and then i wanted to show all monday dates inbetween those start and end dates, this is what was output

04/04/2011
04/18/2011
04/25/2011
11/04/2011

So it looks like the last date is showing November 4, instead of April 11?

Not sure why this is, if i can crack this problem then i know what i need to do.

Do you know why the recurring database would be outputting the date incorrectly?

Mikie

 

[dhookom]

Sounds like a location date issue. You might want to enter all dates as yyyy-mm-dd.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi

Your suggestion didn't work, i changed the format to yyyy-mm-dd, and tried the following test data:

Start Date: 2011-09-01
End Date: 2011-09-30

I wanted only the monday's in that month/criteria

it was outputted as

2011-05-09
2011-09-19
2011-09-26
2011-12-09

So again, not sure why. Did you mean location date issue as in i'm in the UK?

Mikie

 

[dhookom]

Can you share your code?

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

I was only testing this method on your recurring database so here's the code.

Private Sub cmdBuildSchedule_Click()
    Dim datThis As Date
    Dim lngActID As Long
    Dim lngLocID As Long
    Dim varNotes As Variant
    Dim strSQL As String
    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.cboActID) Then
        MsgBox "You must select an Activity.", vbOKOnly + vbInformation, "Enter Activity"
        Me.cboActID.SetFocus
        Me.cboActID.Dropdown
        Exit Sub
    End If
    If IsNull(Me.cboLocID) Then
        MsgBox "You must select a Location.", vbOKOnly + vbInformation, "Enter Location"
        Me.cboLocID.SetFocus
        Me.cboLocID.Dropdown
        Exit Sub
    End If
    'strTitle = Me.txtTitle
    varNotes = Me.txtNotes
    lngLocID = Me.cboLocID
    lngActID = Me.cboActID
    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

I was googling around to see if there was some way to force the date format in VBA but i couldn't figure it out or find the right website.

Mikie

 

[dhookom]

If you want the dates to be weekly (7 days apart) you would need to change this line

        For datThis = Me.txtStartDate To Me.txtEndDate

to

        For datThis = Me.txtStartDate To Me.txtEndDate Step 7

Check Allen Browne's International Dates.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi

so i tried your suggestion and it didn't work, still changed 11/04/2011 to 04/11/2011.

So i thought i'd try to transfer all the code etc from the recurring dbase onto mine, i got it working but now i'm getting an error: Run time error 3134 Syntax error in INSERT INTO statement.

I think it's because the code below, i'm missing a few "" or even ' in the lines that are split across 2 lines etc.

Hopefully it should work once this little problem is sorted: the code i have which has been worked into my forms is:

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 tblOccupancyBuildNewTemp (" & _
                    "tscDate, tsclngNurseryID, tsclngFundedTypeID, " & _
                    "tsclngMonSessionAM, tsclngTueSessionAM,  tsclngWedSessionAM ) " & _
                    "Values(#" & datThis & "#," & lngNurseryID & ", " & _
                    lngFundedTypeID & ", " & lngMonAM & ", " & lngTueAM & ", " & _
                    lngWedAM & ", " & ")"
                    'IIf(IsNull(varNotes), "Null", """" & varNotes & """") & ")"
                db.Execute strSQL, dbFailOnError

the error sticks

db.execute strSQL, dbFailonError

Mikie