Ordering totals in a report

[not a code fan]

Hi

I am running a database that records the sales figures from twelve charity shops. I use the database to record the monthly sales of different categories such as ladies clothes, mens clothes, kids clothes etc. I have a different report for each category so lets take the report for 'Ladies clothes.'

I would like the report to show the Sales total for the shop that sold the most Ladies clothes for that month at the top of the list and the shop that sold the least at the bottom. I can do this easily in a table but a report???

I am currently using this code to sum the monthly figures in one of the footers =Sum([1 Ladies Clothes Sales])

Can i add something onto that code that would also organise the totals as described?

By the way i am a dummy when it comes to expressions and the like so please pretend i am a complete thicko and that will help a bundle.

 

[Rich]

You would have to use a totals query and base the Report on that, as an aside you don't need separate reports for each category, you can use criteria in a query or the Where clause of the open report method

 

[not a code fan]

"You would have to use a totals query and base the Report on that, as an aside you don't need separate reports for each category, you can use criteria in a query or the Where clause of the open report method"

I'm getting a bit lost. I already have a query that does what i want and i think the report is based on that query although when in the report and i go to properties and the SQ window it doesn't show the list of fields from the query i made it shows the list of fields from the base table. I've tried doing it again with the wizard, choosing the right query etc and the same happens again. I don't know if that is the problem.

I already have 99% of what i want, It seems as though all i need to do is overide Access trying to alphabetise the Shop names rather than order the sales totals in order of size. It seems to automatically alphabetise the shop names in the report whereas when using tables you have to ask it to alphabetise.

 

[Rich]

You have to use the sorting/grouping option on the Report toolbar, however you cannot sort on a calculated Total, as I've already said you'll have to base the Report on a TotalsQuery

 

[The_Doc_Man]

It seems to automatically alphabetise the shop names in the report whereas when using tables you have to ask it to alphabetise.

I'll try to explain this behavior.

Tables and reports have different behaviors because reports built by wizards have sort options built in. The wizard ASKS you for the sort order, so you should not be surprised if the report comes out sorted.

On the other hand, if the Access table does not have the ShopName field as its primary key, then it does not by default present shop names in alphabetical order. Whatever is your prime key determines the order. If your prime key is an autonumber field, then the order of entering the shops is also the prime key order.

So a simple rule: For sorting, keys are "key."

I would like the report to show the Sales total for the shop that sold the most Ladies clothes for that month at the top of the list and the shop that sold the least at the bottom.

Having attacked the earlier problem first (which is necessary for the understanding of this one), I'll try to explain. You can sort by ShopName easily enough 'cause it is a field in a table. Relatively static. You can edit the field - but you don't.

On the other hand, Rich points out (correctly) that to sort by a particular item that is computed, you need to base the report on a query, not on the raw information. Why? 'cause the totals are computed. They don't exist UNTIL they are computed. And they don't exist for the previous member once you pass to the next member of the GROUP BY. Can't sort on something that doesn't exist. Can't compare totals 'cause only one total at a time exists. To what would you compare it?

So the only way to make them exist is to define a summation query that contains them. Then base the report on the query rather than the original table.

NOW, the next part of this, I can see it coming, is "How would I build one report that does the same thing for each of the twelve categories?"

The answer is, "It ain't easy." In fact, if you are having trouble with Access expressions, taking your report to the next level may be insurmountable until you get more experience under your belt. That's not a condemnation, it's intended as an attempt to put things in proper perspective.

Building a report on a simple summation query is like climbing to the top of a small hill. Building the combined report that shows different sort orders for the different categories of the same data set is like climbing Mt. McKinley or Mt. Everest. Doable, but not for the faint of heart.

 

[not a code fan]

Halfway there

Thanks for taking the time to put things into access dummy terms, it helps. As it happens i found what appears to be another way around the problem. From another source of information i found (didn't even notice before) that when creating a report with the wizard you can choose which field to put in order.

The hard part of creating a query table with monthly totals and an expression that sorted out the correct month had already been done for me by someone else who used the database so i just had to figure out how to get the totals in the right order. So now i have all the shop names in there for that month and the totals figures in descending order as i wanted.

I said halfway there as, as i have outlined in another thread, once past this problem i wanted to apply this structure to all 10 categorys (ladies, mens clothes etc) meaning 10 separate reports; then put all the separate reports into one big report. My problem was avoiding the "enter date parameter" for every single sub report contained in the main report.

Hopefully my previous conquest of the small hill has been done in the correct way and has not fluffed up my chances for the next stage just mentioned.

 

[Rich]

As I said to you before, you don't need ten separate reports, you can use the sorting and grouping options to add subGroups.
You'll have more trouble trying to add ten subReports to one master.

As for entering parameter prompts for each report you should use a popup form to enter the relevant date/s, just keep it open whilst running the Report

 

[not a code fan]

Hmm ok i'll give that a go, sounds a bit tricky though with my limited experience