Access: WHERE IN AND NOT IN on same column

MS Access is usually far from standard compliant. The following is the simple query in standard SQL and runs in about every RDBMS:

SELECT *
FROM animal 
WHERE id_animal IN (SELECT id_animal FROM competition WHERE name = 'A')
  AND id_animal NOT IN (SELECT id_animal FROM competition WHERE name = 'B');

[NOT] IN works on lists. It makes little sense to use it on single-item lists. name IN ("A") is the same as name = "A". As you want to look up the animal list in the competition table, you need a subquery as shown above.

As you see, I don't join. Why should I? I want to select animals, so I select from the animal table. I want to restrict the result to certain animals, so I use a WHERE clause.

I notice you are using double quotes for string literals. If this is necessary in MS Access, then replace the single quotes above with double quotes. (In standard SQL double quotes delimit names, not string literals, but this may be different in MS Access.)