ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] Excel VBA SQL ¬d¸ß°ÝÃD

[µo°Ý] Excel VBA SQL ¬d¸ß°ÝÃD

½Ð±Ð¦U¦ì¥ý¶i~

§Ú¦³¤@mdb¸ê®Æ®w,¤º¦³3­Ótable¤À§O¬°
1. dbo_pro
   prno, kano, a01,.........
2. dbo_prok
   kano, name,......
3. dbo_aa
   a01, name, names.......

§Ú·Q¨Ì±ø¥óSheet1.Range("K9")¬°·j´M±ø¥ó(prno),
±q¸ê®Æ®wdbo_pro -->kano, a01 left join ¥t¥~
¨â­ÓTable¤ºªº¸ê®Æ,¦ý°õ¦æ®ÉÅã¥Ü¿ù»~"Automation ¿ù»~'

Err.JPG

·Q½Ð±Ð¬O§ÚªºSQL»yªk¿ù»~ÁÙ¬OEXCEL VBA ¹ïSQL
¬d¸ß»yªk¦³­­¨î©O?

·P®¦~

¦pªþ¥ó
Input2.zip (39.63 KB)

dbo_aa.names
names ¦n¹³­è¦n¬OÃöÁä¦r,  Â²³æªº¸Ü´«¤@¤UÄæ¦W

¥t¥~LEFT JOIN, ISNULL ªº¦bACCESS·|¦³¤£¦P¼gªk:
(¥H¤U§Ú§âDBO_AA.NAMES§ï¦¨DBO_AA.NAME)
      SQL = "Select dbo_pro.*,  dbo_aa.name as anames, iif(isnull(dbo_prok.name), '',dbo_prok.name)as namek "
     SQL = SQL & "From (dbo_pro left outer join dbo_aa on dbo_pro.a01=dbo_aa.a01) "
     SQL = SQL & "left join dbo_prok on dbo_pro.kano=dbo_prok.kano "
     SQL = SQL & "Where dbo_pro.prno ='" & Sheet1.Range("K9") & "'"
     SQL = SQL & " Order by dbo_pro.prno"
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¥»©«³Ì«á¥Ñ mgame168 ©ó 2011-10-24 17:58 ½s¿è

¦^´_ 2# kimbal


    ·PÁ kimbal ª©¤j
     isnull() ¸g¬dÃÒ«á, ¦bMS-SQL ¥i¥Î,

     Access¤¤ªºIsNull()     
      ¦bAccess¤¤¡AIsNullªº§@¥Î¶È¶È¬O§PÂ_¬O§_¬°ªÅ­È
     ¤£¹LAccessÁÙ¬O¦³¤ä´©MS-SQL IsNullªº¬Û¦ü«ü¥O½X¡A
     ¦bAccess¬O¥Î iif ´À¥N..
Select iif(IsNull( express ), value1, value2 ) From TableName
»yªk»¡©ú¡A§PÂ_express¬O§_¬°ªÅ¡A­Y¬OªÅªº¦^¶Çvalue1¡A¤Ï¤§«h¦^¶Çvalue2

LEFT JOIN ªº¼gªk¦p¦P±z¼gªº¤è¦¡

dbo_aa.names
names ¦n¹³­è¦n¬OÃöÁä¦r, ¨S¿ùªº½T¬OÃöÁä¦r
¦ý¸ê®Æ®wªºÄæ¦ì¦WºÙ§ÚµLªk§ó§ï, ©Ò¥H¹ï©ó
«O¯d¦rªº½Ä¬ð
¸Ñ¨M¿ìªk´N¬O±Nnames§ï¦¨[names]´N¸Ñ¨M¤F

¥H¤Uªº»yªk§Ú¦³­×§ï¹L
     SQL = "Select dbo_pro.*, dbo_prok.name, dbo_aa.[names] "
     SQL = SQL & "From (dbo_pro left outer join dbo_aa on dbo_pro.a01=dbo_aa.a01) "
     SQL = SQL & "left join dbo_prok on dbo_pro.kano=dbo_prok.kano "
     SQL = SQL & "Where dbo_pro.prno ='" & Sheet1.Range("K9") & "'"
     SQL = SQL & " Order by dbo_pro.prno"

·PÁÂkimbalª©¤jªº«ü¾É

Input2.zip (40.9 KB)

TOP

        ÀR«ä¦Û¦b : ¯à¥I¥X·R¤ß´N¬OºÖ¡A¯à®ø°£·Ð´o´N¬O¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD