Sorting of Total sum in report

[mohtar01]

Create a report with customer header and footer.
On the customer header, it contains the customer name.
The detail section contains information like brand, classification and units.
On the customer footer, it contains the sub-total of units for each customer.

My questions is, is there a way to sort the sub-totals in descending order.

Thanks in advance.

 

[Pat Hartman]

No. Not unless they are pre-calulated in the query. To pre-calculate them, create a totals query that sums the same way your report will. Then join this query to your original query on the break fields and include the summed value from the totals query. You should see the new column in your report's sorting and grouping dialog and you will be able to sort on it.

 

[mohtar01]

Hi Pat,

Thanks for the reply. Solve problem as per your advice.

regards,

 

[gtj_global]

Hi Pat,

I hope you don't mind me replying to this thread and not starting a new one, but I have the same problem. I have googled help on the topic and you've come up several times in the results.

I have two tables and one query to bring them together. But I want to sum the total number of times an items appears and then sort descending.

What do you mean by "break fields" and how does one go about joining the two queries together so that the new column is available. Can this be done in design view or SQL?

Thank you so much for your help.
Regards, GTJ

 

[Pat Hartman]

Welcome aboard!

You need to create a query that does your sum/count and then join that query to the original query and use the new query as the recordsource for the report.

"break" fields are the ones defined in the sorting and grouping dialog that tell Access how to sort and at which points to create group headers and footers. For example, if you wanted to produce a telephone list and you wanted each department to start on a separate page, you would add departmentID to the sorting/grouping dialog and specify that you want a group header. A new section will be added to the report where you can put the department name. You can also set the new page property to cause a new page to be printed when the value of department "breaks" - ie changes.

 

[gtj_global]

Hi Pat,
Thank you for your reply.
I have created the query to return the total and it returns the correct data.
When I join the two queries and run it I get a "circular reference error." I am assuming because the first query must run first (to calculate the numbers), then the second query to total the numbers. I tried nested query but the nested query can only return on value and I have many values to return -OR- it is because I am joining the two queries by a common field...?

I understand the "break fields" now... thank you for explaining.

On a different topic... is it possible to return a pivot table in a report -OR- possible to pass the data parameters to a pivot table using a form?

Thanks again for your help. I really appreciate it.
- G

 

[stopher]

Hi Pat,
Thank you for your reply.
I have created the query to return the total and it returns the correct data.
When I join the two queries and run it I get a "circular reference error." I am assuming because the first query must run first (to calculate the numbers), then the second query to total the numbers. I tried nested query but the nested query can only return on value and I have many values to return -OR- it is because I am joining the two queries by a common field...?

Could you post your queries?

On a different topic... is it possible to return a pivot table in a report -OR- possible to pass the data parameters to a pivot table using a form?

Take a look here, particularly the section "Handle Parameters".

hth
Chris

 

[gtj_global]

Chris,
My queries are below.
The first query is the result of joining two tables (two parameters (1) choose family, (2) start and end date)

PARAMETERS [Forms]![SingleReport]![cboStartDate] DateTime, [Forms]![SingleReport]![cboEndDate] DateTime;
SELECT Family.Family, Family.[Item Code], Family.Color, Family.Size, Order.[No Sold], Order.Date
FROM Family INNER JOIN [Order] ON Family.ID = Order.FamilyID
GROUP BY Family.Family, Family.[Item Code], Family.Color, Family.Size, Order.[No Sold], Order.Date
HAVING (((Family.Family)=[Forms]![SingleReport]![Family]) AND ((Order.Date)>=[Forms]![SingleReport]![cboStartDate] And (Order.Date)<=[Forms]![SingleReport]![cboEndDate]))
ORDER BY Family.Family, Family.[Item Code], Family.Color, Family.Size, Order.[No Sold] DESC;


The second query totals (sums) the above results by [Item Code].

SELECT DISTINCTROW General_Query_One_Item.Family, General_Query_One_Item.[Item Code], Sum(General_Query_One_Item.[No Sold]) AS [Sum Of No Sold]
FROM General_Query_One_Item
GROUP BY General_Query_One_Item.Family, General_Query_One_Item.[Item Code];

I am going to checkout the link you posted and see if I can get something to work.
Any help would be greatly apprecaited.
Thank you SO much - GTJ

 

[Pat Hartman]

A circular reference error occurs when you give a calculated field an alias name which the same name as a table field. I don't see this in your query but I do see three other issues.

1. you have three parameters and only two are defined.
2. the first query should not have a group by and the having should therefore be a where clause.
3. Distinctrow is not needed in the second query. Group by will produce a single row for each set of Family and [Item Code]

 

[themurph2000]

Welcome aboard!

You need to create a query that does your sum/count and then join that query to the original query and use the new query as the recordsource for the report.

"break" fields are the ones defined in the sorting and grouping dialog that tell Access how to sort and at which points to create group headers and footers. For example, if you wanted to produce a telephone list and you wanted each department to start on a separate page, you would add departmentID to the sorting/grouping dialog and specify that you want a group header. A new section will be added to the report where you can put the department name. You can also set the new page property to cause a new page to be printed when the value of department "breaks" - ie changes.

Glad I found this, because this is what I needed as well. My bosses wanted me to create a report that sorted by the profit/loss column, which was previously a summation in the report itself. I tried to get around it by creating a report off the groups/totals query and add a subreport with the original records, but it was running insanely slow. This worked so much better.

Thanks!