0

I have the below table named 'timings'. It holds the comings and goings of staff members (morn_in, morn_out, afternoon_in, afternoon_out).

timings table What I would like to achieve is an output grouped by staff_ref, SUM of their hours worked, like so;

staff_ref|   total_worked|   
   U1            18:00

I currently use 4 separate queries to select each of the 'type' (morn_in, morn_out etc), the one for type='morn_in' is below, this query is called [101 am in] (it gets referenced later);

SELECT 
staff_ref
, time
, Format([date],"dd/mm/yyyy") AS t_date
FROM timings
WHERE (((type)='morn_in'));

I then SUM the DateDiff between the morning times;

SELECT 
[101 am in].staff_ref
, Sum(DateDiff("n",[101 am in].[time],[102 am out].[time])) AS morning_mins

FROM [101 am in] INNER JOIN [102 am out] 
 ON ([101 am in].staff_ref = [102 am out].staff_ref) 
 AND ([101 am in].date = [102 am out].date)
GROUP BY [101 am in].staff_ref;

And then SUM the datediff on the afternoon times (the same as above) separately in another query, then I add the result of those two queries together to get my total.

As you can see its very convoluted, and its not simple to explain.

I would like to know how I can combine the queries so I only need one statement to return the results if possible.

If I've explained it terribly then let me know and I'll make amendments.
Thanks

Edit 1 - 03/07/2018
I have used the code below to pull the data I need, since its a 'time' data type I had to calculate the total minutes then perform \60 for the hours and MOD 60 for the minutes as the results were over 24 hours. 

SELECT 
staff_ref, 
CLng(24*60*CDate(Sum(IIf([type]='morn_out',[time],0)-IIf([type]='morn_in',[time],0)+
IIf([type]='afternoon_out',[time],0)-IIf([type]='afternoon_in',[time],0)))) AS time_mins,
[time_mins]\60 & Format([time_mins] Mod 60,"\:00") AS convert_backHHMM

FROM timings

GROUP BY 
staff_ref, 
fix(time)
;

I would like to know how I would handle the possibility of the table having a "morning_out" time but NO "morning_in" time (or vice versa). [The front end of the system does protect against this, but I'm really curious and want to learn}.

Thanks for any assistance

the_cockerel
  • 17
  • 4
  • Something doesn't make sense here. I think your example data my be wrong. Assuming the total worked for "U1" should be 18 hours with the data provided. The totals I see are closer to about 16.9 hours. Also, what is your `[time]` column used for? – ccarpenter32 Jun 29 '18 at 16:14

1 Answers1

0

Try this:

SELECT 
    staff_ref,
    Sum(IIf([Type] = "morn_out", [date], 0) - IIf([Type] = "morn_in", [date], 0) +
        IIf([Type] = "afternoon_out", [date], 0) - IIf([Type] = "afternoon_in", [date], 0)) As TotalTime
FROM 
    [101 am in]
GROUP BY
    staff_ref,
    Fix([date]);

Apply a format to TotalTime of, say, h:nn

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • That's great, thank you. You gave me enough info to start looking into things and learning (really appreciate it). I have a further question, regarding any stray data in the table. i.e. if there is a "morning_out" time but no "morning_in", the calculation will be skewed (the system at the front end does validate this, so it won't occur - I'm more curious on how I would combat such an event). I have updated the original post with where I'm up to so far. Thanks – the_cockerel Jul 03 '18 at 09:27
  • Great. To take care of missing entries will take a lot more. You would probably need to loop the recordset in VBA to validate the entries and calculate with a default value where an entry is missing. – Gustav Jul 03 '18 at 09:41
  • Understood. Now VBA, I can do. I was just trying to see what I could do with the queries. Thanks again – the_cockerel Jul 03 '18 at 09:44