Sorting by a calculated field

[Anne]

I have a report that looks at services provided to a clients. It records the
TimeSpent. I have a header for each client where I total the TimeSpent. The
details section has all the services listed to that particular client. How
to I sort my report on the calculated field =sum([TimeSpent]) which is in my
client header?

 

[KARL DEWEY]

Do the summing in the query feeding the report and then use the report
Sorting and Grouping.

 

[Anne]

I don't know how to add it to my query. I have tried a few ways but give me
error messages.
--
Anne

Do the summing in the query feeding the report and then use the report
Sorting and Grouping.
--
KARL DEWEY
Build a little - Test a little

I have a report that looks at services provided to a clients. It records the
TimeSpent. I have a header for each client where I total the TimeSpent. The
details section has all the services listed to that particular client. How
to I sort my report on the calculated field =sum([TimeSpent]) which is in my
client header?

 

[KARL DEWEY]

Use a totals query.
--
KARL DEWEY
Build a little - Test a little

I don't know how to add it to my query. I have tried a few ways but give me
error messages.
--
Anne

Do the summing in the query feeding the report and then use the report
Sorting and Grouping.
--
KARL DEWEY
Build a little - Test a little

I have a report that looks at services provided to a clients. It records the
TimeSpent. I have a header for each client where I total the TimeSpent. The
details section has all the services listed to that particular client. How
to I sort my report on the calculated field =sum([TimeSpent]) which is in my
client header?

 

[Anne]

I don't know how to do a "totals query"--
Anne

Use a totals query.
--
KARL DEWEY
Build a little - Test a little

I don't know how to add it to my query. I have tried a few ways but give me
error messages.
--
Anne

Do the summing in the query feeding the report and then use the report
Sorting and Grouping.
--
KARL DEWEY
Build a little - Test a little


:

I have a report that looks at services provided to a clients. It records the
TimeSpent. I have a header for each client where I total the TimeSpent. The
details section has all the services listed to that particular client. How
to I sort my report on the calculated field =sum([TimeSpent]) which is in my
client header?

 

[KARL DEWEY]

Create a query in design view. Click on the Greek symbol that looks like the
letter 'M' laying on it's side -- ∑
Select the fields you want by draging them into the grid. Below each field
name it will read GROUP BY. Change the one you want to total to Sum.

Just remember you can not have your cake and eat it too - example --
Name Start Amount
Joe 1/2/09 4.24
Joe 2/2/09 5.32

To sum the amount you have to drop the date field or change the GROUP BY to
something that will roll up the date - Min, Max, or Avg.
--
KARL DEWEY
Build a little - Test a little

I don't know how to do a "totals query"--
Anne

Use a totals query.
--
KARL DEWEY
Build a little - Test a little

I don't know how to add it to my query. I have tried a few ways but give me
error messages.
--
Anne


:

Do the summing in the query feeding the report and then use the report
Sorting and Grouping.
--
KARL DEWEY
Build a little - Test a little


:

I have a report that looks at services provided to a clients. It records the
TimeSpent. I have a header for each client where I total the TimeSpent. The
details section has all the services listed to that particular client. How
to I sort my report on the calculated field =sum([TimeSpent]) which is in my
client header?

 

[Anne]

I don't think I am explaining my problem properly. I will try again. I have
a functional query and my report runs fine. I have a client header, in the
details of each it shows the TimeSpent on each client visit. In my header I
have a field where I total the TimeSpent for each client. I want to sort the
way the clients are displayed by descending order of the Total Time Spent
which is calculated on the report.

I tried to add the sum as you discribed and my report no longer runs - it
asks me for TimeSpent.
--
Anne

Create a query in design view. Click on the Greek symbol that looks like the
letter 'M' laying on it's side -- ∑
Select the fields you want by draging them into the grid. Below each field
name it will read GROUP BY. Change the one you want to total to Sum.

Just remember you can not have your cake and eat it too - example --
Name Start Amount
Joe 1/2/09 4.24
Joe 2/2/09 5.32

To sum the amount you have to drop the date field or change the GROUP BY to
something that will roll up the date - Min, Max, or Avg.
--
KARL DEWEY
Build a little - Test a little

I don't know how to do a "totals query"--
Anne

Use a totals query.
--
KARL DEWEY
Build a little - Test a little


:

I don't know how to add it to my query. I have tried a few ways but give me
error messages.
--
Anne


:

Do the summing in the query feeding the report and then use the report
Sorting and Grouping.
--
KARL DEWEY
Build a little - Test a little


:

I have a report that looks at services provided to a clients. It records the
TimeSpent. I have a header for each client where I total the TimeSpent. The
details section has all the services listed to that particular client. How
to I sort my report on the calculated field =sum([TimeSpent]) which is in my
client header?

 

[KARL DEWEY]

Ok, do the totals query but only using fields Client and TimeSpent and save
as [TotalTimeSpent].

Open your 'functional query' in design view and save a copy. Open it again
and add the [TotalTimeSpent] query to the grid. Click on Client field of
the table and drag to the Client field of [TotalTimeSpent] query. Click on
the [TotalTimeSpent] [SumOfTimeSpent] field and drag to the Field row of the
grid. Save.

Use the [SumOfTimeSpent] field in the report header and in the report
Sorting and Grouping.

--
KARL DEWEY
Build a little - Test a little

I don't think I am explaining my problem properly. I will try again. I have
a functional query and my report runs fine. I have a client header, in the
details of each it shows the TimeSpent on each client visit. In my header I
have a field where I total the TimeSpent for each client. I want to sort the
way the clients are displayed by descending order of the Total Time Spent
which is calculated on the report.

I tried to add the sum as you discribed and my report no longer runs - it
asks me for TimeSpent.
--
Anne

Create a query in design view. Click on the Greek symbol that looks like the
letter 'M' laying on it's side -- ∑
Select the fields you want by draging them into the grid. Below each field
name it will read GROUP BY. Change the one you want to total to Sum.

Just remember you can not have your cake and eat it too - example --
Name Start Amount
Joe 1/2/09 4.24
Joe 2/2/09 5.32

To sum the amount you have to drop the date field or change the GROUP BY to
something that will roll up the date - Min, Max, or Avg.
--
KARL DEWEY
Build a little - Test a little

I don't know how to do a "totals query"--
Anne


:

Use a totals query.
--
KARL DEWEY
Build a little - Test a little


:

I don't know how to add it to my query. I have tried a few ways but give me
error messages.
--
Anne


:

Do the summing in the query feeding the report and then use the report
Sorting and Grouping.
--
KARL DEWEY
Build a little - Test a little


:

I have a report that looks at services provided to a clients. It records the
TimeSpent. I have a header for each client where I total the TimeSpent. The
details section has all the services listed to that particular client. How
to I sort my report on the calculated field =sum([TimeSpent]) which is in my
client header?

 

[Anne]

Anne

Thanks Karl,

I'm getting closer. The Time Spent query works fine, however my original
query is looking at services performed in the last calendar year. The Time
Spent query totals the time spent for all of the services performed
regardless of the time period. If add my time restriction to the last
calendar year in my Time Spent query it then just totals each service instead
of all the services for the year. I have tried changing how my query's link
but so far I haven't had any luck.

Ok, do the totals query but only using fields Client and TimeSpent and save
as [TotalTimeSpent].

Open your 'functional query' in design view and save a copy. Open it again
and add the [TotalTimeSpent] query to the grid. Click on Client field of
the table and drag to the Client field of [TotalTimeSpent] query. Click on
the [TotalTimeSpent] [SumOfTimeSpent] field and drag to the Field row of the
grid. Save.

Use the [SumOfTimeSpent] field in the report header and in the report
Sorting and Grouping.

--
KARL DEWEY
Build a little - Test a little

I don't think I am explaining my problem properly. I will try again. I have
a functional query and my report runs fine. I have a client header, in the
details of each it shows the TimeSpent on each client visit. In my header I
have a field where I total the TimeSpent for each client. I want to sort the
way the clients are displayed by descending order of the Total Time Spent
which is calculated on the report.

I tried to add the sum as you discribed and my report no longer runs - it
asks me for TimeSpent.
--
Anne

Create a query in design view. Click on the Greek symbol that looks like the
letter 'M' laying on it's side -- ∑
Select the fields you want by draging them into the grid. Below each field
name it will read GROUP BY. Change the one you want to total to Sum.

Just remember you can not have your cake and eat it too - example --
Name Start Amount
Joe 1/2/09 4.24
Joe 2/2/09 5.32

To sum the amount you have to drop the date field or change the GROUP BY to
something that will roll up the date - Min, Max, or Avg.
--
KARL DEWEY
Build a little - Test a little


:

I don't know how to do a "totals query"--
Anne


:

Use a totals query.
--
KARL DEWEY
Build a little - Test a little


:

I don't know how to add it to my query. I have tried a few ways but give me
error messages.
--
Anne


:

Do the summing in the query feeding the report and then use the report
Sorting and Grouping.
--
KARL DEWEY
Build a little - Test a little


:

I have a report that looks at services provided to a clients. It records the
TimeSpent. I have a header for each client where I total the TimeSpent. The
details section has all the services listed to that particular client. How
to I sort my report on the calculated field =sum([TimeSpent]) which is in my
client header?

 

[John W. Vinson]

I'm getting closer. The Time Spent query works fine, however my original
query is looking at services performed in the last calendar year. The Time
Spent query totals the time spent for all of the services performed
regardless of the time period. If add my time restriction to the last
calendar year in my Time Spent query it then just totals each service instead
of all the services for the year. I have tried changing how my query's link
but so far I haven't had any luck.

Please open the query in SQL view and post the SQL text here. Karl or another
volunteer will be able to help you straighten it up.

 

[Anne]

SELECT qryWorkOrder7.TimeSpent, [Clients Vents].VentName, Clients.ClientID,
Clients.ClientFirstName, Clients.ClientLastName, Clients.City,
HealthRegion.HealthRegionName, qryWorkOrder7.WorkorderID,
qryWorkOrder7.EmployeeID, qryWorkOrder7.DatePerformed,
tblWOCategories.Description
FROM (((tblWOCategories INNER JOIN qryWorkOrder7 ON tblWOCategories.ID =
qryWorkOrder7.CategoryID) INNER JOIN ((HealthRegion INNER JOIN Clients ON
HealthRegion.HealthRegionID = Clients.HealthRegions) INNER JOIN Departments
ON Clients.DepartmentID = Departments.DepartmentID) ON qryWorkOrder7.ClientID
= Clients.ClientID) INNER JOIN [Clients Vents] ON Clients.Vent = [Clients
Vents].VentID) INNER JOIN qryTotalTimeSpent ON Clients.ClientID =
qryTotalTimeSpent.ClientID
WHERE (((qryWorkOrder7.DatePerformed) Between Date()-365 And Date()) AND
((Departments.DepartmentName)="PROP" Or (Departments.DepartmentName)="Both"));


Thanks, here it is.

 

[KARL DEWEY]

You need a left join but I am not sure which query is summing your time.
Try this ---
SELECT qryWorkOrder7.TimeSpent, [Clients Vents].VentName, Clients.ClientID,
Clients.ClientFirstName, Clients.ClientLastName, Clients.City,
HealthRegion.HealthRegionName, qryWorkOrder7.WorkorderID,
qryWorkOrder7.EmployeeID, qryWorkOrder7.DatePerformed,
tblWOCategories.Description
FROM (((tblWOCategories INNER JOIN qryWorkOrder7 ON tblWOCategories.ID =
qryWorkOrder7.CategoryID) INNER JOIN ((HealthRegion INNER JOIN Clients ON
HealthRegion.HealthRegionID = Clients.HealthRegions) INNER JOIN Departments
ON Clients.DepartmentID = Departments.DepartmentID) ON qryWorkOrder7.ClientID
= Clients.ClientID) INNER JOIN [Clients Vents] ON Clients.Vent = [Clients
Vents].VentID) LEFT JOIN qryTotalTimeSpent ON Clients.ClientID =
qryTotalTimeSpent.ClientID
WHERE (((qryWorkOrder7.DatePerformed) Between Date()-365 And Date()) AND
((Departments.DepartmentName)="PROP" Or (Departments.DepartmentName)="Both"));

If this does not work then probably this --
qryWorkOrder7 LEFT JOIN tblWOCategories

When seen in design view the left joins (arrow pointing to the right) must
be to the left of inner joins (no arrow heads).
--
KARL DEWEY
Build a little - Test a little

SELECT qryWorkOrder7.TimeSpent, [Clients Vents].VentName, Clients.ClientID,
Clients.ClientFirstName, Clients.ClientLastName, Clients.City,
HealthRegion.HealthRegionName, qryWorkOrder7.WorkorderID,
qryWorkOrder7.EmployeeID, qryWorkOrder7.DatePerformed,
tblWOCategories.Description
FROM (((tblWOCategories INNER JOIN qryWorkOrder7 ON tblWOCategories.ID =
qryWorkOrder7.CategoryID) INNER JOIN ((HealthRegion INNER JOIN Clients ON
HealthRegion.HealthRegionID = Clients.HealthRegions) INNER JOIN Departments
ON Clients.DepartmentID = Departments.DepartmentID) ON qryWorkOrder7.ClientID
= Clients.ClientID) INNER JOIN [Clients Vents] ON Clients.Vent = [Clients
Vents].VentID) INNER JOIN qryTotalTimeSpent ON Clients.ClientID =
qryTotalTimeSpent.ClientID
WHERE (((qryWorkOrder7.DatePerformed) Between Date()-365 And Date()) AND
((Departments.DepartmentName)="PROP" Or (Departments.DepartmentName)="Both"));


Thanks, here it is.


--
Anne

Please open the query in SQL view and post the SQL text here. Karl or another
volunteer will be able to help you straighten it up.

 

[Anne]

Hi Karl,

Your suggestions didn't work but got me on the right track. I made another
query where I specified the time period, (last calendar year) and listed all
the services and [TimeSpent]. I modified the qryTimeSpent by adding the
lastest query I made and using the [TimeSpent] field from there (it only had
the last calendar year. I used the [TimeSpent] from that query to create a
sum for each client. I could then use the sumTimeSpent field in my query to
sort my report. It took a lot of teeth gnashing and some choice words but my
report now sorts in descending order of the total time spent/client. I will
post my qry SQL because I'm not sure it is clear from my description above
what I did.

SELECT qryTotalTimeSpent.SumOfTimeSpent, qryWorkOrder7.TimeSpent,
qryWorkOrder7.DatePerformed, [Clients Vents].VentName, Clients.ClientID,
Clients.ClientFirstName, Clients.ClientLastName, qryWorkOrder7.WorkorderID,
tblWOCategories.Description
FROM (((tblWOCategories INNER JOIN qryWorkOrder7 ON tblWOCategories.ID =
qryWorkOrder7.CategoryID) INNER JOIN (Clients INNER JOIN Departments ON
Clients.DepartmentID = Departments.DepartmentID) ON qryWorkOrder7.ClientID =
Clients.ClientID) INNER JOIN [Clients Vents] ON Clients.Vent = [Clients
Vents].VentID) LEFT JOIN qryTotalTimeSpent ON Clients.ClientID =
qryTotalTimeSpent.ClientID
GROUP BY qryTotalTimeSpent.SumOfTimeSpent, qryWorkOrder7.TimeSpent,
qryWorkOrder7.DatePerformed, [Clients Vents].VentName, Clients.ClientID,
Clients.ClientFirstName, Clients.ClientLastName, qryWorkOrder7.WorkorderID,
tblWOCategories.Description
HAVING (((qryWorkOrder7.DatePerformed) Between Date()-365 And Date()))
ORDER BY qryTotalTimeSpent.SumOfTimeSpent DESC;

--
Anne

You need a left join but I am not sure which query is summing your time.
Try this ---
SELECT qryWorkOrder7.TimeSpent, [Clients Vents].VentName, Clients.ClientID,
Clients.ClientFirstName, Clients.ClientLastName, Clients.City,
HealthRegion.HealthRegionName, qryWorkOrder7.WorkorderID,
qryWorkOrder7.EmployeeID, qryWorkOrder7.DatePerformed,
tblWOCategories.Description
FROM (((tblWOCategories INNER JOIN qryWorkOrder7 ON tblWOCategories.ID =
qryWorkOrder7.CategoryID) INNER JOIN ((HealthRegion INNER JOIN Clients ON
HealthRegion.HealthRegionID = Clients.HealthRegions) INNER JOIN Departments
ON Clients.DepartmentID = Departments.DepartmentID) ON qryWorkOrder7.ClientID
= Clients.ClientID) INNER JOIN [Clients Vents] ON Clients.Vent = [Clients
Vents].VentID) LEFT JOIN qryTotalTimeSpent ON Clients.ClientID =
qryTotalTimeSpent.ClientID
WHERE (((qryWorkOrder7.DatePerformed) Between Date()-365 And Date()) AND
((Departments.DepartmentName)="PROP" Or (Departments.DepartmentName)="Both"));

If this does not work then probably this --
qryWorkOrder7 LEFT JOIN tblWOCategories

When seen in design view the left joins (arrow pointing to the right) must
be to the left of inner joins (no arrow heads).
--
KARL DEWEY
Build a little - Test a little

SELECT qryWorkOrder7.TimeSpent, [Clients Vents].VentName, Clients.ClientID,
Clients.ClientFirstName, Clients.ClientLastName, Clients.City,
HealthRegion.HealthRegionName, qryWorkOrder7.WorkorderID,
qryWorkOrder7.EmployeeID, qryWorkOrder7.DatePerformed,
tblWOCategories.Description
FROM (((tblWOCategories INNER JOIN qryWorkOrder7 ON tblWOCategories.ID =
qryWorkOrder7.CategoryID) INNER JOIN ((HealthRegion INNER JOIN Clients ON
HealthRegion.HealthRegionID = Clients.HealthRegions) INNER JOIN Departments
ON Clients.DepartmentID = Departments.DepartmentID) ON qryWorkOrder7.ClientID
= Clients.ClientID) INNER JOIN [Clients Vents] ON Clients.Vent = [Clients
Vents].VentID) INNER JOIN qryTotalTimeSpent ON Clients.ClientID =
qryTotalTimeSpent.ClientID
WHERE (((qryWorkOrder7.DatePerformed) Between Date()-365 And Date()) AND
((Departments.DepartmentName)="PROP" Or (Departments.DepartmentName)="Both"));


Thanks, here it is.


--
Anne

I'm getting closer. The Time Spent query works fine, however my original
query is looking at services performed in the last calendar year. The Time
Spent query totals the time spent for all of the services performed
regardless of the time period. If add my time restriction to the last
calendar year in my Time Spent query it then just totals each service instead
of all the services for the year. I have tried changing how my query's link
but so far I haven't had any luck.

Please open the query in SQL view and post the SQL text here. Karl or another
volunteer will be able to help you straighten it up.