0

I would really appreciate help in achieving the correct output, I want to understand how it works and self teach.

I have one table:

tasks table

I would like to achieve the below results from one query:

user_name  |   task   |   CountOftask  |   SumOftime
    U1           1            1              00:15
    U2           1            1              00:25
    U2           2            1              00:05
    U3           2            1              00:20
    U3           3            2              00:40

re: the table 'tasks':

The time field is date/time hh:nn. The date field is irrelevant for the query at this stage - I want all historic data returned.

re: the query:

I would like to see a Count of task, SUM of time and Group by user_name & task, using one query with a sub select, instead of two separate queries.

Thanks for your time

Edit:

I can create this output, but I use two queries (Q1) to convert the time to integer, summing it. (Q2) using Q1 convert the integer back to time hh:mm.

Q1:

SELECT 
user_name
, task
, Count(task) AS CountOftask
, Sum(CLng(24*60*CDate([time]))) AS time_mins
FROM tasks
GROUP BY user_name, task;

Q2:

SELECT 
[301 SF count of task].user_name
, [301 SF count of task].task
, [301 SF count of task].CountOftask
, [time_mins]\60 & Format([time_mins] Mod 60,"\:00") AS Time_sum
FROM [301 SF count of task]
;

I would like to be able to do this in one query, any advice is appreciated.

the_cockerel
  • 17
  • 4
  • What have you tried so far? Please show what you've attempted so people can help you with that. Stack Overflow is not a code-writing service – alroc Jun 27 '18 at 10:25
  • Certainly, I didn't want the post to be too long. I will amend the original post. Thanks – the_cockerel Jun 27 '18 at 10:38

1 Answers1

0

Try with:

Select
    user_name,
    Max(task) As max_task,
    Count(*) As task_count,
    CDate(Sum([time])) As total_time
From
    YourTable
Group By    
    user_name,
    task

Edit - if time is text:

    Sum(CDate([time])) As total_time

or forced to date value:

    CDate(Sum(CDate([time]))) As total_time
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Thanks for the reply. Access doesn't like the CDate function on my field. Error: "Data type mismatch in criteria expression" – the_cockerel Jun 27 '18 at 11:03
  • You didn't tell, that _time_ is text. – Gustav Jun 27 '18 at 11:42
  • The time is stored as date/time data type with a format of hh:nn. I've just tried the forced date value you suggested above - **CDate(Sum(CDate([time]))) As total_time**. It works perfectly, thank you for your time here. Can I ask, why would the original **CDate(Sum[time])) AS total_time** as you suggested not work as the time field is NOT text? Thanks – the_cockerel Jun 27 '18 at 13:05
  • Well, if your _time_ was Date, the values would be right aligned. On your picture, however, they are left aligned, so I'm not convinced that _time_ is Date. – Gustav Jun 27 '18 at 13:29
  • 1
    Ah, you are correct. I created a dummy table (to hide my data) so I've been looking at the wrong table! Lesson learned. Thanks again – the_cockerel Jun 27 '18 at 13:51
  • OK. That explains. – Gustav Jun 27 '18 at 15:02