Sorting Problem Within Report

[thierry.drouet]

Hi everybody,

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

What I've achieved to do is a report which lists:
- the Customers (NameCust Header)
-- then,the Employees who had Projects for this Customer (NameEmp Header)
--- then, the Projects of this Employee, for this Customer (Detail)

My problem is that I can't manage to sort the EmployeeS,
based on the "Employee Total Duration" which is a calculated field (=Sum([duration])) in the NameEmp Header

The report Record Source is:
SELECT tblCustomers.NameCust, tblProjects.IDProj, tblProjects.Duration, tblEmployees.NameEmpl
FROM (tblProjects INNER JOIN tblEmployees ON tblProjects.IDEmpl = tblEmployees.IDEmpl) INNER JOIN tblCustomers ON tblProjects.IDCust = tblCustomers.IDCust
ORDER BY tblCustomers.NameCust;

Could someone help me?
I've enclosed "SortingProblemWithinReport.mdb" (converted in Access 2000 format) as an easy way to see what I'm talking about... and can be used to provide a solution
Thanks in advance
Thierry

 

[Rich]

You'll have to do the calculation in a query if you want to order by it in a Report

 

[thierry.drouet]

You'll have to do the calculation in a query if you want to order by it in a Report

Thanks for your quick reply Rich. It make sense. I've tried out by first modifying the query like this:

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;

But I'm not getting the expected results :-( I'm an Access newbie. Could tell me more how to build this query, how to perform this calculation? Actually I don't see how I can tell the Query Builder: sum up all the Duration for an Employe within the same Customer !?!?!
Thanks again
Thierry

 

[Rich]

Use a totals query, it's the sigma button on the query toolbar

 

[thierry.drouet]

Use a totals query, it's the sigma button on the query toolbar

That's what I've used to generate the above query: Sum(tblProjects.Duration) AS SumOfDuration, but I don't get the expected results !!! Any other suggestion?

 

[Rich]

What data type is duration?

 

[thierry.drouet]

What data type is duration?

Number type !!!
Is it a problem to look at the file I've enclosed? It's mdb I've done just to exposed that problem. I've thought it could an easy way !!!

 

[Rich]

Unless you post a 97ver of your db I can't look at it

 

[thierry.drouet]

Unless you post a 97ver of your db I can't look at it

That's fine Rich
Now the enclosed zip file contains the 97 version !!!
Thanks in advance

 

[apr pillai]

Change the Sorting and Grouping Order on your Report as well which I think you are not doing. Select the Employee Code after the SumofDuration in the Sorting Order on your Report.

 

[thierry.drouet]

Change the Sorting and Grouping Order on your Report as well which I think you are not doing. Select the Employee Code after the SumofDuration in the Sorting Order on your Report.

Thanks apr for your reply,
The first problem I think, is that the query using SumOfDuration doesn't provide the expected results:

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

I could probably manage to create the desired report if my query was returning these results:

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 !!!

Any suggestion to build this Query ??