Report - sort within a formula?

[Lilly420]

Hello,

First, I thank you for any help you can provide, I truly appreciate it.

I have a Report based on a query in Access and I need it to sort in a different manner and can’t seem to make it do that as the item I want to sort is a formula …

Right now it sorts like this…first on month and year – oldest to newest – which is great.

Second sort is sorting on the reviewer’s name which I don’t want, I want it to sort on their monthly total – (number they reviewed within that month) with the one with the most reviews first – but that is a formula (see below).

What I have currently:

January 2013 Monthly Total: 7 % of Grand Total 100%

Reviewer 1 2 28.6%
Reviewer 2 4 57.1%
Reviewer 3 1 14.3%

This is what I would like:

January 2013 Monthly Total: 7 % of Grand Total 100%

Reviewer 2 4 57.1%
Reviewer 1 2 28.6%
Reviewer 3 1 14.3%

The formula is in the Reviewer Name Header and looks like this:

Reviewer Full Name =Count([Review Date]) =Count([Review Date])/[AccessTotalsReview Date1]

Again, thank you for any help.

Lilly

 

[jdraw]

Reports have their own sorting and grouping --use them.
You can not depend on query sort order once you create a report.

 

[Lilly420]

Hello,

Thank you and I am aware of the sorting and grouping in reports that is how I got my formulas-my problem is trying to sort on =Count([Review Date]) as it is in the Reviewer Name Header and will only let me sort on the reviewer name and I am unsure how to get it to sort on the count of review date so I can get the top producer first? Not sure I am wording this correctly...

Thank you.

Lilly

 

[jdraw]

Would be helpful if we could see the database-- you could try zipping a copy. Remove anything private first.

 

[Ian Rennie]

Is there a possibility you can do the calculation of the percentage for all the reviewers outside the report and then provide it to the report as part of the source data?

Ian

Hello,

First, I thank you for any help you can provide, I truly appreciate it.

I have a Report based on a query in Access and I need it to sort in a different manner and can’t seem to make it do that as the item I want to sort is a formula …

Right now it sorts like this…first on month and year – oldest to newest – which is great.

Second sort is sorting on the reviewer’s name which I don’t want, I want it to sort on their monthly total – (number they reviewed within that month) with the one with the most reviews first – but that is a formula (see below).

What I have currently:

January 2013 Monthly Total: 7 % of Grand Total 100%

Reviewer 1 2 28.6%
Reviewer 2 4 57.1%
Reviewer 3 1 14.3%

This is what I would like:

January 2013 Monthly Total: 7 % of Grand Total 100%

Reviewer 2 4 57.1%
Reviewer 1 2 28.6%
Reviewer 3 1 14.3%

The formula is in the Reviewer Name Header and looks like this:

Reviewer Full Name =Count([Review Date]) =Count([Review Date])/[AccessTotalsReview Date1]

Again, thank you for any help.

Lilly

 

[Lilly420]

Thank you for your help, I will be able to provide a clean Database tomorrow with the query and report in which I am referring to ... maybe then it would easier to see what I am talking about...also did some more research online and from some info I found it was said to perform the calculation in the query and then pull that field into the report so I could sort on it which makes sense but not sure how to get the calculation into the query correctly...what are your thoughts on that?

Again, thank you for any help.

Lilly

 

[Ian Rennie]

Can you post the source table structure and possibly a couple of lines of data together with the current query you are using to extract the data for the report - if you are using a query??

Ian

 

[Lilly420]

Hello Ian,

I am working on providing you a scrubbed version of the database with the table, query, and reports I am asking about. Hopefully later today, and again, thank you for the help...

Lilly

 

[Lilly420]

Hello,

Here is a scrubbed version of my database with the table, query, and report I am referring to. Again, thank you for any help with this.

Lilly

 

[Ian Rennie]

Hi,

Herewith one possible solution. It'll need tidying, but yo can see how the queries work. All the best, Ian

 

[Lilly420]

Hello Ian,

Thank you so much for taking the time to help me...and the report looks perfet - I have downloaded your tweaked DB and am going through it now to try to understand what you did...may I ask you questions if needed?

Lilly

 

[Ian Rennie]

Of course. I'm struggling with one of my own just now.

 

[Lilly420]

Thank you Ian, this is very cool what you did - I wish I understood more on how to do these types of things - but having smart people like you to help is awesome - still going through it and trying to map what you did.

Lilly

 

[Lilly420]

Hello Ian,

I went through the queries and think I understand exactly what you did- you created the LinkFld so you could join the tables in the query is that correct? You did a great job and again, thank you for the help.

Lilly

 

[Ian Rennie]

Yes, you could run the query without actually linking the tables but if it ever has more than one field you will end up with No of records = original records * fields in second table. Access just provides all possible records. Nightmare.

Let me know if you need anything else.
Ian

 

[Lilly420]

Thank you Ian for all your help, you are wonderful!

 

[Lilly420]

Hello Ian,

Hope you are still out there...I am having some trouble with this and not quite sure how to fix it--I did try. I know you said if it runs over year end the queries and reports would need to be tweaked...I tried and failed...our fiscal year runs 11-1-2013 through 10-31-14 and I can't get the reports to work right ...can you walk me through how to tweak the queries and reports? I thank you for any help.

Lilly

 

[Ian Rennie]

Hello Lilly,

Yes, I'm here. I'll just take a look at the database I put up and get back to you.

All the best, Ian

 

[Ian Rennie]

Hello Lilly,

Herewith one way of doing it. In the query which generates the month from the reviewer date you also generate a field which is the year from the reviewer date. Then link on the year as well as the month and the link field. In the report add a RepYr header and possibly footer - though you won't need a footer if you just select your financial year dates for the query.

Let me know if this gets you where you want to go.

Ian

 

[Lilly420]

Hello Ian,

I am sorry I did not get back to you, I had a death in my family and am just getting back to things now...I will look at this and let you know but wanted to say thank you for taking the time to help me, I so appreciate it.

Lilly