Mi sembrava troppo facile !!!
in effetti sono più query elementari l'una dentro l'altra

Prova cosi dovrebbe funzionare.
codice:
SELECT A.auto AS q6Au, qq5.q3co
FROM (SELECT qq3.q3id, qq3.q3co FROM (SELECT B.id AS q3id, B.colore AS q3co, (nz([preferito],0)) AS cp FROM B)  AS qq3 INNER JOIN (SELECT B.id AS q2id, (Max(nz([preferito],0))) AS mcp FROM B GROUP BY B.id)  AS qq2 ON (qq3.cp=qq2.mcp) AND (qq3.q3id=qq2.q2id))  AS qq5 INNER JOIN A ON qq5.q3id=A.id;