INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Table Design HELP

Table Design HELP

(OP)
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:

CODE

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
    

CODE

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:

CODE

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:

CODE

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

RE: Table Design HELP

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:

CODE

currentsessionstartdate    date/time    
currentsessionenddate      date/time

Duane
Hook'D on Access
MS Access MVP

RE: Table Design HELP

(OP)

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

CODE

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

RE: Table Design HELP

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.

CODE

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

RE: Table Design HELP

In addition, I was thinking

CODE

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

RE: Table Design HELP

(OP)
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

 

RE: Table Design HELP

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:

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

Duane
Hook'D on Access
MS Access MVP

RE: Table Design HELP

(OP)
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

RE: Table Design HELP

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

RE: Table Design HELP

(OP)

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"?

RE: Table Design HELP

(OP)

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

RE: Table Design HELP

(OP)
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.

CODE

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

CODE

'   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

CODE

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

CODE

=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

CODE

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

CODE

  '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








 

RE: Table Design HELP

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

RE: Table Design HELP

(OP)
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

CODE

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

RE: Table Design HELP

So you are going against my advice

Quote (dhookom):

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

RE: Table Design HELP

(OP)
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:

CODE

  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







 

RE: Table Design HELP

(OP)
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

RE: Table Design HELP

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

RE: Table Design HELP

(OP)
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

RE: Table Design HELP

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

RE: Table Design HELP

(OP)
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

CODE

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

RE: Table Design HELP

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

RE: Table Design HELP

(OP)

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:

CODE

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

CODE

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


 

RE: Table Design HELP

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

RE: Table Design HELP

(OP)

Hi

CODE

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.

CODE

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

CODE

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.

CODE

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
 

RE: Table Design HELP

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

RE: Table Design HELP

(OP)

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.

CODE

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

RE: Table Design HELP

Your stated your code is:

CODE

strSQL = "Update tblOccupancyBuildNewTemp
Clearly this is and update query. An append record to add new records would begin with

CODE

strSQL = "INSERT INTO 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

RE: Table Design HELP

(OP)
Hi

CODE

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:

CODE

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:

CODE

=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:

CODE

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
 

RE: Table Design HELP

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

RE: Table Design HELP

(OP)

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

RE: Table Design HELP

(OP)

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

RE: Table Design HELP

(OP)
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

RE: Table Design HELP

(OP)

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

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

RE: Table Design HELP

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

CODE

        For datThis = Me.txtStartDate To Me.txtEndDate
to

CODE

        For datThis = Me.txtStartDate To Me.txtEndDate Step 7
Check Allen Browne's International Dates.

Duane
Hook'D on Access
MS Access MVP

RE: Table Design HELP

(OP)
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:

CODE

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  

CODE

db.execute strSQL, dbFailonError

Mikie

 

RE: Table Design HELP

(OP)

Thanks for your help PHV, the forms works perfectly

RE: Table Design HELP

(OP)

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

.... now thats difficult

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close