Accessã®ã¯ãšãªïŒ3çš®é¡ã®çµåã®éãã«ã€ããŠ
Accessã®ã¯ãšãªã§ã¯ãè€æ°ã®ããŒãã«ãçµåããŠäœãçºã®ããªã¬ãŒã·ã§ã³ã·ããããšåŒã°ããæ©èœããããŸãããã®æ©èœãçšããããšã§ãè€æ°ã®ããŒãã«ãåãããŒïŒäŸãã°ç€Ÿå¡IDïŒãçšããŠçµåããã¬ããã£ã³ã³ããç¶æ ã§æ°ããªã¯ãšãªãäœãããšãã§ããŸãã
äŸãã°ååãã¹ã¿ãŒãšå£²äžãã¹ã¿ãŒãçµåãããŠã売äžãã¹ã¿ã«ååæ å ±ãå ããç¶æ ã®ã¯ãšãªãäœããªã©ãããã§å¯èœã«ãªãããã§ãããããããã®çµåã«ã¯3çš®é¡ååšãããã¡ããšææ¡ããç¶æ ã§çµåãããªããšãããŒã¿ã®åãããŒããæãæãããããŸãã®ã§ãä»åã¯ããã解説ããŠã¿ãããšæããŸãã
ç®æ¬¡
ä»å䜿çšãããµã³ãã«ãã¡ã€ã«
- çµåãµã³ãã« - Access2016ã§äœæãããŠããŸã
2ã€ã®ããŒãã«ãš3çš®é¡ã®ã¯ãšãªãäœã蟌ãã§ãããŸãã
çµåã®çš®é¡ãšå 容
ä»åã®ãµã³ãã«ã¯ããããããããçºã«ããã¹ã¿ããã³ãµããã¹ã¿ã®äž¡æ¹ã®ããŒãã«ã«æŒããŠããæœèšåã«ãããããŒã«ããŠãäž¡æ¹ã®ããŒãã«ãçµåãäž¡æ¹ã®ããŒãã«ããŒã¿ãçµåããŠæœåºããŠããŸãã
å éšçµå
ããããå®å šçµåã§ããïŒã€ã®ããŒãã«ïŒãã¹ã¿ãšãµããã¹ã¿ïŒã®é¢ä¿ã«æŒããŠãäž¡æ¹ã«ããŒïŒäŸãã°ç€Ÿå¡IDïŒãåºæºã«ãåãããŒã¿ãååšãããå Žåã«ãããããããã¯ã¢ããããŠè¡šç€ºããŸããçæ¹ã®ããŒãã«ã«ãããªãããŒã¿ã«ã€ããŠã¯ãã¯ãšãªã®çµæã«è¡šç€ºãããŸãããéåžžã«ãã䜿ããã¿ãŒã³ã§ãããããŒã¿ãåãããŒãå¯èœæ§ããããŸãã
å³ïŒããŒãã«éã¯ç¢å°ã®ãªãçµåã§çµã°ããŠãã
å³ïŒäž¡æ¹ã«åäžããŒãæã€ã¬ã³ãŒãã ããæœåºããã
å·Šå€éšçµå
LEFT JOINãšåŒã°ããŸããããã¹ã¿ãŒåŽã®å šããŒã¿ã衚瀺ãããµããã¹ã¿åŽã®ããŒã¿ã¯ååšãããã®ãããã°ããã¯ã¢ããããŠæ¥ãŠãããŸãããã¹ã¿ãŒåŽã®ããŒã¿ã®åãããŒãã¯ãããŸãããããµããã¹ã¿åŽã«ã ãããæœèšåãååšããå Žåããã®ããŒã¿ã¯ãããããªãã®ã§ãåãããŒãå¯èœæ§ã¯ãããŸãã
å³ïŒãµããã¹ã¿ã«ã ãããããŒã¿ã¯è¡šç€ºãããªã
å³ïŒãã¹ã¿ã«ã ãããå Žåãåååã空çœã«ãªã£ãŠã
å³å€éšçµå
RIGHT JOINãšåŒã°ããŸããããµããã¹ã¿åŽã«ããããŒã¿ã¯å šè¡šç€ºãããŸãããã£ãŠããã¹ã¿åŽã«ç»é²ã®ãªãããŒã¿ã¯ãã®å Žåãæœèšåã空çœã®ç¶æ ã§è¡šç€ºãããŸãããã¹ã¿ãŒåŽã®ããŒã¿ãåãããŒãããšã«ã¯ãªããŸããã空çœãããããšã§ãããããªããŒã¿ããããããšããŠãŒã¶ã¯æ€ç¥ã§ããã®ã§ãåãããŒãã¯å°ãªããªããŸãã
å³ïŒãã¹ã¿ã«ãªãããŒã¿ã¯ç©ºçœã§è¡šç€ºãããäºã«ãªã
å³ïŒæœèšåã空çœã®ã¬ã³ãŒãããã¹ã¿åŽã«ç»é²ã®ãªãããŒã¿
çµåã®äœãæ¹
çµåã®éžæ
ã¯ãšãªäœæç»é¢ã«æŒããŠã以äžã®ãããªæé ã§äžèšã®3çš®é¡ã®çµåãäœæããããšãå¯èœã§ãã
- 空ã®ã¯ãšãªãã¶ã€ã³äœæç»é¢ã«æŒããŠããã¹ã¿ããã³ãµããã¹ã¿ã远å ãã
- ãã¹ã¿ãŒåŽæœèšåã«ããæŽãã§ããµããã¹ã¿ãŒåŽæœèšåç§°ãžãã©ãã°ã¢ã³ãããããããïŒãã®æ®µéã§å éšçµåã¯å®æããïŒ
- çµåãããç·ãããã«ã¯ãªãã¯ãã
- çµåã®çš®é¡ãåºãŠããã®ã§ã2åç®ãéžã¶ãšå·Šå€éšçµåãšãªã
- ããªãã3åç®ãéžã¶ãšå³å€éšçµåãšãªã
- äžæ¹åã«å¯ŸããŠç¢å°ãåºãŠãçµåã¯å®æãããOKãæŒãäºã§å®æãã
å³ïŒãã€ã¢ãã°å ã§çµåã®çš®é¡ãéžæãã
ã¯ãšãªã®äœæ
çµåãå®äºãããäž¡æ¹ã®ããŒãã«ãããã£ãŒã«ããæŽãã§ãäžã®ããã«ã«é¢ãäºã§ã¯ãšãªã®åã®ãã¶ã€ã³ã決ããããŸããããããéé²ã«ã€ããã°è¯ããšãããã®ã§ããªãã以äžã®ã«ãŒã«ãå ã«å¿ èŠãªåãæŽãã§ãã€ããŸãããã
- ãã¹ã¿ãŒåŽããã¯IDãããŒã«ããåãªã©äž»èŠãªããŒã¿ãæŽãã§å ¥ãã
- ãµããã¹ã¿ãŒåŽããã¯ãããŒã«ããå以å€ã§è¡šç€ºããããµããã¹ã¿ããŒã¿ã®åã ããå³éžããŠå ¥ãããIDåãªã©ã¯éåžžå¿ èŠãªãã
- ãŸããã¹ã¿åŽã«ããã£ãŠãµããã¹ã¿åŽã«ããããããŒä»¥å€ã§åããããªããŒã¿ã«ã€ããŠã¯ããã¹ã¿åŽã®ã ããå ¥ããã°ååã§ããã
å³ïŒãã¹ã¿ãããŒã¹ã«ãããµããã¹ã¿ããã¯å¿ èŠãªåã ãããã¯ã¢ãã