Concatenating multiple rows into single line in MS Access and calculating sums

Question

I am trying to calculate the hours for each task list to generate only one line with all of the information given. I have input an excel file into Access and generated the following required information.

Task List ------- Hours --- Progress --- Time Logged --- Billable Hours 
General Task ------10-----------0------------0------------------0----------
General Task -------8-----------0------------8-----------------20----------
General Task -------4----------100----------10------------------0----------
General Task -------0----------100----------20------------------0----------
Project Initiation -22----------25----------24------------------0----------
Project Initiation -12----------25----------12------------------0----------
Project Initiation -16----------25----------16------------------0----------
Project Initiation -4-----------25-----------8------------------0----------
Requirements -------16---------100-----------0------------------0----------
Requirements -------14----------50----------44-----------------14----------
Requirements --------5----------75----------32-----------------12----------
Requirements --------0-----------0-----------8------------------0----------
Design--------------240----------0-----------0------------------0----------
Design -------------120----------0-----------0------------------0----------
Design -------------120----------0-----------0------------------0----------
Prototype------------24----------0-----------0------------------0----------
Prototype -----------42----------0-----------0------------------0----------
Prototype -----------32----------0-----------0------------------0----------
Prototype -----------16----------0-----------0------------------0----------
Prototype -----------12----------0-----------0------------------0----------
Testing -------------16----------0-----------0------------------0----------
Testing -------------24----------0-----------0------------------0----------
Testing --------------8----------0-----------0------------------0----------
Testing --------------0----------0-----------0------------------0----------
Testing --------------0----------0-----------0------------------0----------

And I would like to come up with the final output looking like this!

Each of the task lists combined with the hours, time logged, and billing time summed up. Progress would be summed up and divided by the entries (ex requirements progress is (100+50+75+0)/4=56.25 progress total)

Task List --------- Hours --- Progress --- Time Logged --- Billable Hours 
General Task --------22---------50------------38---------------20---------- 
Project Initiation --54---------25------------60----------------0----------
Requirements --------35-------56.25-----------84---------------26----------
Design--------------480---------0--------------0----------------0----------
Prototype-----------126---------0--------------0----------------0----------
Testing -------------48---------0--------------0----------------0----------

I tried looking at Concatenating multiple rows into single line in MS Access and working off some of the code there, but was unable to make it work... This is where I started but was getting the error "the SELECT statement includes a reserved word or argument..."

Probably simultaneous editing, @HovercraftFullOfEels. I rolled it back — K.Dᴀᴠɪs, Jul 12 '19 at 19:52
Thanks, I was trying to reformat the info in my question as it looked correct until I posted and was very messy. Looks pretty good now, but could use new lines at requiremens, design, prototype and testing in the desired output. Thanks! — Flipflops, Jul 12 '19 at 19:54

score 1 · Answer 1 · answered Jul 12 '19 at 20:06

Concatenating multiple rows is not appropriate for this requirement. Use an aggregate query:

SELECT [Task List], Sum(Hours) AS SumHrs, Avg(Progress) AS AvgProg, 
      Sum([Time Logged]) AS SumTime, Sum([Billable Hours]) AS SumBill 
FROM table 
GROUP BY [Task List];

Could instead build a report with raw table as source and use report Sorting & Grouping features with aggregate calcs. Report allows display of detail records as well as summary data

Concatenating multiple rows into single line in MS Access and calculating sums

1 Answers1