Count(*)

[Kath]

Hi! I'm hoping someone can help me with this. I need a descending sort on a textbox that has =Count(*) as its source. Nothing I do (filter, sort) works. The totals display for each group (grouped by the field Service Category). When I run a query, each record has 'countofService Category' as 1, group totals are fine in the report as is the 'Grand Total'. I just can't get the display to go from highest amount to lowest amount. Any help is really appreciated !!

 

[vbaInet]

* Create a query to return a count per Service Category
* Join this query to your report's Record Source via the Service Category field
* Use the count from step 1 in your report's Sorting.

 

[Kath]

I really appreciate your response, but don't understand it. I have a query that counts on Service Category and creates a 'CountofService Category' field which shows all '1's for each record. When I use that query as record source for my report, I get the totals all fine. It totals each group just the way it should and it all is accurate. So the report/query work fine together.
But I don't know how to make the report display get sorted by the totals for each 'Service Category' Group. IE:
Group 1 13 records
Group 2 3 records
Group 3 10 records
All appear fine, but how do I get it to have Group 1, then Group 3, then Group 2 so the values are in descending order?
Make Sense?

 

[Kath]

Or maybe I just don't understand how to 'join this query to report's record source'
VIA SERVICE CATEGORY FIELD. I have it as the reports record source (completely(?) - if that's the right wording) and using the count from step 1 in my report's Sorting I also don't understand.....

 

[vbaInet]

Highest amount of what exactly?

 

[Kath]

I keep track of the amount of calls we get for multiple types of services, so list the total calls for each and then a 'grand' total. What I want to do with those counts is list them by the heaviest hit (most calls for this service, next is that one, etc). So 'rank' each service by amount of calls in descending order. Make Sense? And thank you again!

 

[vbaInet]

So let's look back at my first post, I'll quote it for reference:

* Create a query to return a count per Service Category
* Join this query to your report's Record Source via the Service Category field
* Use the count from step 1 in your report's Sorting.

which steps have you accomplished?

 

[Kath]

OK, I think I've gotten the first and second steps; I have a query that does the counts and my report uses it as the source. I'm not understanding the last item 'use the count from step 1 in your report's sorting'

 

[vbaInet]

There's a feature called Sorting & Grouping in a report. Since you have the count in your report's record source now, simply select that field and add a Sort based on the field. Make sure in the sort list it's the first item.

 

[Kath]

I do have the report sorted by Group 'Service Category' - it sorts in alphabetical order but I want it to sort using the totals for each 'Service Category' - in descending order...

 

[vbaInet]

Kath please focus. Please re-read the last step and understand that I said you should sort by the count field you've just created. You said you've created the query to do the count and you've joined this query to your report's record source, if the field is there sort on it.

 

[Kath]

OK, I''ll try. I'm confusing myself I think

 

[Kath]

I know why I'm having such a hard time. I'm explaining myself wrong I think. I have one field called 'Service Category' which I'm using a query to count when 'SRT' appears for each of the Service Categories in a query . I use 'count(*)' in the report under a title '# of SRT's" which gives me the total amount of times an SRT was had for each of the multiple Service Categories. So that 'count(*)' field in the report is what I'm actually looking to sort in descending order....

 

[vbaInet]

And you will continue to confuse yourself if you continue to explain the same thing over and over again.

I've already told you what you need to do. Get the count in your report's Record Source and sort it. This I've explained several times. A field MUST exist in the underlying record source of a report for you to be able to sort on it.

 

[Kath]

Alrightey; I'll go 'back to the drawing board' - - thanks again, esp for your patience!!

 

[vbaInet]

Take some time to digest what I've told you and if you're still struggling upload a cut-down version of your db.

 

[Kath]

I'm hoping I didn't use up all of your patience!! I ended up creating 2 queries (2nd one uses 1st as a source) because the 'count' field was always returning a '1' and I couldn't sort on the =sum box. So I have 1 query that has all the info needed for the report, then the 2nd query does a 'sum' of the count field in the first. Both return correct info. When I go to use the 2nd query's 'sum' field as a control on the report the name of the dataview column doesn't come up to select, and the name that does come up I can't find anywhere!! I feel like I'm making some progress, at least I have a query that works to get a sum!

 

[Kath]

Well, I was able to get that field to show up in my report, but now only the calculated fields from query1 and query2 show up to select as controls; and in Query 2 when I drop down the fields from Query 1 (to use in report) - the Sum calculation goes back to showing a 1 instead of the correct, summed, calculation.

 

[vbaInet]

Let's see your db.

 

[Kath]

How do I upload the database for you to see? or just attach to the email you sent?