Convertir MY SQL dans Access - Access

Convertir MY SQL dans Access De my sql à access (Résolu) » Forum - Webmaster De my sql vers access » Forum - Programmation Choisir Access ou My Sql ? sinon free JDBC » Forum - Access Connecter access à my sql » Forum - Bases de données My SQL - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) » Conseils pratiques - MySQL

Bonjour à tous,

J'aimerais adapter ma base créée dans My SQL Server à Access. N'étant pas du tout un expert dans ce domaine, je me permet de vous demander de l'aide.
A chaque étape d'avancement dans Access je rencontre des problèmes de syntaxe qui est légèrement différent à My SQL Server (les parenthèses, IFF au lieu de case when, mais mon grande problème ce sont les parenthèses! je ne comprends pas à quel moment les introduire, etc.)
Aurez qqun la gentillesse de convertir mon code My SQL dans Access svp?

Le code est basé sur plusieurs tables "tirées" de ERP. J''utilise Access 2007

Merci d'avance!

 SELECT /*Information générale commande d'achat*/ DATA0023.SUPPLIER_NAME as 'Fournisseur', DATA0070.PO_NUMBER as 'Numéro PO', DATA0070.PO_DATE as 'Date de création PO', YEAR (DATA0070.PO_DATE) as 'Année (date PO)', CASE WHEN MONTH(DATA0070.PO_DATE) = 1 THEN 'Janvier'   WHEN MONTH(DATA0070.PO_DATE) = 2 THEN 'Février'   WHEN MONTH(DATA0070.PO_DATE) = 3 THEN 'Mars'   WHEN MONTH(DATA0070.PO_DATE) = 4 THEN 'Avril'   WHEN MONTH(DATA0070.PO_DATE) = 5 THEN 'Mai'   WHEN MONTH(DATA0070.PO_DATE) = 6 THEN 'Juin'   WHEN MONTH(DATA0070.PO_DATE) = 7 THEN 'Juillet'   WHEN MONTH(DATA0070.PO_DATE) = 8 THEN 'Août'   WHEN MONTH(DATA0070.PO_DATE) = 9 THEN 'Septembre'   WHEN MONTH(DATA0070.PO_DATE) = 10 THEN 'Octobre'   WHEN MONTH(DATA0070.PO_DATE) = 11 THEN 'Novembre'   WHEN MONTH(DATA0070.PO_DATE) = 12 THEN 'Décembre'   ELSE'-'   END as 'Mois (date PO)', /*Information générale Standard PO*/ CASE WHEN DATA0017.INV_PART_NUMBER IS NOT NULL THEN DATA0017.INV_PART_NUMBER   ELSE DATA0072.DESCRIPTION    END as 'Article ST_MiSC', CASE WHEN DATA0017.INV_PART_DESCRIPTION IS NOT NULL THEN DATA0017.INV_PART_DESCRIPTION   ELSE DATA0072.DESCRIPTION2    END as 'Description article ST_MiSC', CASE WHEN DATA0071.REQ_DATE IS NOT NULL THEN DATA0071.REQ_DATE    ELSE DATA0072.DEL_DATE    END as 'Cicor delivery date ST_MiSC', DATA0071.DEL_DATE as 'Requested ship date ST', CASE WHEN DATA0071.QUAN_ORD IS NOT NULL THEN DATA0071.QUAN_ORD   ELSE DATA0072.QUAN_ORD    END as 'Qté commandée ST_MiSC', DATA0022.QUANTITY as 'Qté réceptionnée 1 (22)ST', DATA0022.TDATE as 'Date réception 1 (22) ST', CASE WHEN DATA0022.TTYPE=2 THEN 'from PO'   ELSE '-'   END as 'Type de transaction (22) ST', CASE WHEN DATA0206.QUANTITY IS NOT NULL THEN DATA0206.QUANTITY   ELSE DATA0235.QUAN_RECD    END as 'Qté réceptionnée 2 (206) ST_MiSC', CASE WHEN DATA0206.TRANSACTION_DATE IS NOT NULL THEN DATA0206.TRANSACTION_DATE    ELSE DATA0235.DATE_RECD    END as 'Date réception 2 (206) ST_MiSC', CASE WHEN YEAR (DATA0206.TRANSACTION_DATE) IS NOT NULL THEN YEAR (DATA0206.TRANSACTION_DATE)   ELSE YEAR (DATA0235.DATE_RECD)    END as 'Année (date de récéption) ST_MiSC', CASE   WHEN DATA0070.STATUS = 1 THEN 'active'  WHEN DATA0070.STATUS = 2 THEN 'closed'  WHEN DATA0070.STATUS = 3 THEN 'cancelled'  WHEN DATA0070.STATUS = 4 THEN 'reserved'  WHEN DATA0070.STATUS = 5 THEN 'on hold'  WHEN DATA0070.STATUS = 6 THEN 'completed'  END as 'Status PO', DATA0028.LEAD_TIME as 'Manufacturing lead time ST (en jours)', DATA0024.SHIPPING_LEAD_TIME as 'Shipping lead time (en jours)', DATA0024.FOB as 'Incoterm', CASE   WHEN DATA0070.PO_TYPE = 0 THEN 'standard PO'  WHEN DATA0070.PO_TYPE = 1 THEN 'miscellaneous PO'  ELSE '-'  END as 'Type PO', CASE WHEN DATA0023.ANALYSIS_CODE1='1' THEN 'Stratégique'   WHEN DATA0023.ANALYSIS_CODE1='2' THEN 'Non-stratégique'                  ELSE'-'   END as 'Classification Fournisseur', CASE WHEN DATA0023.SUPPLIER_NAME='HOFSTETTER PCB AG'   OR DATA0023.SUPPLIER_NAME='MECANOR SA'  OR DATA0023.SUPPLIER_NAME='ROLF HÄNGGI'  OR DATA0023.SUPPLIER_NAME='COLLINI-FLÜHMANN AG'  OR DATA0023.SUPPLIER_NAME='KAUFMANN ETAMPES SA'  OR DATA0023.SUPPLIER_NAME='GALVAMETAL AG  ESTOPPEY-REBER'  OR DATA0023.SUPPLIER_NAME='MICROCONTACT AG'  OR DATA0023.SUPPLIER_NAME='ROBERT LAMINAGE'  OR DATA0023.SUPPLIER_NAME='CPPCF HERMINJARD'   OR DATA0023.SUPPLIER_NAME='HYBRID SA'  THEN 'Sous-traitant'  ELSE 'Fournisseur'  END as 'Type de fournisseur', DATA0070.ANALYSIS_CODE_1  'Supplier confirmation (CA 1 PO)', DATA0070.ANALYSIS_CODE_2 as 'Date initiale demandé (CA 2 PO)', DATA0024.LOC_ANALYSIS_CODE1 as '% Over delivery', CASE WHEN DATA0022.QUANTITY > DATA0206.QUANTITY THEN 'Ajustement ST'  WHEN DATA0022.TDATE<>DATA0206.TRANSACTION_DATE OR DATA0206.TRANSACTION_DATE<>DATA0206.ENTERED_DATE OR DATA0022.TDATE<>DATA0206.ENTERED_DATE THEN 'Ajustement ST'  ELSE '-'  END as 'Contrôle ajustements', CASE WHEN DATA0023.SUPPLIER_NAME='Sous-traitant' THEN 'En ordre'   WHEN DATA0070.ANALYSIS_CODE_1='' OR DATA0070.ANALYSIS_CODE_1 IS NULL AND LEFT(DATA0024.FOB,3)='DAP' OR LEFT(DATA0024.FOB,3)='DAT' OR LEFT(DATA0024.FOB,3)='DDP' THEN 'En ordre'   WHEN DATA0070.ANALYSIS_CODE_1<>'' AND LEFT(DATA0024.FOB,3)='EXW' OR LEFT(DATA0024.FOB,3)='CIP' OR LEFT(DATA0024.FOB,3)='FCA' OR LEFT(DATA0024.FOB,3)='CPT' OR LEFT(DATA0024.FOB,3)='FAS' OR LEFT(DATA0024.FOB,3)='FOB' OR LEFT(DATA0024.FOB,3)='CFR' OR LEFT(DATA0024.FOB,3)='CIF' THEN 'En ordre'   ELSE'A contrôler'   END as 'Cotnrôle Supplier Confirmation', CASE WHEN DATA0070.PO_NUMBER IS NOT NULL THEN 3   ELSE ''   END as 'Limite ALT MiSC 1', CASE WHEN DATA0070.PO_NUMBER IS NOT NULL THEN 5   ELSE ''   END as 'Limite ALT MiSC 2', CASE WHEN DATA0070.PO_NUMBER IS NOT NULL THEN 0.02   ELSE ''   END as 'Limite NC 1', CASE WHEN DATA0070.PO_NUMBER IS NOT NULL THEN 0.05   ELSE ''   END as 'Limite NC 2'   FROM DATA0070  FULL OUTER JOIN DATA0071 DATA0071 WITH(NOLOCK) ON DATA0070.RKEY=DATA0071.PO_PTR AND DATA0070.PO_TYPE=0  FULL OUTER JOIN DATA0072 DATA0072 WITH(NOLOCK) ON DATA0070.RKEY=DATA0072.POPTR AND DATA0070.PO_TYPE=1  FULL OUTER JOIN DATA0017 DATA0017 WITH(NOLOCK) ON DATA0071.INVT_PTR=DATA0017.RKEY  LEFT OUTER JOIN DATA0023 ON DATA0070.SUPPLIER_POINTER=DATA0023.RKEY  /*Liaison data0028 on data0017 -> liaison article + on data0028 -> liaison Fournisseur car lead time article et différent suivant le Fournisseur (idem pour le prix)*/ LEFT OUTER JOIN DATA0028 DATA0028 WITH (NOLOCK) ON DATA0017.RKEY=DATA0028.INVENTORY_PTR AND DATA0023.RKEY=DATA0028.SUPPLIER_PTR  LEFT OUTER JOIN DATA0206 ON DATA0071.RKEY=DATA0206.PO_PTR  LEFT OUTER JOIN DATA0235 DATA0235 WITH (NOLOCK) ON DATA0072.RKEY=DATA0235.D0072_PTR  LEFT OUTER JOIN DATA0022 ON DATA0206.DATA22_PTR=DATA0022.RKEY   LEFT OUTER JOIN DATA0024 DATA0024 WITH(NOLOCK) ON DATA0023.RKEY=DATA0024.SUPPLIER_PTR  WHERE  DATA0070.PO_DATE > '01.01.2017' AND DATA0070.STATUS <> 3  AND DATA0070.STATUS <> 4  AND DATA0070.STATUS <> 5  AND DATA0023.ANALYSIS_CODE1='1' /*Ajout de modification -> AND (DATA0022.STATUS IS NULL OR DATA0022.STATUS=5) -> permet exclure les transactions de réception cancelled pur éviter des lignes supplémentaires*/ AND (DATA0022.STATUS IS NULL OR DATA0022.STATUS=5)   ORDER BY DATA0070.PO_NUMBER 


EDIT : Ajout des balises code code !

Forum

Convertir MY SQL dans Access De my sql à access (Résolu) » Forum - Webmaster De my sql vers access » Forum - Programmation Choisir Access ou My Sql ? sinon free JDBC » Forum - Access Connecter access à my sql » Forum - Bases de données My SQL - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) » Conseils pratiques - MySQL

Web: www.shapebootstrap.net

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed