Sorting by a calculated field summary

[ReportSmith]

I have an imported table (linked to an employee table), in a query that
returns:
1>emp name
2>type of phone call (incoming/outgoing)
3>location to/from which called.
4>a calculated field which counts the number of calls to/from a location
[Counter: 1]

When I use the dataset in a report, I would like to sort (descending) by the
"sum of number of calls" to/from a location. I'm currently displaying only
the footers for:
1>emp name (with sum of [Counter])
2>type of phone call
3>location

I've come across this before (years ago), but forget what I did.

Thanks in advance for any pointers.

 

[Tom Wickerath]

In report design view, you can click on View > Sorting and Grouping. Specify
your desired sort order here, as sorts applied at the query level are not
used by the report.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[ReportSmith]

Thanks Tom, but I previously tried different variations of the "Sorting and
Grouping" options without success.
Within that box, if I use the expression "=Sum([Counter])//Descending"
anywhere prior to the last entry, I get either an error message :"Cannot have
aggregate function in ORDER BY clause (Sum([Counter]))." or if I put it as
the last Field/Expression, nothing happens.

I was thinking of creating a summary query and putting the dataset in the
"Detail" section of the report...what do you think?

In report design view, you can click on View > Sorting and Grouping. Specify
your desired sort order here, as sorts applied at the query level are not
used by the report.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I have an imported table (linked to an employee table), in a query that
returns:
1>emp name
2>type of phone call (incoming/outgoing)
3>location to/from which called.
4>a calculated field which counts the number of calls to/from a location
[Counter: 1]

When I use the dataset in a report, I would like to sort (descending) by the
"sum of number of calls" to/from a location. I'm currently displaying only
the footers for:
1>emp name (with sum of [Counter])
2>type of phone call
3>location

I've come across this before (years ago), but forget what I did.

Thanks in advance for any pointers.

 

[Tom Wickerath]

Here is an example using the Northwind sample database. Create a new query
with the following SQL statement:

SELECT Customers.Country, Count(Orders.OrderDate) AS [Total Orders]
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.Country;

Save the query and then create a report that is based on it, using design
view (ie. not the wizard). Click on View > Sorting and Grouping. Select
"Total Orders" with a Descending sort. Leave the grouping defaults for this
field set to No. Next, select the
"Country" field with an Ascending sort. Specify Group Header Yes. Close the
Sorting and Grouping dialog. Add the Country and Total Orders fields to the
Country Header. Cut the associated labels and paste them into the Page
Header. Collapse the Detail section to 0 height. Run the report. You should
get a report sorted by number of orders for each country descending, followed
by countries ascending (in case the number of orders is the same for two
countries).

Can you make this work for your specific example?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[ReportSmith]

Sorry Tom, I think I may have given too much info. Let's just say there is 1
table with:

CALL TYPE (in/out), DIALED NUMBER and the calculated field to count the # of
calls (in/out)

I want to summarize the # of calls by type (I'll add the rest to the report
once this works).

Thanks again.

Here is an example using the Northwind sample database. Create a new query
with the following SQL statement:

SELECT Customers.Country, Count(Orders.OrderDate) AS [Total Orders]
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.Country;

Save the query and then create a report that is based on it, using design
view (ie. not the wizard). Click on View > Sorting and Grouping. Select
"Total Orders" with a Descending sort. Leave the grouping defaults for this
field set to No. Next, select the
"Country" field with an Ascending sort. Specify Group Header Yes. Close the
Sorting and Grouping dialog. Add the Country and Total Orders fields to the
Country Header. Cut the associated labels and paste them into the Page
Header. Collapse the Detail section to 0 height. Run the report. You should
get a report sorted by number of orders for each country descending, followed
by countries ascending (in case the number of orders is the same for two
countries).

Can you make this work for your specific example?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thanks Tom, but I previously tried different variations of the "Sorting and
Grouping" options without success.
Within that box, if I use the expression "=Sum([Counter])//Descending"
anywhere prior to the last entry, I get either an error message :"Cannot have
aggregate function in ORDER BY clause (Sum([Counter]))." or if I put it as
the last Field/Expression, nothing happens.

I was thinking of creating a summary query and putting the dataset in the
"Detail" section of the report...what do you think?

 

[ReportSmith]

Tom,
I solved the problem with a Pivot table (form) built on the query.

Thanks for the assistance.

Sorry Tom, I think I may have given too much info. Let's just say there is 1
table with:

CALL TYPE (in/out), DIALED NUMBER and the calculated field to count the # of
calls (in/out)

I want to summarize the # of calls by type (I'll add the rest to the report
once this works).

Thanks again.

Here is an example using the Northwind sample database. Create a new query
with the following SQL statement:

SELECT Customers.Country, Count(Orders.OrderDate) AS [Total Orders]
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.Country;

Save the query and then create a report that is based on it, using design
view (ie. not the wizard). Click on View > Sorting and Grouping. Select
"Total Orders" with a Descending sort. Leave the grouping defaults for this
field set to No. Next, select the
"Country" field with an Ascending sort. Specify Group Header Yes. Close the
Sorting and Grouping dialog. Add the Country and Total Orders fields to the
Country Header. Cut the associated labels and paste them into the Page
Header. Collapse the Detail section to 0 height. Run the report. You should
get a report sorted by number of orders for each country descending, followed
by countries ascending (in case the number of orders is the same for two
countries).

Can you make this work for your specific example?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thanks Tom, but I previously tried different variations of the "Sorting and
Grouping" options without success.
Within that box, if I use the expression "=Sum([Counter])//Descending"
anywhere prior to the last entry, I get either an error message :"Cannot have
aggregate function in ORDER BY clause (Sum([Counter]))." or if I put it as
the last Field/Expression, nothing happens.

I was thinking of creating a summary query and putting the dataset in the
"Detail" section of the report...what do you think?