Solved: Table Design / SQL Design Advice

[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.