SELECT from multiple tables with GROUP BY

Question

I've a got a little problem with multitable query. (RDBMS: Access)

Here is the database schema: (only S_Cards, Books, Authors, Students tables are used in this query) S_Cards is Student book order (in library).

Query: Select the most popular author(s) among students and the number of this author's books, which were ordered in library.

Although I can get list of orders + authors like this in one query:

SELECT 
    Students.FirstName & " " & Students.LastName AS [Student], 
    Books.Name AS [Book], Authors.FirstName & " " & Authors.LastName AS [Author]
FROM 
    Students, 
    Books, 
    S_Cards, 
    Authors
WHERE 
    S_Cards.ID_Student = Students.ID 
AND S_Cards.ID_Book = Books.ID 
AND Books.ID_Author = Authors.ID
ORDER BY Authors.LastName

Result (sorry, it's in Russian):

I can't figure out, why I can't COUNT and GROUP BY like this:

SELECT 
    Students.FirstName & " " & Students.LastName AS [Student], 
    Books.Name AS [Book], 
    COUNT(Authors.FirstName & " " & Authors.LastName) AS [Number of books]
FROM Students, Books, S_Cards, Authors
WHERE 
    S_Cards.ID_Student = Students.ID 
AND S_Cards.ID_Book = Books.ID 
AND Books.ID_Author = Authors.ID
GROUP BY 3

I get an error that 'Authors.FirstName & " " & Authors.LastName' is not a part of static function or group.

Questions:

1. Is there a way to do this query without JOIN, only by GROUP BY, SELECT, UNION and how?
2. What's the problem in my 2nd query?

Solution

Solution (extracted from older revision in question):

SELECT TOP 1 Author, COUNT(Book) AS [Number of books] FROM
(
    SELECT 
        Students.FirstName & " " & Students.LastName AS [Student], 
        Books.Name AS [Book], 
        Authors.FirstName & " " & Authors.LastName AS [Author]
    FROM 
        Students, 
        Books, 
        S_Cards, 
        Authors
    WHERE 
        S_Cards.ID_Student = Students.ID AND
        S_Cards.ID_Book = Books.ID AND
        Books.ID_Author = Authors.ID
    ORDER BY Authors.LastName
)
GROUP BY Author
ORDER BY 2 DESC

Comments

[Ben]

This assumes you're using SQL Server (it looks like it). Please always tag questions with the appropriate RDBMS in future.

[Aremyst]

Thanks for reply. I think COUNT(Books.ID) would count number of times some book was ordered. I need to count how many times any books by the author was ordered, not some particular book. RDBMS is Access.