Help needed to build my query!!!

[thierry.drouet]

Hi everybody,

Let's say that I've got the following tables:
- tblCustomers(IDCust,NameCust)
- tblEmployees(IDEmpl,NameEmpl)
- tblProjects(IDProj,IDCust,IDEmpl,Duration)

My query:
SELECT tblCustomers.NameCust, tblProjects.IDProj, tblProjects.Duration, tblEmployees.NameEmpl, Sum(tblProjects.Duration) AS SumOfDuration
FROM (tblProjects INNER JOIN tblEmployees ON tblProjects.IDEmpl = tblEmployees.IDEmpl) INNER JOIN tblCustomers ON tblProjects.IDCust = tblCustomers.IDCust
GROUP BY tblCustomers.NameCust, tblProjects.IDProj, tblProjects.Duration, tblEmployees.NameEmpl
ORDER BY tblCustomers.NameCust;

What I'd like get when running my query is:

NameCust ------ IDProj - Duration ----- NameEmpl ------- ?????????
CUST1 ---------- 1 ------ 3 ------------ EMPL3 ---------- 7
CUST1 ---------- 2 ----- 10 ------------ EMPL4 --------- 10
CUST1 ---------- 9 ------ 2 ------------ EMPL3 ---------- 7
CUST1 --------- 11 ------ 2 ------------ EMPL3 ---------- 7
CUST1 --------- 13 ------ 8 ------------ EMPL1 ---------- 9
CUST1 --------- 18 ------ 1 ------------ EMPL1 ---------- 9
CUST2 ---------- 3 ----- 20 ------------ EMPL1 --------- 20
CUST2 ---------- 4 ----- 12 ------------ EMPL3 --------- 17
CUST2 ---------- 5 ------ 3 ------------ EMPL2 ---------- 4
CUST2 --------- 10 ------ 5 ------------ EMPL3 --------- 17
CUST2 --------- 17 ------ 1 ------------ EMPL2 ---------- 4
CUST3 ---------- 6 ----- 25 ------------ EMPL4 --------- 27
CUST3 --------- 14 ------ 2 ------------ EMPL1 --------- 14
CUST3 --------- 15 ------ 2 ------------ EMPL4 --------- 27
CUST3 --------- 16 ----- 12 ------------ EMPL1 --------- 14
CUST3 --------- 19 ------ 2 ------------ EMPL2 ---------- 2

where ???????? (manually done) = the sum of the Employee duration for ONE Customer !!!

But what I get instead:

NameCust ------ IDProj - Duration ----- NameEmpl ------- SumOfDuration
CUST1 ---------- 1 ------ 3 ------------ EMPL3 ---------- 3
CUST1 ---------- 2 ----- 10 ------------ EMPL4 --------- 10
CUST1 ---------- 9 ------ 2 ------------ EMPL3 ---------- 2
CUST1 --------- 11 ------ 2 ------------ EMPL3 ---------- 2
CUST1 --------- 13 ------ 8 ------------ EMPL1 ---------- 8
CUST1 --------- 18 ------ 1 ------------ EMPL1 ---------- 1
CUST2 ---------- 3 ----- 20 ------------ EMPL1 --------- 20
CUST2 ---------- 4 ----- 12 ------------ EMPL3 --------- 12
CUST2 ---------- 5 ------ 3 ------------ EMPL2 ---------- 3
CUST2 --------- 10 ------ 5 ------------ EMPL3 ---------- 5
CUST2 --------- 17 ------ 1 ------------ EMPL2 ---------- 1
CUST3 ---------- 6 ----- 25 ------------ EMPL4 --------- 25
CUST3 --------- 14 ------ 2 ------------ EMPL1 ---------- 2
CUST3 --------- 15 ------ 2 ------------ EMPL4 ---------- 2
CUST3 --------- 16 ----- 12 ------------ EMPL1 --------- 12
CUST3 --------- 19 ------ 2 ------------ EMPL2 ---------- 2

Any suggestion to build this Query ??
Thanks in advance

 

[stepone]

You can do this in 2 stages (ie. with 2 queries).

The first query Groups By two fields - Customer ID and Employee ID - and Sums the Duration field. This will give you a total duration for each Customer/Employee table.

You then join the result of this query to the one you have below (joing by Employee ID and Customer ID), and instead of displaying

Sum(tblProjects.Duration) AS SumOfDuration

as your final field, you display the total duration from the first query.

Hope that helps,
StepOne

 

[stepone]

In other words, create a qry called qryTotalDuration ;

SELECT tblProjects.IDCust, tblProjects.IDEmpl, Sum(tblProjects.Duration) AS SumOfDuration
FROM tblProjects
GROUP BY tblProjects.IDCust, tblProjects.IDEmpl;

Then your query is changed slightly to ;

SELECT tblCustomers.NameCust, tblProjects.IDproj, tblProjects.Duration, tblEmployees.NameEmpl, Sum(qryTotalDuration.SumOfDuration) AS SumOfSumOfDuration
FROM ((tblProjects INNER JOIN tblEmployees ON tblProjects.IDEmpl = tblEmployees.IDEmpl) INNER JOIN tblCustomers ON tblProjects.IDCust = tblCustomers.IDCust) INNER JOIN qryTotalDuration ON (tblProjects.IDEmpl = qryTotalDuration.IDEmpl) AND (tblProjects.IDCust = qryTotalDuration.IDCust)
GROUP BY tblCustomers.NameCust, tblProjects.IDproj, tblProjects.Duration, tblEmployees.NameEmpl
ORDER BY tblCustomers.NameCust;

StepOne

 

[thierry.drouet]

In other words, create a qry called qryTotalDuration ;

SELECT tblProjects.IDCust, tblProjects.IDEmpl, Sum(tblProjects.Duration) AS SumOfDuration
FROM tblProjects
GROUP BY tblProjects.IDCust, tblProjects.IDEmpl;

Then your query is changed slightly to ;

SELECT tblCustomers.NameCust, tblProjects.IDproj, tblProjects.Duration, tblEmployees.NameEmpl, Sum(qryTotalDuration.SumOfDuration) AS SumOfSumOfDuration
FROM ((tblProjects INNER JOIN tblEmployees ON tblProjects.IDEmpl = tblEmployees.IDEmpl) INNER JOIN tblCustomers ON tblProjects.IDCust = tblCustomers.IDCust) INNER JOIN qryTotalDuration ON (tblProjects.IDEmpl = qryTotalDuration.IDEmpl) AND (tblProjects.IDCust = qryTotalDuration.IDCust)
GROUP BY tblCustomers.NameCust, tblProjects.IDproj, tblProjects.Duration, tblEmployees.NameEmpl
ORDER BY tblCustomers.NameCust;

StepOne

StepOne
It works perfectly !!
Thanks a lot and have a good day