Having problems converting MS Access Query to mySQL

SELECT jos_users.name, 
jos_users.username, 
jos_users.email, 
Max(IIf([jos_community_fields_values.field_id]=20,[jos_community_fields_values.value],Null)) AS Company, 
Max(IIf([jos_community_fields_values.field_id]=13,[jos_community_fields_values.value],Null)) AS [Company Website], 
Max(IIf([jos_community_fields_values.field_id]=19,[jos_community_fields_values.value],Null)) AS [Job Title], 
Max(IIf([jos_community_fields_values.field_id]=9,[jos_community_fields_values.value],Null)) AS [Address 1], 
Max(IIf([jos_community_fields_values.field_id]=25,[jos_community_fields_values.value],Null)) AS [Address 2], 
Max(IIf([jos_community_fields_values.field_id]=11,[jos_community_fields_values.value],Null)) AS City, 
Max(IIf([jos_community_fields_values.field_id]=10,[jos_community_fields_values.value],Null)) AS State, 
Max([EMEA by IMT].Country) AS MaxOfCountry, 
Max([EMEA by IMT].IMT) AS MaxOfIMT, 
Max(IIf([jos_community_fields_values.field_id]=26,[jos_community_fields_values.value],Null)) AS Zip, 
jos_users.registerDate, jos_users.lastvisitDate
FROM (jos_users INNER JOIN (jos_community_fields INNER JOIN jos_community_fields_values ON jos_community_fields.id = jos_community_fields_values.field_id) ON jos_users.id = jos_community_fields_values.user_id) LEFT JOIN [EMEA by IMT] ON jos_community_fields_values.value = [EMEA by IMT].Country
GROUP BY jos_users.name, jos_users.username, jos_users.email, jos_users.registerDate, jos_users.lastvisitDate
HAVING (((jos_users.email) Like "*email.com") AND ((Max(IIf([jos_community_fields_values.field_id]=12,[jos_community_fields_values.value]=[EMEA by IMT.Country],Null)))=-1));

SET SESSION SQL_BIG_SELECTS=1;
SELECT a.id as `ID`,
a.name as `Name`,
a.username as `Username`,
a.email as `Email`,
MAX(IF(b.field_id=20,b.value, NULL)) AS 'Company',
MAX(IF(b.field_id=13,b.value, NULL)) AS 'Website',
MAX(IF(b.field_id=19,b.value, NULL)) AS 'Job Title',
MAX(IF(b.field_id=9,b.value, NULL)) AS 'Address',
MAX(IF(b.field_id=25,b.value, NULL)) AS 'Address 2',
MAX(IF(b.field_id=11,b.value, NULL)) AS 'City / Town',
MAX(IF(b.field_id=10,b.value, NULL)) AS 'State',
MAX(IF(b.field_id=26,b.value, NULL)) AS 'Zip / Country Code',
c.country AS 'Country',
a.registerDate as `Register`,
a.lastvisitDate as `Last Visit`
FROM jos_users a 
LEFT JOIN jos_community_fields_values b ON a.id = b.user_id
LEFT JOIN jos_emea c ON b.value = c.country
WHERE ((a.email) like "%email.com")
GROUP BY a.id
ORDER BY a.name, a.id

SELECT a.id 
     , a.name 
     , a.username 
     , a.email
     , MAX(CASE WHEN b.field_id=20 THEN b.value END) AS 'Company'
     , MAX(CASE WHEN b.field_id=13 THEN b.value END) AS 'Website'
     , MAX(CASE WHEN b.field_id=19 THEN b.value END) AS 'Job Title'
     , MAX(CASE WHEN b.field_id=9  THEN b.value END) AS 'Address'
     , MAX(CASE WHEN b.field_id=25 THEN b.value END) AS 'Address 2'
     , MAX(CASE WHEN b.field_id=11 THEN b.value END) AS 'City / Town'
     , MAX(CASE WHEN b.field_id=10 THEN b.value END) AS 'State'
     , MAX(CASE WHEN b.field_id=26 THEN b.value END) AS 'Zip / Country Code'
     , c.country 
     , a.registerDate AS `Register`
     , a.lastvisitDate AS `Last Visit`
  FROM jos_users a 
LEFT OUTER
  JOIN jos_community_fields_values b 
    ON b.user_id = a.id 
LEFT OUTER
  JOIN jos_emea c 
    ON c.country = b.value
 WHERE a.email LIKE '%email.com'
GROUP 
    BY a.id
ORDER 
    BY a.name
     , a.id

LEFT OUTER
  JOIN jos_emea c 
    ON c.country = b.value

INNER
  JOIN jos_emea c 
    ON c.country = b.value