[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How do I sort/order a group by count in Access?

Posted on 2011-03-22
14
Medium Priority
?
1,512 Views
Last Modified: 2013-11-30
Hi.

Im using Access 2010, working in an ADP-file. I have extracted some tables from a sql-database. Then I have made a View where want to extract som data. What I have to do is make a view of number of students and years grouped by year and sorted by count of student each year but it doesnt work.  

If you look at the View I have made you can see that it is grouped by Immaar (means year) but not sorted by Count in descending order (it is count of studentID. Each student has his/her own ID).

What i want to have in view is this:

2007 6
2008 6
2005 4
2006 4
2002 2
2004 2
2009 1

My code is this:

SELECT      TOP 100 PERCENT Immaar, COUNT(StudentID) AS Number
FROM          dbo.Student
GROUP BY Immaar
ORDER BY COUNT(StudentID) DESC



Screenshot.jpg
0
Comment
Question by:Hans_Christian
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +2
14 Comments
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35194524
Hi,
 
Try this

SELECT      TOP 100 PERCENT Immaar, COUNT(StudentID) AS Number
FROM          dbo.Student
GROUP BY Immaar
ORDER BY 2 DESC
0
 

Author Comment

by:Hans_Christian
ID: 35194756
That didn't do it. The result order is the exact same as the screenshot above. What happens is that Access automatically changes the "2" back to "COUNT(StudentID)" when you click outside the code.
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35194794
weird, ok try this

SELECT Immaar, Number FROM (
SELECT      TOP 100 PERCENT Immaar, COUNT(StudentID) AS Number
FROM          dbo.Student
GROUP BY Immaar
) AS DATA
ORDER BY Number DESC
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Hans_Christian
ID: 35194838
Nothing happens. The sort order is still the same. The code changes to this, when clicking outside the code-area:

SELECT      TOP 100 PERCENT Immaar, Number
FROM          (SELECT      TOP 100 PERCENT Immaar, COUNT(StudentID) AS Number
                        FROM           dbo.Student
                        GROUP BY Immaar) DATA
ORDER BY Number DESC


It added "TOP 100 PERCENT" in the top and changed "AS DATA" to "DATA". I can see what your intentions were. Putting a table around the original. But it didnt do it either.
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35194906
ok, my bad I have not read that this is a View, lets try this one

SELECT * FROM (
SELECT      TOP 99 PERCENT Immaar, COUNT(StudentID) AS Number
FROM          dbo.Student
GROUP BY Immaar
GROUP BY COUNT(StudentID) DESC
) AS DATA

0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35194911
Sorry again the second  GROUP BY  is actually a SORT BY
0
 

Author Comment

by:Hans_Christian
ID: 35194968
Now I get an error message. Error in group by clause. Im not sure it will accept to group by Count(StudentID). I tried that earlier with no luck. And now its sudden 99 Percent and not 100?

The code changes to this when clicking outside after pasting your code:

SELECT      *
FROM          (SELECT      TOP 99 PERCENT Immaar, COUNT(StudentID) AS Number
                        FROM           dbo.Student
                        GROUP BY Immaar
                        GROUP BY COUNT(StudentID) DESC) AS DATA

0
 
LVL 4

Expert Comment

by:Ztinel
ID: 35195512
try this:
SELECT      TOP (99.999999) PERCENT Immaar, COUNT(StudentID) AS Number
FROM          dbo.Student
GROUP BY Immaar
ORDER BY COUNT(StudentID) DESC

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35195521
This works fine for me, it produces the exact output you requrested
...or am I missing something...

SELECT Student.Immaar, Count(Student.StudentID) AS [Number]
FROM Student
GROUP BY Student.Immaar, Student.Immaar
ORDER BY Count(Student.StudentID) DESC , Student.Immaar;

JeffCoachman
0
 
LVL 4

Assisted Solution

by:Ztinel
Ztinel earned 332 total points
ID: 35195576
0
 
LVL 13

Accepted Solution

by:
gamarrojgq earned 336 total points
ID: 35195843
Sorry fot that, the second  GROUP BY  is actually a SORT BY, so it will be

SELECT * FROM (
SELECT      TOP 99 PERCENT Immaar, COUNT(StudentID) AS Number
FROM          dbo.Student
GROUP BY Immaar
ORDER BY COUNT(StudentID) DESC
) AS DATA

Yes try it with 99, this  is a workaround
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35198671
If this is SQL Server you should not be using TOP 100 PERCENT ... ORDER BY it is simply pointless.  SQL Server is smart enough to see that you want all the rows and skip the sort entirely.

And if it is not SQL Server than perhaps you should not be posting in this zone...
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 332 total points
ID: 35198757
Here is what you need to do:
Create your VIEW without an ORDER BY clause.
Execute it with the ORDER BY clause.

Here are the steps:
Do not use Design mode, use the Query window instead.
Step 1. Create your VIEW

CREATE VIEW vw_MyViewNameGoesHere

As

SELECT  Immaar,
        COUNT(*) Number
FROM    dbo.Student
GROUP BY Immaar

Step 2.  Execute it as follows:

SELECT Immaar, Number
FROM vw_MyViewNameGoesHere
ORDER BY Number
0
 

Author Comment

by:Hans_Christian
ID: 35201911
gamarrojqq: That did it! Thanks. The workaround.

Ztinel: Thanks for the link read it with interest.

acperkins. Thanks for that information.

0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question