Sum Report Order By

[jojo86]

Hi

I have a report that shows all records with criteria for land development (which are each scored) then all scores are added up for each record. This works great.

However, I have been told that it needs to be ordered by the total score, in decending order, which I cannot do. The total score is calculated on the actual report. I tried putting the calculation in a query but it did not add the scores up correctly. Can anyone help? I am using 2007 but can revert back to 2003 if needed (just in case!)

This is my last day in work and I will not be able to complete this afterwards.

I would really appreciate anything you can give.

 

[ted.martin]

Without seeing the report and the underlying query, it may be difficult to provide a quick solution. Firstly, I would ask why the calculation within the query is not adding up properly. I would certainly be looking at this. Secondly, there is a property within the report design that allows you to Sort data. In Access 2003 this is the Sorting and Grouping button on the toolbar (there will be one somewhere in 2007)

 

[jojo86]

Hi

Thanks for getting back to me. The report is attached in a zip file (the database has been cut down a lot, this one has 3/4 records, the original has over 400). The report is called rptSumGIS, which comes from the query qrySumGIS. The calculated field is called GIS_Total (on the report) and when I put this into the query instead, it does not calculate correctly. Each site has 12 criteria for GIS scores, yet when the calculation is done in the query, it doesn't total them all and doesn't show them all for all sites either.

The report is ordered by RTP_ID2 and then by QuesNo, ascending, but the Total Score has to be the dominant order by, decending.

One thing I need to mention is that the scores are either 5 or 0 (with some having 4,3,2 and 1). This could help you, I am not sure.

Thanks again.

 

[ted.martin]

When you say you want the results in Descending Order, do you mean within each RTP_ID2 and then by QuesNo order?

 

[ted.martin]

Just tried putting Score in the Report's sort sequence between the current sort order of RTP_ID2 and QuesNo. Does this work for you?

 

[jojo86]

The report needs to be ordered by Total Score, decending. If more than one record has the same score, I would like it to be ordered by RTP_ID2, ascending. The criteria for each record needs to be ordered in ascending order. EG:
Record 3 - score 50 Record 1 - score 40 Record 2 - score 40
Criteria 1 - 5 Criteria 1 - 5 Criteria 1 - 5
Criteria 2 - 0 Criteria 2 - 0 Criteria 2 - 0
Criteria 3 - 5 Criteria 3 - 5 Criteria 3 - 0
Criteria 4 - 5 Criteria 4 - 0 Criteria 4 - 0
Criteria 5 - 0 Criteria 5 - 0 Criteria 5 - 5
Criteria 6 - 5 Criteria 6 - 5 Criteria 6 - 5
Criteria 7 - 5 Criteria 7 - 5 Criteria 7 - 5
Criteria 8 - 5 Criteria 8 - 0 Criteria 8 - 0
Criteria 9 - 5 Criteria 9 - 5 Criteria 9 - 5
Criteria 10 - 5 Criteria 10 - 5 Criteria 10 - 5
Criteria 11 - 5 Criteria 11 - 5 Criteria 11 - 5
Criteria 12 - 5 Criteria 12 - 5 Criteria 12 - 5

Record 1 and 2 are in ascending order even though they have the same score. That's what I would like to see on the reports. By the way, quesNo is not visable but that is what is used for the order by.

Thanks for this

 

[Brianwarnock]

I would expect all grouping and sorting of a report to be done before any calculations therefore you must do the calculation in a query. I would have done this ages ago if people wouldn't hide the toolbars before posting!!

Create a query grouping on RTP_ID2 and sum Score, from current qrySumGis and then join the 2 to create a new query to form the source of the report the totals coming from sumofsum. You will be able to sort on this.

Brian

 

[jojo86]

Thanks for that, the report is now ordering by total score. Unfortunately, it is not displaying it correctly. There are only 3 records in this database with scores for each criteria, however when I open the report, it shows 3 different sets of scores for each of those records, meaning it is showing nine records (record 9, record 66 and record 67).

I can see on the report that it is showing the scores for one record (in this case, 67) three times but placing it under the other records too. Once this has been done, it then goes to show the next lowest score (in this case that of record 9) and does the same etc.

I am not sure why it is doing this, but the database is attached again (with the updated report on it). And I am sorry that the toolbar is hidden, I didnt even realise it was at all!

 

[ted.martin]

Thanks for sending a typical report layout.You have set RTP_ID2 as a report header; I think this is the problem. Given the amount of text in the header section, this may be another issue.

To try and find an acceptable report layout - I would create a simple TEST report with the 3 key fields in the detail section and get it to sort properly. All the other stuff to do with Site name and Town can be dropped on later.

I will have play for a while to see if I can come up with something.

 

[jojo86]

Thanks for very much for helping me, it is much appreciated.

 

[ted.martin]

Try this - Report V 2. Page breaks may need sorting but I think this is close.

 

[Brianwarnock]

Thanks for that, the report is now ordering by total score. Unfortunately, it is not displaying it correctly. There are only 3 records in this database with scores for each criteria, however when I open the report, it shows 3 different sets of scores for each of those records, meaning it is showing nine records (record 9, record 66 and record 67).

I can see on the report that it is showing the scores for one record (in this case, 67) three times but placing it under the other records too. Once this has been done, it then goes to show the next lowest score (in this case that of record 9) and does the same etc.

I am not sure why it is doing this, but the database is attached again (with the updated report on it). And I am sorry that the toolbar is hidden, I didnt even realise it was at all!

It worked ok for me, I will try to look at the new db if I get a chance.

Brian

 

[Brianwarnock]

There is no join in the query qrySumGis3 join on RTP_ID2

Brian

 

[jojo86]

Try this - Report V 2. Page breaks may need sorting but I think this is close.

This is close, and it does sort by the score, but unfortunately it is the Total Score that needs to be sorted, not the individual scores, which is what is happening in this version.

Thanks for trying this

 

[ted.martin]

Ok - getting close now. As Brian suggested you need to get the Total Score field from your under-pinning query, then you can sort on it. As I suggested previously - that is where you need to go back to. If the totalling in the query is wrong, is there a rounding problem etc. Maybe you need to do something with the command FIX().

Build your query one step at a time and go from there. Keep it simple with the key elements only.

 

[jojo86]

There is no join in the query qrySumGis3 join on RTP_ID2

Brian

Thank you Brian!!! I totally forgot about that and now it seems to be working. Thanks for your help too Ted, all appreciated lots

 

[Brianwarnock]

Good glad to have helped, things are easily overlooked when under pressure.

Brian

 

[jojo86]

tell me about it. It is my last day here and I have a million things to do on two different databases! Thanks again