You probably can do this through Access GUI query designer, but I don't know how (I don't use Access much). If you right click in the query designer, the menu lets you choose SQL view, where you can directly edit the SQL query.
Should you wish to do it that way (I would), the SQL keywords and functions you should look up up are JOIN, COUNT(), GROUP BY, ORDER BY and possibly HAVING. You should keep in mind that you can join the same table in a query more than once, if needed. All of these are features of SELECT query and documented
here.
