Solved: Table Design / SQL Design Advice

[Shred Dude]

I'm trying to utilize DB's more in my little projects. I've often read that it's best to not store a value that can be calculated from the other data being stored. Taking that for gospel, I've designed a simple table that houses Time sheet data

EmpID, dtWorked, TimeIn1, TimeOut1, TimeIn2, TimeOut2, etc. there is an actual need for up to 6 Time In/Out pairs in this case. I made EmpID and dtWork dual Primary Keys, so there's only one record for an employee on any given day.

I was building a query with the intention of returning from this table the total time worked for a given employee between two dates. Using the Access Query Designer, I got to this point with the SQL looking at just the first two Time In/Out pairs...it would get much uglier for all 6 pair.

[VBA]PARAMETERS lEmpID Long, dtFrom DateTime, dtTo DateTime;
SELECT T.EmpID, T.dtWork, T.TimeIn1, T.TimeOut1, T.TimeIn2, T.TimeOut2, (DateDiff("n",[TimeIn1],[TimeOut1]))+IIf(Not (IsNull([TimeIn2]) Or IsNull([TimeOut2])),IIf(nz([TimeOut2])>0,DateDiff("n",nz([TimeIn2]),nz([TimeOut2])),DateDiff("n",nz([TimeIn2]),DateAdd("d",1,nz([TimeOut2])))),0) AS TotalMinutes, Format(TimeSerial([TotalMinutes]\60,[TotalMinutes] Mod 60,0),"Short Time") AS TotalHours
FROM tblDailyTimeSheets AS T
WHERE (((T.EmpID)=[lempid]) AND ((T.dtWork) Between [dtfrom] And [dtto]) AND (Not (T.TimeIn1) Is Null))
ORDER BY T.dtWork;
[/VBA]

It works, returning the cumulative time worked for each day , but I'm guessing there must be a better more efficient way to do not just that, but to then Group it for the aggregate sum?

It would be easy enough for me to add a column to the table to store the "Total Hours" when a record is added to the table, which would make this much simpler to do with an Aggregate query I'm guessing. I didn't get there without such a column so far.

Any perspectives on how others would approach this query and/or table layout would be most welcome.

Thanks,

Shred

[Shred Dude]

I"ve moved the SQL from the Access Query to a VBA function that returns the resulting recordset to a worksheet for testing. This works as a SQL call using adCmdText, but not as when trying to use the command object against a storedprocdure. (Is that not for Access, but rather SQL Server?)

I'd like to just pull the resulting aggregate value of total minutes from the tbl design as described in earlier post. With that number I could do the math needed for my other purposes.

Any Ideas?

If not, I think I'll just create a template sheet and use excel formulas to calculate the result for me after dumping the recordset with just the time pairs. That would eliminate all the IIF's in the SQL. SQL would get much simpler.

This is part of a project where Excel is the front end to an Access DB, so it's gotta go there at some point anyway. I just like to learn new ways of doing things more efficiently.

Thanks for any insights,

Shred

[VBA]Public Function getTotalWorkedTime(lempID As Long, dtFrom As Date, dtTo As Date) As Boolean
'---------------------------------------------------------------------------------------
' Procedure : getTotalWorkedTime
' Author : Shred Dude
' Date : August 16, 2011 - 19:47
' Purpose : Pull recordset for total time worked b/t two dates for a given empID
'---------------------------------------------------------------------------------------
'
Const sPROCEDURE As String = "getTotalWorkedTime"

Dim bResult As Boolean
Dim sSQL As String

Dim rsData As ADOdb.Recordset
Dim cmAccess As ADOdb.Command
Dim objParams As ADOdb.Parameters

Dim lAffected As Long

On Error GoTo errHandler

'Modified SQL from qryEmployeeTimeBetweenTwoDates
sSQL = "PARAMETERS lEmpID Long, dtFrom DateTime, dtTo DateTime; " & _
"SELECT T.EmpID, T.dtWork, T.TimeIn1, T.TimeOut1, T.TimeIn2, T.TimeOut2, " & _
"(DateDiff(""n"", [TimeIn1], [TimeOut1])) + IIf(Not (IsNull([TimeIn2]) Or IsNull([TimeOut2])), IIf([TimeOut2] > 0, DateDiff(""n"", [TimeIn2], [TimeOut2]), DateDiff(""n"", [TimeIn2], DateAdd(""d"", 1, [TimeOut2]))), 0) AS TotalMinutes, " & _
"[TotalMinutes]/60 AS TotalHours " & _
"FROM tblDailyTimeSheets AS T " & _
"WHERE (((T.EmpID)=[lempid]) AND ((T.dtWork) Between [dtfrom] And [dtto]) AND (Not (T.TimeIn1) Is Null)) " & _
"ORDER BY T.dtWork;"

'Confirm connection...
If Not mcnConnect.State = adStateOpen Then mcnConnect.Open

' Create the Command object.
Set cmAccess = New ADOdb.Command

With cmAccess
.ActiveConnection = mcnConnect
.CommandText = sSQL
.CommandType = adCmdText

' ''Not Working with Access Database...
' .CommandText = "qryEmployeeTimeBetweenDates"
' .CommandType = adCmdStoredProc

' Create and append the parameters.
Set objParams = .Parameters

objParams.Append .CreateParameter("EmpID", adInteger, adParamInput, 0, lempID)
objParams.Append .CreateParameter("dtFrom", adDate, adParamInput, 10, dtFrom)
objParams.Append .CreateParameter("dtTo", adDate, adParamInput, 10, dtTo)

'Execute the Query
Set rsData = .Execute(lAffected)

End With

'Dump data to a worksheet for testing...
If gwkbUI Is Nothing Then HookUI

With gwkbUI.Sheets(sSheetTabName(gwkbUI, "wksTest"))
.Activate
With .[ptrTitle]
.CurrentRegion.ClearContents
.CopyFromRecordset rsData
.Offset(0, 2).Resize(.CurrentRegion.Rows.Count, 4).NumberFormat = "[$-409]h:mm AM/PM;@"
.CurrentRegion.Columns.AutoFit
End With
End With


errExit:
getTotalWorkedTime = bResult

'Release Objects

DestroyADORecordset rsData
Set objParams = Nothing
Set cmAccess = Nothing

'Restore Application Settings
'XL_RestoreDefaults

Exit Function

errHandler:

bResult = False

If bCentralErrorHandler(msMODULE, sPROCEDURE, , False) Then
'if in Debug Mode go here...
Stop
Resume
Else
Resume errExit
End If

End Function
[/VBA]

[Bob Phillips]

[

I'm trying to utilize DB's more in my little projects. I've often read that it's best to not store a value that can be calculated from the other data being stored. Taking that for gospel, I've designed a simple table that houses Time sheet data

That is right. The reason is that the calculation is a business rule, business rules can change, so you need flexibility.

EmpID, dtWorked, TimeIn1, TimeOut1, TimeIn2, TimeOut2, etc. there is an actual need for up to 6 Time In/Out pairs in this case. I made EmpID and dtWork dual Primary Keys, so there's only one record for an employee on any given day.

You should allow for any in/out pairs and use a table design like so

Employee Id
Date
In_Out_Index
Time In
Time Out

I was building a query with the intention of returning from this table the total time worked for a given employee between two dates. Using the Access Query Designer, I got to this point with the SQL looking at just the first two Time In/Out pairs...it would get much uglier for all 6 pair.

[VBA]PARAMETERS lEmpID Long, dtFrom DateTime, dtTo DateTime;
SELECT T.EmpID, T.dtWork, T.TimeIn1, T.TimeOut1, T.TimeIn2, T.TimeOut2, (DateDiff("n",[TimeIn1],[TimeOut1]))+IIf(Not (IsNull([TimeIn2]) Or IsNull([TimeOut2])),IIf(nz([TimeOut2])>0,DateDiff("n",nz([TimeIn2]),nz([TimeOut2])),DateDiff("n",nz([TimeIn2]),DateAdd("d",1,nz([TimeOut2])))),0) AS TotalMinutes, Format(TimeSerial([TotalMinutes]\60,[TotalMinutes] Mod 60,0),"Short Time") AS TotalHours
FROM tblDailyTimeSheets AS T
WHERE (((T.EmpID)=[lempid]) AND ((T.dtWork) Between [dtfrom] And [dtto]) AND (Not (T.TimeIn1) Is Null))
ORDER BY T.dtWork;
[/VBA]

It works, returning the cumulative time worked for each day , but I'm guessing there must be a better more efficient way to do not just that, but to then Group it for the aggregate sum?

It would be easy enough for me to add a column to the table to store the "Total Hours" when a record is added to the table, which would make this much simpler to do with an Aggregate query I'm guessing. I didn't get there without such a column so far.

Any perspectives on how others would approach this query and/or table layout would be most welcome.

Much easier with the table as I lay it out.

[Shred Dude]

XLD:

Thank you for the insight, as always.

I think my original table design was too Excel spreadsheet layout oriented. I'm trying....

I'll give your suggestion a go.

So once I have:

EmpID, dtWork, iTimeInOut, dtTimeIn, dtTimeOut as fields...

I would still need to generate an aggregate query to sum off of an expression field I'd include in the query that calculates total time per iTimeInOut record, to generate the total time per employee per day, right?


For example, if my table has emp# 100 with two In/Out pairs yesterday as below:

100, 8/16/2011, 1, 9:00 AM, 5:00 PM
100, 8/16/2011, 2, 7:00PM, 10:00PM

I'd want to return

100, 8/16/2011, 11

(9a to 5p = 8 hours) + (7p to 10p = 3 hours) = 11 hours

After a couple of mis-steps I achieved this rather quickly with the following SQL:

[vba]PARAMETERS lEmpID Long, dtWorkDate DateTime;
SELECT tblTimePairs.empID, tblTimePairs.dtWork, Sum(DateDiff("n",[dtTimeIn],[dtTimeOut])/60) AS Hours
FROM tblTimePairs
GROUP BY tblTimePairs.empID, tblTimePairs.dtWork
HAVING (((tblTimePairs.empID)=[lEmpID]) AND ((tblTimePairs.dtWork)=[dtWorkDate]));[/vba]

I can appreciate the value of designing the table layout as you've suggested. Your way, if things change in the future and I need eight pairs instead of six, no problem. Much easier to include than having to redesign a DB already deployed to include TimeIn_8 as a field for example.

Thank you very much for taking the time to have a read and offer up yet another valuable insight for me. I truly appreciate it.

Best Regards,

Shred

[Bob Phillips]

In a pure sense, some might argue that you should only have one time, and an indicator as to whether it is In or Out. This is where I feel some reality checking is required, you have to remember that you want to report on this data. The times will always be in pairs, and it makes the SQL a lot simpler.

Good luck on the transition up to databases!

[Shred Dude]

I was a little too quick in thinking I had it. My Original goal was to aggreaget all time pairs between two dates. My earlier post was successful in doing that for a single date, but when I tried modifying it to include a From and To date parameter, I'm getting stuck on returning a record for each date. What I want is the total across the range of dates given.

Here's where I'm at...any pointers?

[vba]PARAMETERS lEmpID Long, dtFrom DateTime, dtTo DateTime;
SELECT TP.empID, Sum(DateDiff("n",[dtTimeIn],[dtTimeOut])/60) AS Hours
FROM tblTimePairs AS TP
GROUP BY TP.dtWork, TP.empID
HAVING (((TP.dtWork) Between [dtFrom] And [dtTo]) AND ((TP.empID)=[lEmpID]));
[/vba]

[Shred Dude]

A little reading enlightened me on including both a WHERE and a HAVING clause in the SQL. That got me to where I wanted to be.

[vba]PARAMETERS lEmpID Long, dtFrom DateTime, dtTo DateTime;
SELECT TP.empID, Sum(DateDiff("n",[dtTimeIn],[dtTimeOut])/60) AS Hours
FROM tblTimePairs AS TP
WHERE (((TP.dtWork) Between [dtFrom] And [dtTo]))
GROUP BY TP.empID
HAVING (((TP.empID)=[lEmpID]));[/vba]

Is this how you'd do it, with Access?

[Bob Phillips]

I was going to ask you about that HAVING. Having is used to filter aggregate items, not row items, so doesn't seem necessary here. I think

PARAMETERS lEmpID Long, dtFrom DateTime, dtTo DateTime; 
SELECT TP.empID, SUM(DateDiff("n",[dtTimeIn],[dtTimeOut])/60) AS Hours 
FROM tblTimePairs As TP 
WHERE (TP.dtWork BETWEEN [dtFrom] AND [dtTo]) AND (TP.empID=[lEmpID])
GROUP BY TP.empID ;

should be sufficient.

BTW, it is convention to use upper-case for keywords.

[Shred Dude]

That worked too.

I had gotten to the HAVING clause by using the Query Designer in Access. When I clicked on the Sigma button (SUM) it converted by SELECT query to an Aggregate query and automatically put in the HAVING clause based on which columns had been checked with GroupBy.

I see how the simpler version achieves the same result given the Expression as the Sum in it.

Thanks again!

[stanl]

Any perspectives on how others would approach this query and/or table layout would be most welcome.
Shred

Perhaps a little late in this thread but: most timeclock systems use use a card swiper, or Server/Intranet login from a PC. Time is usually stored as GMT then converted for timezone in another field. You would have your employee table, a 'punch' table, and a lookup table for punch type, viz: (assumes in-times are 100 series, out-times are 200 series

100=punch in for work
200=punch out for lunch
101=punch in from lunch
201=punch out for emergency/sickness/appointment
102=punch back in from [201]
etc..

It makes the SQL for calculating total time a little more complex but manageable. Just .02 Stan

[Shred Dude]

Stan:

Thanks for the perspective.

The project in front of me now however is much less sophisticated than one involving a time clock. Basically I"m facilitating the transcription of a paper based time sheet an employee fills out each week, and submits to a supervisor. The supervisor then needs to validate and aggregate to then submit up the chain for another person to "do payroll".

I like your concept if they were being required to capture any sort of reason codes with their in/outs. I"ll tuck that away for another time. These employees will never touch a computer, time clock, etc.

Thanks again for the input.

Shred