Order by Count of matching records 1

[HowardMarks]

Hi all

This is one that always gets me and I can never work out how to achieve it simply. I wonder if someone could post a simple rule to achieve the following result with a SQL query against an Access database.

I wish to return a list of PROMOTERS, but ordered by the number of matching REQUESTS from another table.

For example this is what I have, but it won't work:

SELECT * FROM PROMOTERS, COUNT(SELECT * FROM REQUESTS WHERE REQUESTS.REQUEST_PROMOTERID = PROMOTERS.PROMOTER_ID) As TotalRequests ORDER BY TotalRequests

Could someone point out where I am going wrong, and I should go about this? It seems like it should be a relatively simple operation.

Many thanks

Nick





Nick (Webmaster)

http://www.npfx.com

http://www.artswales.org

 

[PHV]

A starting point:
SELECT P.PROMOTER_ID, P.Field, P.AnotherField, Count(*) As TotalRequests
FROM PROMOTERS AS P INNER JOIN REQUESTS AS R ON P.PROMOTER_ID = P.REQUEST_PROMOTERID
GROUP BY P.PROMOTER_ID, P.Field, P.AnotherField
ORDER BY Count(*)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HowardMarks]

Thanks very much PH, that's just the ticket.

Nick (Webmaster)

http://www.npfx.com

http://www.artswales.org