Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2014

    Unanswered: Convert MS Access query sql to mySQL

    Hi guys! Just want to seek some initial help how to convert my Access query to mySQL or is there an online website that can convert all these stuffs? Im having series of errors due to incompatibility of built-in functions between the 2 databases. Immediate response will be highly appreciated. Thank you.

    SELECT qryUserContract_Active.UserContractID, qryUserContract_Active.UserID, qryUserContract_Active.DateTo, qryUserContract_Active.AgentID, qryUserContract_Active.Name, qryUserContract_Active.ContractCode, IIf(qryUC_1.[ContractNumber]>15,qryUC_1.Name,IIf(qryUC_2.[ContractNumber]>15,qryUC_2.Name,IIf(qryUC_3.[ContractNumber]>15,qryUC_3.Name,IIf(qryUC_4.[ContractNumber]>15,qryUC_4.Name,IIf(qryUC_5.[ContractNumber]>15,qryUC_5.Name,IIf(qryUC_6.[ContractNumber]>15,qryUC_6.Name,IIf(qryUC_7.[ContractNumber]>15,qryUC_7.Name,IIf(qryUC_8.[ContractNumber]>15,qryUC_8.Name,IIf(qryUC_9.[ContractNumber]>15,qryUC_9.Name,IIf(qryUC_10.[ContractNumber]>15,qryUC_10.Name,"Err:Linkage Issue")))))))))) AS UplineVP, IIf(qryUC_1.[ContractNumber]>15,qryUC_1.UserID,IIf(qryUC_2.[ContractNumber]>15,qryUC_2.UserID,IIf(qryUC_3.[ContractNumber]>15,qryUC_3.UserID,IIf(qryUC_4.[ContractNumber]>15,qryUC_4.UserID,IIf(qryUC_5.[ContractNumber]>15,qryUC_5.UserID,IIf(qryUC_6.[ContractNumber]>15,qryUC_6.UserID,IIf(qryUC_7.[ContractNumber]>15,qryUC_7.UserID,IIf(qryUC_8.[ContractNumber]>15,qryUC_8.UserID,IIf(qryUC_9.[ContractNumber]>15,qryUC_9.UserID,IIf(qryUC_10.[ContractNumber]>15,qryUC_10.UserID,"Err:Linkage Issue")))))))))) AS UnderVPUserID, "1:" AS UA1, qryUC_1.Name, qryUC_1.ContractCode, "2:" AS UA2, qryUC_2.Name, qryUC_2.ContractCode, "3:" AS UA3, qryUC_3.Name, qryUC_3.ContractCode, "4:" AS UA4, qryUC_4.Name, qryUC_4.ContractCode, "5:" AS UA5, qryUC_5.Name, qryUC_5.ContractCode, "6:" AS UA6, qryUC_6.Name, qryUC_6.ContractCode, "7:" AS UA7, qryUC_7.Name, qryUC_7.ContractCode, "8:" AS UA8, qryUC_8.Name, qryUC_8.ContractCode, "9:" AS UA9, qryUC_9.Name, qryUC_9.ContractCode, "10:" AS UA10, qryUC_10.Name, qryUC_10.ContractCode, "UserID=" & [qryUserContract_Active].[UserID] & IIf([qryUC_1].[UserID] Is Not Null," UserID=" & [qryUC_1].[UserID]) & " OR " & IIf([qryUC_2].[UserID] Is Not Null," OR UserID=" & [qryUC_2].[UserID]) & IIf([qryUC_3].[UserID] Is Not Null," OR UserID=" & [qryUC_3].[UserID]) & IIf([qryUC_4].[UserID] Is Not Null," OR UserID=" & [qryUC_4].[UserID]) & IIf([qryUC_5].[UserID] Is Not Null," OR UserID=" & [qryUC_5].[UserID]) & IIf([qryUC_6].[UserID] Is Not Null," OR UserID=" & [qryUC_6].[UserID]) & IIf([qryUC_7].[UserID] Is Not Null," OR UserID=" & [qryUC_7].[UserID]) & IIf([qryUC_8].[UserID] Is Not Null," OR UserID=" & [qryUC_8].[UserID]) & IIf([qryUC_9].[UserID] Is Not Null," OR UserID=" & [qryUC_9].[UserID]) & IIf([qryUC_10].[UserID] Is Not Null," OR UserID=" & [qryUC_10].[UserID]) AS Query_Upline, "" AS Query_Baseshop, "" AS Query_Superbase, "" AS Query_Superteam
    FROM ((((qryUserContract_Active LEFT JOIN (((((qryUserContract_Active AS qryUC_1 LEFT JOIN qryUserContract_Active AS qryUC_2 ON qryUC_1.UnderUserID = qryUC_2.UserID) LEFT JOIN qryUserContract_Active AS qryUC_3 ON qryUC_2.UnderUserID = qryUC_3.UserID) LEFT JOIN qryUserContract_Active AS qryUC_4 ON qryUC_3.UnderUserID = qryUC_4.UserID) LEFT JOIN qryUserContract_Active AS qryUC_5 ON qryUC_4.UnderUserID = qryUC_5.UserID) LEFT JOIN qryUserContract_Active AS qryUC_6 ON qryUC_5.UnderUserID = qryUC_6.UserID) ON qryUserContract_Active.UnderUserID = qryUC_1.UserID) LEFT JOIN qryUserContract_Active AS qryUC_7 ON qryUC_6.UnderUserID = qryUC_7.UserID) LEFT JOIN qryUserContract_Active AS qryUC_8 ON qryUC_7.UnderUserID = qryUC_8.UserID) LEFT JOIN qryUserContract_Active AS qryUC_9 ON qryUC_8.UnderUserID = qryUC_9.UserID) LEFT JOIN qryUserContract_Active AS qryUC_10 ON qryUC_9.UnderUserID = qryUC_10.UserID
    WHERE (((qryUserContract_Active.DateTo) Is Null));

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    MySQL doesn't support the Access IIF statement. instead mySQL uses Case

    Access also is very keen on using brackets.

    Id also suggest using table aliases to reduce the amount of verbiage in your query
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts