Sorting by first letter, but not really

[ezgoer89]

I've searched and searched and cannot find a solution to my
challenge. I have created reports in Access using SQL and currently
have the records sorted by LastName. Each of these participants comes
from a particular Organization. I have been asked to list the
participants in an order that rotates through the Organization.

For instance:

Lastname Organization

Smith ABC
Jones ABC
Adams GHI
Meyer DEF
Bell ABC
Manning DEF

As I said, previously, the sort was easy and was based strictly on
last name. Now, the request is to list the participants in "rotating
ascending order". So, for this simple example the proper report would
appear as:

Lastname Organization

Smith ABC
Meyer DEF
Adams GHI
Jones ABC
Manning DEF
Bell ABC

Basically, it is sorting, but looping through the list and picking the
first instance of each letter, then going back and doing it again
until all records are listed... any thoughts?

 

[John Spencer (MVP)]

IF the list of persons is not too large you might try a ranking query to
establish the order within the organization and then sort by rank and
organization in the report.

SELECT A.LastName, A.Organization
, Count(B.LastName) as Rank
FROM SomeTable as A INNER JOIN SomeTable as B
ON A.Organization = B.Organization
AND A.LastName <= B.LastName
GROUP BY A.LastName, A.Organization

As long as the names within the Organization were unique this would probably
work. HOWEVER if you have 2 Smiths in the same organization you would end up
with one record for the two Smiths (in this simple example).

Lastname Organization

Smith ABC
Meyer DEF
Adams GHI
Jones ABC
Manning DEF
Bell ABC


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Michel Walsh]

Rank the names by organization:


LName Org Rank
------- ------ -------
Smith ABC 1
Jones ABC 2
Bell ABC 3
Meyer DEF 1
Manning DEF 2
Adams GHI 1


then

ORDER BY rank ASC, lastName DESC


should do the trick.


To rank, if there is no dup (in a given organization), someone can use:

SELECT a.LastName, a.Organization, COUNT(*) AS rank
FROM tableName AS a INNER JOIN tableName AS b
ON a.organization = b.organization
AND a.LastName <= b.LastName
GROUP BY a.LastName, a.Organization


so, the whole query:


SELECT a.LastName, a.Organization
FROM tableName AS a INNER JOIN tableName AS b
ON a.organization = b.organization
AND a.LastName <= b.LastName
GROUP BY a.LastName, a.Organization
ORDER BY COUNT(*) ASC, a.LastName DESC




Vanderghast, Access MVP

 

[Dale Fye]

Shouldn't that be:

ORDER BY Count(*) ASC, a.Organization

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Michel Walsh]

Yep, indeed.


Vanderghast, Access MVP

 

[Dale Fye]

BTW,

You might want to consider using an alias when posting to newsgroups. There
are web bots that scour these newsgroups for valid email addresses.