Sort =count funtion in footer???

[ktariske]

I bet this has been asked before, can't find anything with search.

Can you sort the count function in a footer? I have added the count function and get the correct total for my report. In this case employee, total number of visitors on campus. Now I want to sort the report by number of total visitors so the employee with the most visitors starts at the top of the report. Is there a way to specify in the =count(*) syntax that can sort ascending order?

Or do i need to create this in the query and have it become a database object. But then if so, how do you do that?

Thanks,

Kriss.

 

[dkinley]

I am not sure what you mean by sorting a count function? The purpose of the sorting is for user presentation only. If I have one count() going on in a report it is usually counting the primary key field so sorting is irrelevant and should return the correct value every time.

More clarification?

-dK

 

[ktariske]

Thanks for the reply. I love this forum.

I have a query that gives me a nice 7000 record return with the info I need. I created the report, but alas it's 500 pages. It contains 4 fields, Employee name, visitor name, date, location. The report, being 400 pages was a bit much. So i removed the details, and added the employee name field and a count function to count total number of visitors per employee in the footer.

Works great. Now only 18 pages. Its still sorted by employee name. but now the boss man wants it sorted by total number of visitors, which was created with my =count(*). That make sense?

Now:
Amanda Brown 10 Visitors
David Applegate 14 Visitors
Larry Smith 2 Visitors, etc
He wants:
David Applegate 14 visitors
Amanda Brown 10 visitors
Larry Smith 2 Visitors, etc

 

[dkinley]

Ah .. okay. There are a couple of approaches to this, but I am thinking of the easiest implementation the first time around.

For your count, set it it to pick up some field that is present in all of the reports that you will want to sort, hence why I set mine to count the ID of the record. Now, instead of sorting in the query - use the reports ability to sort the record. You can set the OrderBy property on the reports Open() event.

I am not sure how fancy your set up is, but suppose the user selected to print the report using a command button on a form. Suppose you had a combo box on the form that the user chose the sort order prior to opening.

Sub Report_Open()
     Me.OrderBy = Forms("YourFormName").SomeField
     Me.OrderByOn
End Sub

Note, that if you are using grouping levels on the report then that will not work intended.

Another option is to create one query and then several reports and set the sort order for each report ... rptReportName_SortedByLastName.

Hope that helps,
-dK

EDIT: After rereading your post. Is the visitor a sum and not a count of a subtable?

 

[ktariske]

Ok lets start over. I have a Query pulling records from a VISIT table. It comes back with:

Visitor Name Date Employee Name
Greg Johnson 4/20/2008 Greg Meeting
Ann Test 11/20/2008 Jim Test
Brian Timor 04/12/2008 Ellen Same
Joe Shmow 1/15/2008 Test Test
Jim Test 1/14/2008 Test Test

Nicely sorted by employee name:

Is there a way to create in the query total number of visitors per employee?? Then I could use that field to sort on in the report..like Test Test had 2, etc.

Because ultimately I'd like the report to show: employee name: number of visitors: (And have this start with the most: 21, 20, 19, 18, etc.

I can currently do that with a footer, but then I can't sort it by accending order in the report. It still displays by employee from from the criteria in the query.

 

[dkinley]

The short answer is, yes, you can. I've whipped up a quick demo to avoid confusion in what you want and what I offered.

Open up fSomeForm - here I have 'Normal' and 'Advanced'. Each are built on different reports and on the same query, but each has their own drawbacks in this particular application.

I say 'normal' what I mean is the usual method but everyone's normal is different. This one does the counting in the query (and sorting in the query instead of the report). The bonus is that it is easy to get entire report total - the drawback is a different report for the same information if you have need to provide differently sorted reports for users. Note the sorting in the query. For this particular instance, it sorts the count first and then the name (because the count is a more left column in the QBE).

The 'advanced' uses what I proposed in earlier posts. This trick is more often used (in my cases) so the user can select different fields to sort on without the need to build extra reports (and/or queries). The drawback is this style has problems sometimes when grouping.

Hope that helps ...
-dK