Totals decending order on report grouped by other fields

[surfside1]

This should be a simple thing to do and I'm have great problems in figuring it out.

I have a report grouped by location, commodity. I need detail records and total records by amount in decending order. It does the location ok but and the amounts within a commodity ok, but I need the largest "grand total" of the commodity to display first. That way you quickly view your highest group amount in decending order.

The table is in location, commodity, amount decending order. What could I be doing wrong. Thanks!

 

[surfside1]

HEY! GOT IT TO WORK!
What I did was put the Spend Total as a grouping on the report. It is a field in the query and it holds the Total Spend and the detail is another field. Then I moved Commodity to the detail area of the report. It repeats for every detail record but I don't care because their Excel does also. Maybe I can find code that will check to see if Commodity was what was previously printed to and leave a blank(?)
But anyway, this is great and you both have been so helpful.
THANK YOU!

 

[surfside1]

Ignore my last statement about the commodity repeating, I just moved it to the total grouping.

 

[surfside1]

Now am having another problem.....
I have the decending order fine for the detail records showing commodity spend $$, grouped by commodity. To have the total spend by commodity to show before the detail, it required a separate query that only totaled the commodity spend $$. In the query, I have the detail table, and joined the query for the total commodity spend $$ on commodity. The query produces the detail of the commodity spend $$ and the total is just repeated on each detail, no big deal. Then on the report I included both queries detail and total, and created the report with a group above the detail with the commodity total spend $$ before the detail as a group above the detail.

I also changed the report properties to OrderBy the detailed spend $$, since it is not in the sorting and grouping, also the OrderByOn to yes. Everything works.

This report is also grouped by location being the highest level. I did the same thing by creating a query that totaled the location, did the same join but at the location, and that decending order also works.

Now, I need to total the whole table spend $$ and put it at the beginning of the report. This is also what you can do in Excel by totaling the subtotals. But when I modify the main query to include this total query, I get the error "You have chosen fields from a record source which the wizard (because I keep making a new report) cannot connect. You may have chosen fields from a table and from a query based on the table. If so try choosing fields from only the table or only the query".

I think the problem is that I'm creating this new report from the query that contains detailed records from the table, totals from the commodity query, totals from the location total, and I really don't have anything to relate any of them to the grand total of all facilities.


Any ideas?

 

[surfside1]

Can a table be created from a query?

 

[stix4t2]

Surfside, you should be able to use a sum() formula in the total area to sum up the amounts and count() to get counts. And you can create a table from a query by using a make query or append to an empty table. What are you thinking here??

 

[surfside1]

I guess, and know, I have not tried an append. I just saw the "make query" feature, haven't looked further, won't until in the am.

I need:
grand total
commodity total
detail total
in that order.

I appreciate you getting back.
I'll continue in the am.
Thanks!

 

[surfside1]

I hate to say this but I'm still struggling with this. I'm sure none of you remember this situation I had, but to summarize, I have a report that needs to be in the following order:

"Grand Total" (for table's detail records, placed once on top of report)

"Total of Facility" (decending for table's detail records)

"Total of Facility's Commodity" (decending for table's detail records)

Detail records

I resolved all the issues written in this thread by adding the Grand Total to every detail record. Everything comes out great. When I try to do a summary report, removing he detail, it can't because that is where the Grand Total is. I'm sure you would have thought of that but I didn't.

Can a report that is based on a query of the detail table and an individual field on that report be based on the result from another query that is one record - the grand total? The entire problem is that I need the grand total 1st on the form. I know you can create a grand total on a report but it has to be at the end of the report, correct?

Thanks for any help anyone has.
Surfside1

 

[stix4t2]

Hey Surfside,

Sometimes things take time, like wine and cheese. Did you try the sum(field) function. It can be in the section header or footer.

Stix 42
Long Live Rock and Roll
Pop is for drinking

 

[surfside1]

Stix, I swear I tried that one time and it gave me the 1st value of spend. Dern! It works like a charm!
Thanks!
Surfside1

 

[surfside1]

The grand total works in the detail report.
When I delete the data in the detail area which has 2005_Total_Spend I get the error:
“You tried to execute a query that does not include the specified expression ‘[2005_Total_Spend]’ as part of an aggregate function.”

The report is built on the query that is the entire table, joined by the Location query that groups and sums 2005_Total_Spend. The display of the query shows Sum Of 2005_Total_Spend. It is also joined by the Commodity query that groups and sums 2005_Total_Spend. The display of the query shows SumOf2005_Total_Spend.
The two totals are added to the main query.
The report shows for the total field of Location as SumOfSum Of 2005_Total_Spend.
And Commodity as SumOfSumOf2005_Total_Spend.

So deleting the detail that has the 2005_Total_Spend is what is causing this. Is it due to the 2 queries that total 2005_Total_Spend?
We may learn what we spent last year – next year!
Thanks for any help!
Surfside1.