Time Sheet Pairs

[ShredDude]

Pulling my hair out here. I'm guessing it's something simple. Fresh eyes most welcome.

I"m attempting to facilitate data entry in a manner that is aligned with an existing paper form's layout. Of course, it was done on a spreadsheet.

It's a basic time entry situation, for an employee's weekly time sheet. The twist is that on any given day, the employee can clock in an out several times, resulting in as many as 6 "time pairs" I'm calling them. (Maybe this is the root of my problem?)

In my efforts to normalize table layouts, I've create a table that contains:

tblTimePairs:
ID (PK)
EmpID (FK to tblEmployees)
dtWork
IndexTimePair (will be a number from 1-6)
tmIn
tmOut

My logic being that I didn't want to take up space for 12 fields representing the potential 6 TimeIn/TimeOut pairs that could occur on a given day, when most often its one or two pairs.

So, I'm having difficulties creating a bound form to facilitate entering times on one row for each date in a pay period.

Say Pay Period is from the 1st to the 15th of the month. I'd like to put up a form with 15 rows, one for each date, with a column for the date and 12 columns for the potential time pairs, so as to mimic the paper form from which this hand written data is to be transcribed. And then have the user be able to tab around on that form and enter the data where needed, and have this bound so I don't have to write a bunch of After Update code.

I made a different table that has all 12 fields for the possible time fields and that made making the bound form much easier, but then I ran into the issue of not having records for each date within the pay period. After creating a query that generated a record for each date in the period, I was left with an un-updateable recordset in the form. Nice for a report, but not my editing data entry needs.

I'm sure this must be doable in Access where you can present the user a "spreadsheet" like form that they can update? Trying several approaches including crosstab queries, and pivot tables, I always hit a wall with not being to update the resulting data.

I've read a little about disconnected ADO recordsets, and have used those before in Excel applications getting data from other data sources. Is that a path I should take? Or should I structure my DB differently? Users are stuck on being able to enter data in the "spreadsheet" like manner. And given the hand written legacy form from which they need to transcribe the data this horizontal one row per day layout would be nice to accomodate for them.

In fact, I've done this in Excel, and then format the results into a chunk of data that I process back into the tblTimePairs Access table mentioned earlier, with an ADO recordset. I'm trying to now to see if I can do it all within Access. I'm hopeful that I can find a way to get away from using the Excel "form", so that i can move this to an .accdr solution. I'm stuck now with the scenario of needing to include an additional .xltm file to facilitate this data entry, and dealing with the automation code between Access and Excel. I do all of this now as an Excel Add In linking to an Access back end, so that solution also has multiple files to keep track of, and inter application automation to handle. It would be nice to get the whole thing contained in an .accdr for example.

Any thoughts most appreciated.

Thanks,

Shred.

 

[MarkK]

I would create a table that has a Date/Time field and a 'direction' field that can only equal 1 or -1.

tClockEvent
ClockEventID
EmployeeID
DateTime
Direction

I think if you play with that a bit you'll see that it simplifies a lot of things.
Lemme know,
Mark

 

[ShredDude]

Thank you for the reply.

I've played with your idea, I see the merit in storing just the date/time in one field. I can always use DATEVALUE and TIMEVALUE to extract what I need later.

However, I'm still not seeing how to give myself the ability to input data in one horizontal row for multiple clock entries on one day.

I populated your table with four records, all on the same day, two Ins, and two Outs.

Queries I generate return four records. Aggregate queries I made add the TIMEVALUES together rendering them useless for my task, and are then also not updateable in a form.

Still not accomplishing my goal yet however. How can I get 13 columns going across a form

Date TimeIn TimeOut TimeIn TimeOut...etc.

with 15 rows, including dates I don't have any entries for yet, and then be able to update any control on that form? Am I stuck in a spreadsheet mentality in a DB world when trying to do such a thing in Access?

What am I missing?

Thanks again, and any other ideas most appreciated.

 

[gemma-the-husky]

there are a number of ways to do this.

I think you could either have a table with 2 columns - in and out, or a table with 1 column event time, and an event type. I am not sure which is the best. Possibly the first, but it depends how you want to use the data.

You DEFINITELY do not want to allow for multiple time entries on a single row, and you don't want/need an index number for the clock event. The date/time is sufficient.

Now, I think a lot of control needs to come from the mechanism you use to record data. When you enter a time, you need to check the last entry, and not allow the next entry to be "wrong"

So if the last entry was a "clock-in", you cannot enter another clock-in for that empoloyee. You should also check for excessive time gaps between the 2 events, and that the times don't overlap incorrectly. ie a clock-out must nbe timeds after the current clock-in. etc!

If it is easier, you could arrange for the times to be entered in a spreadsheet, and then import the spreadsheet.

 

[ShredDude]

Gemma:

Thanks for the reply. I"m starting to believe I can't accomplish the data entry method I'm seeking within Access.

After many hours attempting to achieve the desired data entry behavior in Access, it looks like I"m back to including an .xltm file with the solution. The business process at hand is one where at week's end, hand written timesheets for multiple employees need to be entered into the system by one person. For expediency, it is preferable to mimic the handwritten form's layout which is one row with multiple columns for in/out time pairs.

I facilitate this presently in an Excel spreadsheet. I have a class that hooks that sheet and monitors the _change events within the entry range to auto-format what was entered (eg: 800 becomes 8:00 AM) based on contents of adjacent cells while also performing some validations as you hinted at it.

Hidden from the user is a tidy validated range that consolidates the data into a block that is processed back into an Access DB with UPDATE queries when the sheet is exited.

I was hoping for a method of facilitating the data entry that would eliminate the need for Excel. I've transitioned all the other data entry requirements to Access forms, eliminating much hoop jumping as compared to how I had facilitated it within Excel. Bound forms make things much easier! Originally I had established this solution as an Excel Add In, hooking an accdb file for all data storage and retrieval. Now trying to originate from Access, and was hoping to not have to hook into any external files for data entry.

Oh well, a good learning exercise for me. I don't think I can convince them to change the form upon which employees manually enter their hours to be one row per time entry just to facilitate data entry.

Thanks for the insights. Much appreciated!

 

[spikepl]

maybe this is a stupid question: " The business process at hand is one where at week's end, hand written timesheets for multiple employees need to be entered into the system by one person." And this process is immutable? So that each employee could enter own times directly and the paper could be skipped?

 

[ShredDude]

Unfortunately that is not going to change. Limited computer resources, and skill sets. A Supervisor is responsible for gathering the paper forms and entering into their laptop. Non-Supervisors have no access to computers.

 

[gemma-the-husky]

I would say you really do not want the time sheet input form in access to mimic the written form - by having multiple in/out columns. A single entry for in and out times is what you want.

As I say - you have a couple of options.

you could enter all the time sheet data into a spreadsheet, and import the spreadsheet
or you can design an input routine that enables inputting each matching pair of date/times for an employee

either way you need some validation checks that the data is entered correctly

the key to it all is that
a) any entry MUST consist of an in and out
b) the in time must be after the previous out time
c) the out time must be after the current in time.
d) it will need to be slightly more complex than that if you are filling in gaps in a sequence.

In terms of presentation, I think I would have a form per employee, with a subform showing the clockin/clock out times.

ANything you can do in excel, you can do easier in access, in my opinion - precisely because access imposes a rigour on you by not allowing "random" data in a given cell.

ie - in excel a column that is supposed to consist of a date or time, can actually accept any data - but in access, it can't.

for what its worth, handling times is always a bit tricky. I tend to force times to be entered as 4 digit text strings values (ie 24 hour clock) in access - length must be 4 chars, entry must be numeric, with the first two digits in the range 00 to 23, and the last two in the rage 00-59.

 

[MarkK]

bound form to facilitate entering times on one row

According to the basic norms of database design, one record in one table should contain the dimensions of a single instance of an object. If you record both a coming AND a going in the same record I believe you will have stored two objects, and when it comes time to report on your data you'll regret it. A clock in and a clock out are the same type of object that differ only by the value of a single dimension: in, or out.
My 2c is that that should inform your table design.

Now if you want to allow entry of that data in a format more intuitive to someone trained in a previously existing system, don't use a bound form. And as far as allowing data entry and parsing it into a table, it seems to me your possibilities are unlimited.
1) Give a textbox for the date, a combo for the employee and then get your user to enter times, all in the same textbox delimited by a space. Then parse those out.
2) Create a temp table with a Date field, a Time field, and In/Out field. Link it to a main form with the employee selected in a combo.
3) Put a series of text boxes on a form in a grid, however you want them, and collect the information you need. Parse it out, error check it, and stuff it into a table.

If you want to normalize your data but have a UI that doesn't look like your normalized table, then don't use a bound form.

Cheers,

 

[ShredDude]

LagBolt:

Thanks for the 2 cents. Much appreciated.

At the risk of offending the DB design gods, I've decided to keep the table layout I had as it facilitates creating the UI in Excel that is needed.

tblTimePairs
ID (PK)
EmpID (FK)
dtWork
IndexTimePair (value 1-6)
tmIn
tmOut

By storing the IndexTimePair I'm able to then easily create a pivot table in XL from which I can extract the elements needed for the dataentry UI.

The XL UI then facilitates the required business process of transcribing from a legacy paper form. The inputted data is then packaged up and sent back to the tblTimePairs table in Access.

I like your idea of not storing as many fields, but I get hung up on how to then easily use that data source. Without a reference as to whether a timeentry refers to a specific pair, I'd have to write a routine to determine which pair it belongs to right?

For example, with four records in your table design consisting of two Ins, two Outs on the same date, how do I then quickly "slot" the timepairs? That's where I lose it.

Preferred output for example of four records above would consist of two rows like:

Date TimePair TimeIn TimeOut

at a minimum, that I could easily use to slot the UI which is laid out like:

Date TimeIn1 Timeout1 TimeIn2 Timeout2 ...

...to facilitate the UI requirement.

Trying to create big unbound form in Access seems like a lot more work than using an XL sheet as nice container that I can validate on.

thanks for the input. I'm just trying to get my head around thinking within the domain of a database world. I've spent most of my time using spreadsheets, and I'm trying to expand my skills.

 

[MarkK]

DB design Gods don't get offended, they laugh an evil laugh, like Mwah-ha-ha-ha. They notice that people come here and ask good questions, receive good anwers, and then ignore them. Mwah-ha-ha-ha.
Your time will be sucked into the vortex of trying to work with data that is not normalized, and the vortex of trying to validate input in MS Excel...
Mwah-ha-ha.
Mark

 

[ShredDude]

Lagbolt:

Thanks for the encouragement. After creating a class to manage the XL linking etc. I find myself still coming back to trying to do it in Access in a way that will minimize the change of process to the users.

I can handle the data validation required, and making the keystrokes as easy as possible for the user, eg: not having to type in "8:00 AM" but type in "800" and have it appear as "8:00" and figure out AM or PM based another previously entered value when appropriate. 24 Hour clock definitely won't fly with these users.

I want to learn how to do it all in Access. My Brain is stuck on the horizontal layout of a row for day with multiple columns (when needed for >1 TimeIn/TimeOut pair), as that is their current way of "looking" at it.

I can report the data just fine from the normalized layout. I'm liking the power I can leverage out of Access for that. My hang up is on the data entry side, trying to appease an existing manual process.

I've tried a form that would present a row for a date with a TimeIn and TimeOut field. That's fine. Then if there happens to be another In/Out on that day, I have to have them go down to another record(row) and do it again. This visual conversion to Vertical from Horizontal is hanging me up on presenting what I believe will be an acceptable data entry process. Any ideas on how to approach the Data Entry task in a manner that gives the user the opportunity to enter multiple Time pairs, on what will seem to them to be the same row?

I've noodled on the idea of adjacent subforms that could appear based on an valid entry in the previous subform.

eg: sfrm1 enter a date timein timeout...that causes sfrm2 to appear adjacent to sfrm1 just to the right of it, with just TimeIn and Timeout columns visible, although it represents a bound record to the same date as in sfrm1...do validation on TimeIn/TimeOut entered to make sure it's after the Times entered in sfrm1, etc.

OR...

Create a form with 6 subforms (one for each potential timein/timeout pair), aligned across the form all bound to a query that generates a record for each date in the pay period. So for example each of the 6 subforms would apear with 15-16 rows in it; the first subform would have 3 Fields/Columns visible with all the dates filled in. The remaining 5 would only show two Fields, but would have 3.

The example above assumes a table with TimeIn and TimeOUt as separte fields I guess.

To use your sleeker table, I"d end up with 12 subforms each tied to a query of Date, Time, Direction, the first sfrm showing 2 columns, data and Time, the others only showing one column, time. Lots of Event code to handle the changes....

Then through a potentially onerous validation routine package up all the entries when user is done, and do the appropriate update queries to tblColockTimes.

Sounds like a pain, but would facilitate input across the screen in a horizontal manner for each date, while honoring the normalized data structure underneath.

I get ideas like that in my head and then think there has to be a better way.

I get the power of the normalized data structure. Thats why I'm here, trying to go deeper. I like your sleek table design with the "Direction" flag for recording In's and Out's, and have used it in testing various queries to facilitate reporting after the data is already entered in that format. (I populated a few hundred records after laying it out in a compatible Excel format) The ability to use that sleek table for reporting works great. It's how to do data enry against that underlying structure that I'm struggling with.

If the challenge is to align the data entry process as closely as possible to an existing hand written paper form, that has 13 columns across (Date + 6(TimeIn/TimeOut) pairs, how would you go about it?

I'm trying to learn, and i appreciate all the feedback...spooky laughs included.

Shred

 

[gemma-the-husky]

i struggle to see why you need to present the data horizontally

why is it harder to conceive of an entry form that puts the clock details in vertically. that's what the clock card looks like, isn't it.

if you go across the screen you will run out of horizontal space anyway, perhaps.


You could "force" access to present the data horizontally, but it hardly seems worth the effort to me.

I think if i wanted to do that, I would probably use a temporary table to do the original input, and then move thre data from the temp table, to the final table.

with regard to times, I already said the easiest way is to force users to enter 4 digit 24 hour clock times.

 

[ShredDude]

Gemma:

Thanks for the reply and feedback. The Horizontal "requirement" for the times comes from my attempt to match their existing legacy form, which of course they've created in Excel. This printed hard copy form is in use by several hundred employees. Would like to avoid the introduction of a new form...change is scary. Kind of like having someone here in America use 24 hour times while sitting at one of those computer thingys.

Imagine sitting down with a stack of 50 of these handwritten forms and needing to enter the data into this application. I was just trying to present the data entry user with a screen that mimics the original form as closely as possible for expediency purposes. I feel like I'm trying to put a square peg into a round hole.

Can you point me to an example of using the "Temp Table". That sounds like the path I had already done when using Excel as the input template and having an ADODB recordset behind the scenes that was getting updated.

So maybe, construct a table with 13 Fields (dtWork, TimeIn1, timeOut1, TimeIn2, TimeOut2, etc...), populate it with however many records are in the pay period, use the contents of the table as the data source for a datasheet form, let the user fill in the datasheet, save to the temp table. Then in VBA open recrodset with entire table, process recordset accordingly extracting fields for UPDATE or INSERT INTO (as the case may be) queries into the normalized table. Then kill al the records in the temp table, or the temp table itself. something along those lines? Or is there an easier way?

Again, that gets me far away from leveraging a bound form. Square peg, round hole?

 

[Brianwarnock]

I am not on very often these days and as I read through this my thoughts went to another thread in the same forum where PBaldy states that he has used a temporary table to allow for non-normalised input which he then adds to a normalised Table, it was a blow to see that Dave (aka Gemma) got there first.

Just as well as I might have been asked how to do it.

Brian

 

[MarkK]

Wizard Idea

Here's an example of what I've done to give users a non-normalized way to enter data. This form lets a user split a quantity of material allocated to one job section and re-allocate it to four or fewer other job sections...

- Note the grid of unbound controls.
This all gets parsed and validated and so on, and then plugged back into normalized data structures.
Mwah-ha-ha.
Cheers,

 

[MarkK]

And here's that little wizard dude if you want him...

 

[ShredDude]

Thanks again everyone. I did receive inspiration from the other thread about entering non normalized data with a temporary table.

So, after teaching myself how to create a temporary table and a few other things, I think I'm about there. I hope! I now have a form that gets populated in a manner that replicates the legacy paper form, that is bound to the temp table.

I used Text format for all the Time Fileds in the Temp Table, and then used a slightly modified version of my change event code I had originally written to manage this in Excel. This allows the user to input times quickly, with some underlying logic as to whether it should be AM or PM based on adjacent entries, and some other validation logic.

Time for a break, but I'm hopeful I can finish up the step of putting the data from this form back into table by leveraging the code I had already done for Excel.

Here's an example of how I get the data sorted out the way I want it for the Data Entry form. Any comments most welcome...:

Public Function GetTimeSheetData(Optional lEmpID As Long, Optional dtFrom As Date, Optional dtTo As Date) As Boolean
'---------------------------------------------------------------------------------------
' Procedure : GetTimeSheetData
' Author    : Shred Dude
' Date      : September 28, 2011 - 09:17
' Purpose   : Retireve Time Pair Data from tblTimePairs
'               reformat into recordset for TimeSheet Entry Form
'               One record for each date in the date range
'               regardless of whether any entries presently exist in the Database
'---------------------------------------------------------------------------------------
'
Const sPROCEDURE As String = "GetTimeSheetData"

Dim rsSource As Recordset
Dim rsTimeSheet As Recordset

Dim sWhere As String
Dim bResult As Boolean
Dim sSQL As String

On Error GoTo errHandler
'Assume Success
bResult = True



'### Testing for Optional inputs
'   will make these parameters required after testing

If Not dtFrom = 0 And Not dtTo = 0 Then
    'Date Range was provided so create WHERE Clause
    sWhere = " WHERE (dtwork BETWEEN #" & dtFrom & "# AND #" & dtTo & "#) "
End If

If lEmpID > 0 Then
    If Len(sWhere) > 0 Then
        sWhere = sWhere & " AND "
        sWhere = sWhere & "(EmpID = " & lEmpID & ")"
    Else
        sWhere = " WHERE EmpID = " & lEmpID
    End If
End If

'###

sSQL = "Select * FROM tblTimePairs" & sWhere

'Set rsSource = CurrentDb.OpenRecordset("SELECT * FROM tblTimePairs")
Set rsSource = CurrentDb.OpenRecordset(sSQL)

If Not rsSource.EOF Then
rsSource.MoveLast
rsSource.MoveFirst
End If

'Create the TimeSheet recordset whether records are returned or not
'will fill in with blank recordsa for all the dates in payperiod later
Set rsTimeSheet = CurrentDb.OpenRecordset("SELECT * from tmptblTimesheet")

If Not rsSource.EOF Then
    Do While Not rsSource.EOF
       
        With rsTimeSheet
            'See if this Date is already in the Recordset
            'If so Append the Time Pair Data to the Appropirate field
            .FindLast ("dtWork=#" & rsSource!dtWork & "#") 'using findlast since if it's there it should be at the end...maybe faster?
            If .NoMatch Then
                .AddNew
                !EmpID = rsSource!EmpID
                !dtWork = rsSource!dtWork
            Else
                'This date already in the recordset, so just add the Time pair
                 .Edit
            End If
            
            .Fields("TimeIn" & rsSource!indexTimePair) = Format(Nz(rsSource!dtTimeIn), "h:mm ampm") ' insert the Time as a string
            .Fields("TimeOut" & rsSource!indexTimePair) = Format(Nz(rsSource!dtTimeOut), "h:mm ampm")
            
            .Update             ' Adds it to the table since the recordset was opened from the table
        
        End With
    
        rsSource.MoveNext
    Loop
End If


'Now go through and add records for any dates not in there for the date range specified
'so they'll show up in the temp table and thus be editable on the Data Entry Form

'Add the EmpID supplied
Dim d As Integer
Dim dtFind As Date

For d = 0 To DateDiff("d", dtFrom, dtTo)
'    Debug.Print DateAdd("d", dtFrom, d)
    dtFind = DateAdd("d", dtFrom, d)
   
    With rsTimeSheet
        .FindFirst ("dtWork=#" & dtFind & "#")
        If .NoMatch Then
            'This date not in the recordset so add a row
            .AddNew
                !EmpID = lEmpID
                !dtWork = dtFind
            .Update
        End If
        .MoveFirst
    End With
    
Next d


errExit:
  'Release Objects
  
  rsSource.Close
  Set rsSource = Nothing
  rsTimeSheet.Close
  Set rsTimeSheet = Nothing
  
  
    GetTimeSheetData = bResult
  
  
    Exit Function

errHandler:
    'SET Last Paramter of bCentralErrorHandler appropriatly
    'TRUE for Entry Level Procedure
    'FALSE for Sub-Level Boolean Procedure
    bResult = False
    If bCentralErrorHandler(msMODULE, sPROCEDURE, , False) Then
        'if in Debug Mode go here...
        Stop
        Resume
    Else
        Resume errExit
    End If

End Function

Thanks again for putting up with my learning curve. I truly appreciate the feedback and insights. I've got a lot to learn as I wade into the world of Access.

Shred