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!

 

[stix4t2]

Add another section for amount, but hide the header and footer if you don't want to see them.

 

[surfside1]

I am adding a separate section for total and it comes up fine, but the totals look like this:

Commodity 1 (ascending order)
item 1 $4,624(decending order)
item 2 $3,734(decending order)
item 3 $2,011(decending order)
item 4 $187(decending order)
item 5 $174(decending order)
item 6 $117(decending order)
-----------------------
tot $10,848
(is there a sort order for the tot so it would go below:

Commodity 2 (ascending order)
item 1 $3,312(decending order)
item 2 $2,392(decending order)
item 3 $2,187(decending order)
item 4 $1,518(decending order)
item 5 $396(decending order)
-----------------------
tot $9,806
These are the actual numbers displaying.
Thanks!

 

[stix4t2]

hey surfside1,

If you add a dsum() function to your query to add the totals as a field into your report's recordsource you could add a section for this field as well.

 

[surfside1]

Do I add this new field to the end of the query or something? How does it know that I would only want the totals for the commodity records just listed and then start the total over again for the next commodity? Would this be another total line or the total line in the report defined that I am now doing a =Sum([amount]) now? Thanks!

 

[stix4t2]

Something like this. I example the commodity name, but if you have ID's use them. You could also build a query that sums up the commodity totals and add it to the report source, linking on commodity. This would be faster if you build the sum query.

DSum("Amount", "tblName", "[Commodity] = '" & commodityname & "'")

 

[surfside1]

DSum[2005_Total_Spend], [tbl_2005_Spend],[Commodity] = [Commodity]

This is what I am coding, and since I don't know how to do this....it errors out. I tried putting the table in "" also. help....

 

[stix4t2]

surfside,

Close, but you have to put stuff into strings. If this is to slow try the query method.

DSum("[2005_Total_Spend]", "[tbl_2005_Spend]","[Commodity] = " & [Commodity])

Query
add table 2005_Total_Spend. Set to totals query, sigma sign on toolbar, looks like a wierd E. Add fields for Commodity and amount. Set totals for commodity to groupby, and amount to sum. Save query as CommodityTotals or what ever name you want. Add the query to the report data source and link commodity to commodity. Then add the sumofAmount field to your columns.

 

[surfside1]

Sorry, I have so many questions. Hope you don't go away. Can you add a new query to an existing table?

 

[stix4t2]

No problem, you have to build a query based on the table and add the total query to it.

 

[surfside1]

I'm sorry, I meant to an existing Report?

 

[stix4t2]

Yes, click on the report's datasource to open it. Then modify it to add the new query. If the source was a table, you will be prompted to make a query.

 

[surfside1]

The reports datasource or recordsource? The recordsource is already pointing to the first query.

 

[stix4t2]

The record source is where you want to add the sum query. You can delete the query name, click the ... and build a new query to join the two queries. The query will come up empty, add both queries and join them on commodity. Drag down the fields for the report.

 

[surfside1]

Ok.....Now I get and error: "The expression you entered as a query parameter produced the error: Microsoft Access can’t find the name “Assessories” you entered in the expression."

This is my DSum expression.
DSum("[2005_Total_Spend]","[tbl_2005_Spend]","[tbl_2005_Spend.Commodity] = " & [qwy_Total_Spend_by_Commodity.Commodity])

I added the qwy_Total_Spend_by_Commodity to the front of Commodity to differentiate the two commodities because I was getting an error:
The specified field '[Commodity]' could refer to more table in the FROM clause in your SQL statement.

My SQL statement:
SELECT DISTINCT tbl_2005_Spend.ID, tbl_2005_Spend.Location, tbl_2005_Spend.Commodity, tbl_2005_Spend.Supplier, tbl_2005_Spend.Supplier_COO, tbl_2005_Spend.LCC, tbl_2005_Spend.Supplier_Strategy_Grow, tbl_2005_Spend.Supplier_Strategy_Maintain, tbl_2005_Spend.Supplier_Strategy_Exit, tbl_2005_Spend.[2005_Total_Spend], qwy_Total_Spend_by_Commodity.SumOf2005_Total_Spend, qwy_Total_Spend_by_Commodity.Commodity
FROM tbl_2005_Spend INNER JOIN qwy_Total_Spend_by_Commodity ON tbl_2005_Spend.Commodity = qwy_Total_Spend_by_Commodity.Commodity
GROUP BY tbl_2005_Spend.ID, tbl_2005_Spend.Location, tbl_2005_Spend.Commodity, tbl_2005_Spend.Supplier, tbl_2005_Spend.Supplier_COO, tbl_2005_Spend.LCC, tbl_2005_Spend.Supplier_Strategy_Grow, tbl_2005_Spend.Supplier_Strategy_Maintain, tbl_2005_Spend.Supplier_Strategy_Exit,
tbl_2005_Spend.[2005_Total_Spend], qwy_Total_Spend_by_Commodity.SumOf2005_Total_Spend, qwy_Total_Spend_by_Commodity.Commodity
HAVING (((qwy_Total_Spend_by_Commodity.SumOf2005_Total_Spend)=DSum("[2005_Total_Spend]","[tbl_2005_Spend]","[Commodity] = " & [Commodity])));

Good Grief!

 

[stix4t2]

surfside,
the dsum was an either use it or the query. Remove it. the total spend query will bring in the value you need. It will be repeated for each row in the query, but you can create the section you need. You also don't need the qwy_Total_Spend_by_Commodity.Commodity field.

Sorry about the confusion, but you should be good to go. Check the datasheet view so you can see the commodity totals in their own column

 

[surfside1]

I did it both ways before, and just added the DSum when I got these errors. I got the totals ok, but again, it is not in the sort order I needed, meaning:

Should be Commodity 1 (used to be 2) (Commodity is defined in ascending order in the sort and grouping of the report - that is why it was 2 - cannot make it "no sort" from what I see)
item 1 $4,624(decending order)
item 2 $3,734(decending order)
item 3 $2,011(decending order)
item 4 $187(decending order)
item 5 $174(decending order)
item 6 $117(decending order)
-----------------------
tot $10,848
(Tot is the total of the decending amounts above, but the total will be decending with the other totals)

Commodity 2
item 1 $3,312(decending order)
item 2 $2,392(decending order)
item 3 $2,187(decending order)
item 4 $1,518(decending order)
item 5 $396(decending order)
-----------------------
tot $9,806

The totals decend. Man you're great, any ideas?

 

[PHV]

Your SQL statement:
SELECT ID, Location, Commodity, Supplier, Supplier_COO, LCC, Supplier_Strategy_Grow, Supplier_Strategy_Maintain, Supplier_Strategy_Exit, [2005_Total_Spend], DSum("[2005_Total_Spend]","tbl_2005_Spend","Commodity=" & [Commodity]) As Total
FROM tbl_2005_Spend;

Now in the report you may group/sort by Total,Commodity.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[stix4t2]

Surfside,

Check the query to be sure you are getting the right results, the section sort should be on the commodity total ascending, the item amount descending.

Commodity1 Item1 $4,624 $10,848
Commodity1 item2 $3,734 $10,848

Commodity2 item1 $3,312 $9,806
Commodity2 item2 $2,392 $9,806

 

[surfside1]

PH and STIX,
I cut and pasted PH's SQL code in the query, I still get the sort order (DESC) on the detail records but the group Total continues to stay in the order of Commodity (ASC). The objective is to show the largest total spend for a commodity DESC as well as the detailed records DESC.

And STIX, I also did what you suggested and the result is still what I replied to PH.

Also, if I set the ORDER BY and ORDERBYON in the properties of the report, it makes no difference to the TOTAL amount.

It seems to only do the DESC in the report on detail records because I removed the "grouping" of commodity on the report and the totals are truely in decending order.
Is there a way to group items on a report without using the Sorting and Grouping feature(which requires ASC or DESC) and still group "commodity". I wouldn't mind repeating the commodity name on each detail line, that's the way the user sees it in Excel anyway.

Any ideas? I've really hit a brick wall.
Thanks!