Vfrdtyky

The thing is, I'm trying to create a table that displays only the max number of votes to a given candidate. For that, it was needed to combine three different tables (this ones are in spanish) Sedes, Candidatos and Voto.
I've got to the point where executing the followning lines, gives me the list with all the total number of votes per candidate per stablishment:

SELECT sedes.nombresede, candidatos.apellido & ", " & candidatos.nombre AS Candidato, count(voto.numcandidato) AS TOTAL

From (Voto INNER JOIN Sedes ON Sedes.sede_id=Voto.sede_id) 

INNER JOIN Candidatos ON Voto.numcandidato=Candidatos.numcandidato

GROUP BY nombresede, candidatos.apellido & ", " & candidatos.nombre

Now this is not my end product, what I need is for the list to only show me the candidate in each establishment that's got the maximum ammount of votes. I tried doing this:

SELECT sedes.nombresede, candidatos.apellido & ", " & candidatos.nombre AS Candidato, count(voto.numcandidato) AS TOTAL

From (Voto INNER JOIN Sedes ON Sedes.sede_id=Voto.sede_id) 

INNER JOIN Candidatos ON Voto.numcandidato=Candidatos.numcandidato

GROUP BY nombresede, candidatos.apellido & ", " & candidatos.nombre

HAVING max(TOTAL)

But when executing, access prompts me to give a value to TOTAL like when using a parameters clause. As you can see, I also ran into trouble when trying to use Candidato inside the GROUP BY and worked around it by copying the exact expression used in the SELECT clause. Maybe this has something to do with the other problem?

Many, many thanks for all the help!

Consider running an aggregate then a correlated subquery for a ranking variable of candidates under each establishment. Then, pick all records with rank = 1:

Helper Query (save as a query object to be referenced below)

SELECT s.nombresede, c.apellido & ', ' & c.nombre AS Candidato, 

       COUNT(voto.numcandidato) AS TOTAL

FROM (Voto v 

INNER JOIN Sedes s ON s.sede_id = v.sede_id) 

INNER JOIN Candidatos c ON c.numcandidato = v.numcandidato

GROUP BY s.nombresede, c.apellido & ', ' & c.nombre

Final Query

SELECT m.nombresede, m.Candidato, m.[rank]

FROM

   (SELECT q.nombresede, q.Candidato,

          (SELECT COUNT(*)

           FROM myHelperQuery sub

           WHERE sub.nombresede = q.nombresede

             AND sub.TOTAL >= q.TOTAL) AS [rank]

    FROM myHelperQuery q

   ) AS m

WHERE m.[rank] = 1

Your TOTAL is not defined. Its not possible to use an Alias this way. So the Max() is trying to evaluate but Access doesnt know what your TOTAL is, so it asks for an input. This one should do the trick:

SELECT sedes.nombresede, candidatos.apellido & ", " & candidatos.nombre AS Candidato, count(voto.numcandidato) AS total, Max(voto.numcandidato) AS maximum

From (Voto INNER JOIN Sedes ON Sedes.sede_id=Voto.sede_id) 

INNER JOIN Candidatos ON Voto.numcandidato=Candidatos.numcandidato

GROUP BY nombresede, candidatos.apellido & ", " & candidatos.nombre