Merge two queries in one, GROUP BY and SUM one attribute

Question

I have a database application that manages projects and its ressources (= the employees). I have an overview that displays all employees, the projects they are assigned to and how many hours they are assigned to that project.

The overview consists of two queries (recordsets) that are put together in a loop in the VBA-code.

1. query, rstEmployees:

SELECT employeeID, employeeName, SUM(janPlan), First(janRest), ... , SUM(decPlan), FIRST(decRest) 
FROM tblRessources 
INNER JOIN tblEmployees ON employeeID = employeeID
GROUP BY employeeID, employeeName

It loops for each employee. In every loop all the correspoding projects of the employee are read and written in a temporary table. I use the following query:

2. query, rstRessources

SELECT projectName, janPlan, janRest, ... , decPlan, decRest
FROM tblRessources
INNER JOIN tblProjects ON projectID = projectID
WHERE employeeID = rstEmployees(employeeID)

• Plan: Number of hours assigned to the project
• Rest: Number of free hours

My desired result is a continuous form that looks like this:

This procedure takes too long (up to 30 seconds) and I would like to do this only with SQL queries or a pivot table. I want to get rid of the loop because that takes too much time. Is that even possible only with SQL? Do you have any ideas how to accomplish that?

Changing the underlying tables is a possibility if it helps in any way. Also I know the tables violate atomicity and all the good normalization stuff but it works for my current solution.

Thanks for the help!

Answer 1

iBener gave me the deciding clues to solve my problem. I translated his MYSQL-query into access-SQL and this one works:

SELECT employeeID, 
SWITCH(typ = 0, 'typ 0', typ = 1, 'typ 1') AS typSwitch, 
SWITCH(typ = 0, employeeName, typ = 1, projektName) AS employeeProjekt, rankName,
SumJanPlan, FJanRest

FROM( 
SELECT 0 AS typ, ma.employeeName, NULL AS projektName, ma.dKX, 
  ma.employeeName, ma.employeeName AS rankName,
  SUM(janPlan) AS SumJanPlan, FIRST(janRest) AS fJanRest
FROM tblEmployees AS ma LEFT JOIN tblRessourcs AS res ON ma.dKX = res.dKX
WHERE res.jahr = 2015
GROUP BY ma.employeeName, ma.dKX

UNION ALL

SELECT 1 AS typ, NULL AS employeeName, pro.projektName, res.dKX, 
  ma.employeeName AS rankName, SUM(janPlan) AS SumJanPlan, FIRST(janRest) AS fJanRest
FROM tblProjekte AS pro 
  INNER JOIN (tblEmployees AS ma 
  INNER JOIN tblRessources AS res ON ma.employeeID = res.employeeID) ON pro.projektNr = res.projektNr

WHERE res.[jahr]=2015 AND res.employeeID IN (
  SELECT DISTINCT employeeID FROM tblRessources
  WHERE jahr = 2015) 
GROUP BY pro.projektName, ma.employeeName, res.dkx)

ORDER BY rankName, typ

Note

• I had to add an additional nameRank column so that all the employees are shown in alphabetical order. Otherwise the order is detemined by employeeID.
• level is a reserved word in Access-SQL so I used typ
• Instead of CASE WHEN you use SWITCH( IF, THEN, ESLE IF, THEN, ...)

Cheers!