אקסס למתחילים: יצירת מערכת לניהול תורמים
-
כהמשך לפוסט: https://tchumim.com/topic/10485/מערכת-לניהול-תורמים/11
למען הסדר הטוב ורציפות בקריאה, פתחתי אשכול נפרד לתגובות. אודה אם תגיבו שם.
@בערל השלב הראשון הוא התכנון כמובן. והתכנון הראשון הוא מבנה הנתונים.
בבסיס הנתונים (להלן db) הנתונים נשמרים כאוסף של טבלאות. כל עמודה בטבלה מציינת "שדה", וכל שורה מציינת "רשומה".אבל קודם כל, היכרות פשוטה:
חלק א: יצירת טבלת התורמים והטופס שלה:
לשם פשיטות, נתחיל עם טבלה פשוטה ביותר של התורמים:
עבור כל תורם אני אשמור את השדות הבאים:
ID (שדה מפתח, המזהה של הרשומה) - מספר רץ
שם פרטי - מחרוזת טקסט
משפחה - מחרוזת טקסט
טלפון נייד - מחרוזת טקסטלשם כך תפתח את אקסס, ובתפריט תבחר יצירה > עיצוב טבלה, ותקליד את השורות הבאות:
אחר כך תעמוד על השורה של ה-ID ותלחץ בתפריט למעלה על הסמל של "מפתח ראשי".
שים לב שנוסף ליד השורה סמל של מפתח:
סגור את הטבלה ותקרא לה: Donor (תורם)
היה קל, נכון?
בוא ניצור עכשיו טופס פשוט שבו נוכל להקליד נתוני תורמים:
תסמן את הטבלה Donor בפאנל שבצד ימין, ובתפריט למעלה תבחר: יצירה > טופס.
אקסס תיצור מיד טופס בשבילך:
סגור ושמור בשם frmDonorפתח את הטופס ותכניס כמה שמות של תורמים. תוכל לנווט בין הרשומות עם החצים למטה.
בפוסט הבא ניצור טבלה וטופס של תרומות, ונקשר אותו לטופס הזה.
-
חלק ב: יצירת טבלת התרומות והטופס
טוב, אז בוא ניצור עוד טבלה, שתכיל את התרומות של כל תורם (אל תשכח לסמן את שדה המפתח)
שים לב: השדה השני DonorID הוא המקשר של התרומה לתורם.
עוד דבר חשוב: בחר את השדה האחרון CreatedOn, ולמטה בחלון המאפיינים של השדה, תחת ערך ברירת מחדל תכניס את הפונקציה (המובנית):=Now()זה יכניס אוטומטית את הזמן הנוכחי ביצירת רשומת תרומה חדשה.
שמור את הטבלה בשם Donation.
צור טופס חדש המבוסס על הטבלה הזו, כמו שעשינו למעלה עם התורמים:
תבחר את הטבלה Donation מצד ימין, ובתפריט: יצירה > טופס:שמור את הטופס בשם frmDonation
קח הפסקה לכוס קפה, ונמשיך בפוסט הבא. -
חלק ג: קישור בין הנתונים של התורמים והתרומות
הכח של בסיס נתונים רלציונאלי (כגון אקסס, SQL) נובע מהגדרת קשרי הגומלין (relationships) בין הנתונים.
הקשר בין רשומה של תורם לרשומה של תרומה הוא קשר של אחד-להרבה (one-to-many)
כי לכל תורם יכולות להיות מספר תרומות (לפחות אם אנחנו אופטימיים
)
כדי להגדיר את הקשר בין הטבלאות, נבחר בתפריט למעלה: כלי מסד נתונים > קשרי גומלין
ונוסיף את שתי הטבלאות שיצרנו:
עכשיו נבחר את השדה DonorID בטבלת התרומות, ונגרור אותו אל השדה ID בטבלת התורמים.
אם לא טעינו, נפתח חלון כזה:
נסמן את התיבה: "אכוף שלמות הקשרים בין הטבלאות". זה מבטיח שלא יהיו לנו רשומות יתומות, דהיינו רשומות של תרומות שאין להן רשומת תורם תואמת.
נקליק על "צור". שים לב שעכשיו הטבלאות מחוברות על ידי קו. הסימון
1 - ∞מציין שהקשר הוא "אחד-להרבה" כנ"ל.
סגור את חלון קשרי הגומלין ושמור את השינויים.עכשיו מתחיל הכיף:
נפתח את הטופס של התורמים בתצוגת עיצוב, ונרחיב את החלון קצת למטה כדי לעשות חלל פנוי לרשימת התרומות:
נגרור לתוך החלל שיצרנו את הטופס של התרומות frmDonation, כדי ליצור טופס משנה:
נסגור ונשמור את הטופס של התורם.
עכשיו נפתח את הטופס של התרומות בתצוגת עריכה, ובגליון המאפיינים של הטופס, נשנה את תצוגת ברירת המחדל ל- גליון נתונים (זה יהפוך את התצוגה לגליון נתונים כמו באקסל)
כמו כן, נמחק מהטופס את שני השדות הראשונים: ID ו- DonorID:
נסגור ונשמור.
שים לב: עכשיו כשנפתח את הטופס הראשי (של התורם) יוצגו עבור כל תורם כל התרומות שלו, ורק שלו:
הפלא ופלא!
איך זה קורה?
הסבר בפוסט הבא! -
חלק ד - נקודות למחשבה
החלק הזה יעסוק פחות ביצירה של דברים חדשים, ויתמקד בתיאוריה שמאחורי הדברים.
חשוב שנבין מה עשינו עד עכשיו.
לשם כך נסקור שוב את מה שעשינו, ונתעמק קצת, ונשפר פה ושם כמה דברים.
כולם הביאו מחברת ועפרון ?טבלת התורמים
נפתח אותה שוב בתצוגת עיצוב, ונראה כי
א. שמות השדות מורכבים מאותיות אנגליות בלבד, וללא רווחים.
למרות שאקסס מאוד סלחנית ומאפשרת לנו בלי בעיה ליצור שמות שדות בעברית, זה מאוד לא מומלץ, ונשלם על כך מחיר יקר בהמשך.ב. קיים שדה מזהה לרשומה בשם ID.
ככלל, לכל טבלה ב-db תמיד יהיה שדה כזה, והוא נקרא מפתח ראשי. סוג הנתונים בו הוא מספר שלם (ארוך), ובדרך כלל הוא יהיה מסוג מספור אוטומטי, זהו שדה לקריאה בלבד וה-db אחראי לטפל בערכים שלו, והוא קובע באופן אוטומטי את הערך שלו בכל יצירת רשומה חדשה (בקפיצות של 1).
ערך השדה הוא ייחודי ונקבע באופן חד-פעמי, כלומר, אם למשל ברשומה האחרונה ID=4, גם אם נמחק אותה וניצור רשומה חדשה, ה-db יקצה לרשומה החדשה ID=5, למרות שכבר אין רשומה עם ID=4.המנגנון הזה מבטיח שתמיד ניגש לרשומה הנכונה.
דרך משל, אם לפלוני היה מספר תעודת זהות 1002, גם אם הוא נפטר, לא ישתמשו במספר שלו עבור אדם אחר, כדי שתמיד פלוני יזוהה עם המספר 1002.
ג. סוג הנתונים בשאר השדות
כברירת מחדל, גודל השדה "טקסט קצר" הוא עד 255 תוים.
אם ודאי לנו שלא נשתמש בכזה אורך, נוכל להקטין את גדול השדה המקסימלי, על מנת לחסוך מקום ולשפר יעילות.
בואו נגדיר מחדש את אורך השדות שלנו: LastName, FirstName, MobilePhone להיות כל אחד 30 תוים:נבחר את השורה LastName, ולמטה במאפיינים, נגדיר גודל שדה 30:
נחזור על הפעולה גם עבור השדות FirstName ו-MobilePhone. נסגור ונשמור את הטבלה, ואז נקבל את תיבת ההתראה הבאה:
שדואגת שלא נעשה דבר טיפשי בטעות. במקרה שלנו מסתמא לא הכנסנו שם גדול מ-30 תוים. פשוט נאשר וזהו.
הערה: אם כבר היה בשדה ערך גדול מ-30 תוים, המחרוזת תיחתך אחרי התו השלושים.נעבור לטבלה השניה:
טבלת התרומות
גם כאן יש מפתח ראשי בשם ID. אבל יש כאן שדה נוסף:מפתח משני
שימו לב לשדה DonorID, הוא מציין לאיזו רשומה בטבלה הראשית (תורמים), שייכת כל רשומה בטבלה המשנית (תרומות). ולכן הוא נקרא מפתח משני או מפתח זַר.
גם המפתח המשני מוגדר כסוג מספר שלם ארוך, כי הוא חייב להיות תואם לסוג הנתונים של המפתח הראשי בטבלת התורמים.
אבל שימו לב שהוא לא מסוג מספור אוטומטי, מפני שאנחנו קובעים (ולא אקסס) לאיזו רשומת תורם שייכת כל תרומה.זה מעלה שתי נקודות חשובות למחשבה:
איך נוודא שלא שכחנו בטעות למלא את השדה?
ומה אם ננסה לרשום מספר שלא נמצאת עבורו רשומה בתורמים (=התורם לא קיים)....?אל תדאגו.
תאמינו או לא, את שתי הבעיות האלו כבר פתרנו כשהגדרנו את קשרי הגומלין בין הטבלאות, וסימננו את התיבה "אכוף שלמות הקשרים בין הטבלאות"!
א. אם ננסה להכניס בשדה DonorID ערך שלא קיים בטבלת התורמים, למשל 1000, ה-db יסרב לשמור את רשומת התרומה, כי הוא מוודא עבורינו שלא יהיו רשומות "יתומות" (תרומות ללא תורם משוייך)
ב. אם ננסה למחוק מטבלת התורמים תורם שקיימת עבורו תרומה - שוב ה-db לא יאפשר זאת.הערה: משום מה יש שמתעצלים מלהגדיר את קשרי הגומלין בצורה מפורשת, וסומכים על הגדרת היחס בין הטפסים \ על הקוד וכו'. זוהי "מדיניות" גרועה של הזנחה, ואנשים כאלו מקומם מאחורי סורג ובריח ולא מאחורי מסך מחשב... נו, הגזמתי כמובן, קצת...
דבר קטן לסיום:
את השדה של הטלפון הנייד הגדרנו כמחרוזת ולא כמספר. הוא הדין גם לגבי שדה שמכיל תעודת זהות.
טיפ: כל שדה שמכיל מספר, אבל ברור לנו שאין לנו לעולם עניין לעשות עליו מניפולציות מתמטיות (חיבור, חיסור, כפל וכו') עדיף להגדיר כמחרוזת.טוב, שיעורי בית (אוף...):
לקצר את אורך השדה של מספר הטלפון הנייד ל-20 תוים.בפוסט הבא ב"ה נמשיך לעבור על הטפסים שעשינו.
-
חלק ד - תוספת: בקשר למדריך הזה
א. איה הסילבוס?
המדריך לא מתיימר להיות מקיף. הוא אמור להוות קרש קפיצה למים.
אני מאמין בשיטת לימוד של "שני צעדים קדימה וצעד אחד אחורה", דהיינו להתנסות בידיים ואחר כך לפרק לגורמים ולראות איך זה עובד.
חוץ מזה, אני לא אוהב למשוך דברים לאורך זמן. לכן אני לא תמיד מארגן נכון את השיעור.
לא בטוח שזו שיטה מתאימה לכולם.ב. זהירות, מלכודת אקסס
ביטים רבים כבר נשפכו כאן בפורום על אקסס ועל הרלוונטיות שלה.
לעניות דעתי אקסס היא שער כניסה מצוין לעולם בסיסי הנתונים. היא נותנת לך ישר מהקופסה ארגז כלים נהדר למתחילים, ומטפלת בהרבה דברים מאחורי הקלעים.
אבל במידה מסויימת, זה החסרון הכי גדול שלה.
אל תפחדו לעבור את השער יום אחד.
אני מקווה שהמדריך יעזור לנו להכיר את אקסס ולהשתמש בה רק במקרים שהיא באמת הכי מתאימה. -
חלק ה: טפסים מקושרים
אז כמו שכבר ראינו, אקסס עוזרת לנו המון ביצירת טפסים. למעשה, כדי ליצור טופס שמבוסס על טבלה פשוט בוחרים אותה ובתפריט: יצירה > טופס.
נשים לב שהטופס מקושר לטבלה עצמה, זה אומר שכל שינוי של נתונים בטופס – מיד מבוצע גם בטבלה עצמה, בלי שנדאג לאיך זה מתבצע.
הערה: אקסס היא תוכנה מבוססת נתונים, כך אין צורך לשמור כל שינוי. אמנם קיימים מקרים שבהם נרצה לשאול את המשתמש האם לשמור את השינויים. יש לזה גם פתרונות אך הם מעבר להיקף ההדרכה הזו.
אקסס גם מספקת לנו סרגל ניווט כברירת מחדל בתחתית כל טופס, תראו בתחתית טופס התורמים:
ויש בו אפילו מנגנון חיפוש פשוט!
תנסו להקליד שם של תורם למטה, אקסס מיד תעבור לרשומה הרלוונטית.
אבל מה שיותר נחמד, הוא שהיא עוזרת לנו בניהול טפסים מקושרים.
תשאלו: מה זה טפסים מקושרים?
אז זוכרים שיצרנו קשרי גומלין בין התורמים והתרומות? בואו תראו מה קרה:
תפתחו את טבלת התורמים, הפעם בתצוגת גליון נתונים (פשוט תקליקו עליה פעמיים)
שימו לב שיש סימן + לפני כל שורה:
אם נלחץ עליו, נקבל את רשימת התרומות של אברהם כהן:
אבל חסר כאן שדה אחד, שמתם לב? השדה DonorID נעלם. אקסס מסתירה אותו, כי היא יודעת שלגבי התרומות של אברהם כהן, המזהה של התרומה צריך תמיד להיות המזהה של אברהם כהן, כלומר 1, לכן היא קובעת עבורנו את הערך בשדה באופן אוטומטי ל-1, ומסתירה את העמודה הזו.
זה גם מה שקרה כשיצרנו את הטופס המשולב, אקסס זיהתה ששתי הטבלאות שעליהם מבוססים הטפסים הן מקושרות בקשר גומלין, ולפיו קבעה לבד כי השדות המקושרים הם: ID בטבלת התורמים עם DonorID בטבלת התרומות.
כאשר אנחנו מדפדפים בין התורמים בטופס, היא היא גם דואגת עבורנו לעדכן אוטומטית את טופס המשנה של התרומות לרשומות הרלוונטיות.
גם כשנוסיף רשומה חדשה לתרומות, אקסס תקבע את ערך השדה DonorID ל-ID של התורם הנכון שבטופס האב. (זו הסיבה שמחקנו את השדה הזה מהטופס, מפני שאם הוא היה מוצג, הוא היה שדה לקריאה בלבד)
מחיאות כפיים לאקסס!
אם אתם רוצים להציץ מתחת למכסה המנוע ולראות היכן מופיע הקישור בטופס, תפתחו את הטופס של התורמים בתצוגת עיצוב, תבחרו את טופס המשנה, ובמאפיינים שלו יש לשונית שנקראת נתונים, והנה השדות המקשרים לפניכם:
הערה: כל ההוקוס פוקוס הזה קרה כי הגדרנו מראש את קשרי הגומלין בין הטבלאות. אם היינו מדלגים על כך, היינו צריכים לספק את שמות השדות האלו באופן ידני.
בפוסט הבא נעסוק אי"ה בשאילתות.
-
חלק ו: שאילתות חלק א: שאילתת בחירה עם שדה מחושב
שאילתות מאפשרות לנו לבצע ניתוח נתונים.
בלעדיהן, כל הנתונים שלנו פשוט יהיו בלתי שמישים.שאילתות במסד הנתונים מבוססות על שפה שנקראת SQL שזה בתרגום חופשי "שפת שאילתות מובנית". (תודה ל @yossiz על ההארה)
אקסס מאפשרת לנו ליצור סוגי שאילתות רבות על ידי שימוש בממשק גרפי פשוט, בלי לדעת כלום על שפת SQL. אקסס עושה את העבודה הקשה בשבילנו, ומתרגמת את רצוננו לשפת SQL.
שאילתא ראשונה: שם מלא
נניח שאני רוצה להציג את השם המלא של כל תורם.
נבחר בתפריט למעלה: יצירה > עיצוב שאילתא, נבחר את טבלת התורמים Donor, ונוסיף אותה למשטח העבודה:
נעמוד על השדה הראשון, ונפתח מהתפריט למעלה את בונה הביטויים:
בחלון שנפתח נרשום:FullName: LastName & " " & FirstNameונלחץ על אישור:
נעבור לתצוגת גליון נתונים (בתפריט בצד ימין):
ונראה את התוצאות:
השאילתא יצרה שדה מחושב בשם FullName, שמכיל את הנוסחא:LastName & " " & FirstNameאנו משתמשים באופרטור & כדי לחבר מחרוזות, בדומה לאקסל.
הערה: השאילתא הזו לא ממש שימושית, כי חסר בה את השדה המזהה ID. בכוונה השמטתי אותו כטעות של מתחילים, ובהמשך כשניתקל בבעיה ונראה שאנו חייבים אותו, נערוך את השאילתא ונוסיף גם אותו.
אתם זוכרים שאמרתי לכם שאקסס דואגת ליצור את פקודת ה-SQL בשבילנו? אז תנשמו עמוק, ובואו נראה כיצד כתובה השאילתא בשפת SQL. בתפריט נעבור לתצוגת SQL:
והנה לפנינו פקודת הSQL שמרכיבה את השאילתא:
תתבוננו ותראו שבאמת שפת SQL לא כל כך נוראית. הפקודה SELECT אומרת שהשאילתא תבחר את השדות LastName ו-FirstName, ותוסיף ביניהם רווח, והמילה AS יוצרת שדה חדש בשם FullName. המילה FROM מציינת כי השדות ייבחרו מתוך הטבלה Donor.נשמור את השאילתא בשם qryDonor_FullName.
בפוסט הבא נכיר את שאילתת הקיבוץ.
-
חלק ז: שאילתות חלק ב: שאילתת קיבוץ
נניח שאני רוצה לדעת מה כמה כסף תרם כל תורם. לשם כך אנו זקוקים לשאילתת קיבוץ. שאילתת קיבוץ כשמה כן היא, מקבצת לנו נתונים הקשורים זה לזה לתוך שדה יחיד. במקרה שלנו, היא תחשב את הסכום של כל התרומות לכל תורם.
שאילתא שנייה: סכום התרומות לתורם
נבחר בתפריט למעלה: יצירה > עיצוב שאילתא, נבחר את טבלת התרומות Donation, ונוסיף אותה למשטח העבודה:
עכשיו נקליק פעמיים על השדה DonorID, הוא יתווסף לרשימה למטה:
נוסיף גם את השדה Amount:
עכשיו, נבחר מהתפריט למעלה את הסמל של הסיגמא, שכתוב מתחתיו "סכומים":
שימו לב שנוספה למטה עוד שורה, עם הכותרת "סך הכל", ולכל שדה כתוב "קיבוץ לפי":
זה אומר שהפכנו את סוג השאילתא משאילתת בחירה פשוטה לשאילתת קיבוץ.נשנה בעמודה של Amount את האפשרות "קיבוץ לפי" לאפשרות Sum (=סכום)
זהו, השאילתא מוכנה!
כדי לראות את התוצאות, נעבור לתצוגת גליון נתונים, והנה התוצאות:
רגע, מה קרה לכותרת של העמודה השניה? אקסס נתנה לה שם אחר כדי לציין שזוהי עמודה של סכום. אבל זוכרים כמה אנו צריכים להזהר במתן שמות לעמודות? חובה עלינו לשנות את שם העמודה לשם תקני. נחזור לתצוגת עיצוב:
נבחר את העמודה השניה, ובתפריט למעלה נפתח את בונה הביטויים:
בחלון שנפתח נרשום:TotalAmount: Amount
ונלחץ על אישור. נעבור שוב לתצוגת גליון נתונים, ונראה שעכשיו הכותרת של העמודה השניה השתנתה ל: TotalAmount:
נשמור את השאילתא בשם qryDonation_TotalAmount.יפה, נכון?
כן, אבל לא כל כך שימושי... מי זוכר מיהו התורם מספר 2 ומיהו התורם מספר 732?
אני רוצה להציג גם את שם התורם ליד הסכום שתרם!
בסדר, בסדר, בפוסט הבא נלמד איך לשלב את שתי השאילתות שיצרנו יחד, וליצור את התוצאה המבוקשת. -
חלק ח: שאילתות חלק ג: שאילתות צירוף
טוב, אז יש לנו שאילתה אחת בשם: qryDonor_FullName שמציגה את השמות המלאים של התורמים, להלן נקרא לה: "שאילתת-השמות", ויש לנו עוד שאילתא בשם qryDonation_TotalAmount שמציגה את סכום התרומות של כל תורם, להלן תיקרא: "שאילתת-הסכומים".
אנחנו רוצים לחבר את שתי השאילתות ביחד, כדי לקבל את התוצאה הבאה:
איך עושים את זה?
לשם כך נכיר סוג נוסף של שאילתא, והוא (תופים בבקשה...)
שאילתת צירוףשאילתא זו מצרפת שתי טבלאות או שאילתות, או יותר.
פתחו את מעצב השאילתות (יצירה > עיצוב שאילתא), ונעבור ללשונית של שאילתות כדי להציג את רשימת השאילתות שלנו:
נוסיף את שתי השאילתות למשטח העבודה:
אבל...
איך נחבר את שתי השאילתות? מה השדה שמקשר ביניהן?
התשובה היא: אין שדה כזה...השדה שאמור להיות המקשר הוא שדה המפתח של התורם, אבל אם נתבונן, נראה כי בשאילתת-הסכומים, לכל רשומה יש שדה מפתח, הלא הוא מיודענו DonorID, אבל בשאילת-השמות אין שדה מפתח.
בואו נתקן את הבעיה. נסגור את עורך השאילתות. תופיע ההודעה הבאה:
נבחר לא. אחרי שנתקן את שאילתות הבסיס, ניצור מחדש את השאילתא.הערה: יכולנו לשמור את השאילתא, לערוך את שאילתות הבסיס ולחזור לערוך את השאילתא, אבל לשם תרגול העדפתי לעשות הכל שוב מחדש. על הדרך תרוויחו עוד שיטה ליצירת שאילתות.
נפתח את שאילתת השמות בתצוגת עיצוב, ונוסיף את השדה ID לרשימת השדות (על ידי הקלקה כפולה על שם השדה):
נעבור לתצוגת גליון נתונים ונראה שעכשיו השדה מוצג בשאילתא:
הממ... השדה המזהה מוצג בעמודה השנייה ולא בראשונה... בואו נתקן גם את זה.הערה: סדר השדות במקרה של השאילתא הנ"ל לא משנה. אמנם אם נרצה להפעיל מיון על העמודות, סדר העמודות יקבע את קדימות המיון. וכן בשאילתת קיבוץ, לסדר השדות יש חשיבות גדולה. לכן כדי ללמוד איך עושים זאת, נסדר את השדה המזהה להיות ראשון.
לשם כך נחזור לתצוגת עיצוב, ונלחץ על המלבן האפור בראש העמודה של ID:
העמודה נצבעה בשחור:
נלחץ שוב פעם שנייה, ונגרור את העמודה לתחילת הרשימה, לפני עמודת השמות:
נשים לב שסדר העמודות השתנה:
נסגור ונשמור את השאילתא.עכשיו הכל מוכן ליצירת השאילתה שלנו:
מי זוכר איך בונים את שאילתת הצירוף? צריך לפתוח את מעצב השאילתות ולהוסיף את שתי השאילתות, אבל בואו נראה עוד דרך לעשות זאת:
בתפריט נבחר יצירה > עיצוב שאילתא, אבל הפעם, נסגור את החלון של הבחירה בלי להוסיף כלום למשטח העבודה, נקבל משטח ריק.
הפעם, נגרור את שתי השאילתות מהפאנל הימני לתוך המשטח:
נשים לב שהפעם מופיע בשאילתת השמות שדה המפתח בשם ID.
כדי לקשר את השאילתות, נגרור את השדה ID משאילתת השמות אל השדה DonorID בשאילתת הסכומים, ונראה שנוצר קו מחבר בין השאילתות (מזכיר לכם משהו?):
נוסיף את השדות הבאים לפי הסדר:
מטבלת השמות: ID, FullName
ומטבלת הסכומים: TotalAmount
נעבור לתצוגת עיצוב כדי לבדוק את התוצאות:
נראה שזה עובד!אבל רגע,
לאן נעלם ישראל יעקב?
תעלומה.מי שהבין מה קרה - כל הכבוד, אתה מוזמן לשתף את התשובה בתגובות.
השאר - נשאיר אתכם במתח עד הפוסט הבא... -
חלק ט: שאילתות חלק ד: שאילתות צירוף חיצוני
אז מה קרה לישראל יעקב?
כל הכבוד ל @בערל שענה תשובה נכונה לשאלה:כי השאילתא שלנו מצרפת בין qryDonor_FullName ל-qryDonation_TotalAmount
מכיוון שישראל יעקב אינו תרם (עדיין), הוא לא מופיע ב- qryDonation_TotalAmount
ולכן גם לא נמצא בתוצאה הסופית שממזגת בין שניהם
ולמה הוא לא מופיע ב-qryDonation_TotalAmount שהוא תרם 0.00?
כי qryDonation_TotalAmount מבוסס על טבלת Donation ושם הרי לא מופיע תרומות של אפסים, אז אין לשאילתא שלנו (כרגע) שום מקור לשלוף את המידע
הבנתי נכון?כן, מצוין! (שאר התלמידים ישארו בסוף השיעור לנקות את הכיתה
)הבעיה נוצרה כי השאילתא שלנו משתמשת בצירוף פנימי.
צירוף פנימי יציג נתונים רק כאשר יש מזהה תואם בשתי מקורות הנתונים, דהיינו בשתי השאילתות שלנו.
וכמו שציין יפה @ בערל, בשאילתת הסיכום לא מופיע המזהה של ישראל יעקב, כי היא מבוססת על טבלת התרומות (שבה הוא לא נמצא עדיין) ולא על טבלת התורמים (שבה הוא נמצא)
בואו נפתח שוב את השאילתא, ונעבור לתצוגת SQL. שימו לב למילים INNER JOIN שהדגשתי בצהוב, שפירושן צירוף פנימי:
כדי שהשאילתא תציג את כל התורמים, אנו צריכים ליצור צירוף חיצוני.
צירוף חיצוני מציג את כל הנתונים ממקור נתונים אחד, ורק את הרשומות התואמות מהמקור השני. למקור הנתונים שאנו רוצים להציג את כולו, קוראים טבלת הבסיס. ולשני קוראים טבלת המשנה.
זה בעצם מה שאנחנו מבקשים: להציג את כל הרשומות משאילתת השמות (בסיס), ורק את הרשומות התואמות משאילתת הסיכום (משנה).
נחזור לתצוגת עיצוב. נקליק פעמיים על הקו המקשר בין השאילתות, והחלון הבא יצוץ:
שימו לב שאקסס קראה לשאילתת השמות: הטבלה השמאלית, ולשאילתת הסכומים: הטבלה הימנית, למרות שבחלון שנפתח המיקום שלהן הפוך. זה בגלל שאקסס מתורגמת מימין לשמאל, ואילו שפת SQL משתמשת בתחביר של אנגלית, שבה הטבלה הראשונה נכתבת משמאל לטבלה השניה... בגרסא האנגלית של אקסס המיקום שלהן נכון.יש כאן שלוש אפשרויות:
- אפשרות ברירת המחדל: רק השורות בהן המזהה שווה. זהו צירוף פנימי.
- כל הרשומות מטבלת השמות (=השמאלית) ורק הרשומות הרלוונטיות מטבלת הסכומים (הימנית)
זה נקרא צירוף חיצוני שמאלי. - כל הרשומות מטבלת הסכומים (הימנית) ורק הרשומות הרלוונטיות מטבלת השמות (=השמאלית)
זה נקרא כמובן צירוף חיצוני ימני.
אז מה האפשרות שאנו מבקשים?
נכון, אפשרות מספר שתיים.
נבחר ונאשר.
תראו מה קרה לקו המחבר:
הוא הפך לחץ! אקסס מודיעה לנו שיש כאן צירוף חיצוני. טבלת הבסיס היא הטבלה שממנה יוצא החץ, וטבלת המשנה היא הטבלה שאליה החץ מגיע.הערה: המונח צירוף חיצוני שמאלי או ימני עלול לבלבל אותנו לא מעט, כי באמת יש כאן יש צירוף שמאלי ואילו המיקום של הטבלאות במעצב השאילתא הוא הפוך (טבלת הבסיס בצד ימין). כשנרצה לכתוב SQL טהור, נזכור שצירוף שמאלי גורם שטבלת הבסיס היא השמאלית, דהיינו זו שמופיעה ראשונה במשפט ה-JOIN, כי באנגלית כותבים משמאל לימין.
למזלנו, אקסס דואגת להבהיר את העניינים על ידי חץ פשוט. כמה נהדר!
נעבור לתצוגת גליון נתונים, ,ונראה שהפעם השאילתה קיבצה נדחי ישראל:
אבל עדיין יש עבודה.
שימו לב שהסכום של ישראל הוא ריק ולא מכיל 0.
הערה: כאשר שאילתת צירוף חיצוני לא מוצאת רשומה עם מפתח תואם בטבלת המשנה, היא מחזירה ערך מיוחד שנקרא NULL, שפירושו = כלום, שום דבר, גורנישט, וכו'.
אם ברצוננו להציג שם אפס, צריך ליצור שדה מחושב:
נחזור לתצוגת עיצוב, נבחר את השדה TotalAmount, וניכנס לבונה הביטויים:
נרשום שם את הנוסחה הבאה:
נלחץ על אישור, ונעבור לתצוגת גליון נתונים כדי לראות את התוצאות:
עכשיו לישראל מופיע אחר כבוד סכום תרומות כולל של 0.מי שם לב למשהו לא בסדר?
מישהו?
נכון מאוד. העמודה של הסכום קיבלה שם מוזר Expr1...
אני לא זוכר שנתתי לה שם כזה...
נחזור לתצוגת העיצוב, ונרחיב את העמודה השלישית כדי לראות את הנוסחא:
ונראה שאקסס נתנה את השם Expr1 לעמודה:
מה הפשט?
זה קרה בגלל שכתבנו נוסחא אך לא סיפקנו שם לשדה. לכל שדה חייב להיות שם, ולכן אקסס נתנה לו את השם הכללי Expr1, דהיינו "ביטוי מספר 1".בואו נשנה את השם ל: DonorTotaDonation:
נפתח בתצוגת גליון נתונים, ונראה שהכל בסדר:
הערה: נתנו לעמודה את השם DonorTotalDonation, ולא TotalAmount מפני שאם כן, הנוסחא היתה
TotalAmount: IIf(TotalAmount Is Null, 0, TotalAmount)מה שהיה יוצר הפנייה מעגלית בנוסחא, כי שם השדה זהה לשם המשתנה בנוסחא. כמו לומר לילד קטן: לך תקרא לי מבחוץ.
מה שמביא אותי להערה הבאה:
שִיוּם, Naming (= מתן שמות) הוא מלאכה חשובה, וקשה לעתים באופן מפתיע. שיום טוב יכול להקל עלינו כל כך, ושיום גרוע יכול לגרום לנו רגשות קשים מאוד... יש כמה כללי אצבע בזה, וגם כמה מחלוקות, בנוסף על הטעם האישי של המתכנת. השמות שבחרתי לטבלאות, לשאילתות והשדות, מסתמכים על כמה הרגלים שפיתחתי.
יופי!
נשמור את השאילתא בשם: qryDonor_TotalDonation
ונצא להפסקה. -
חלק י: שיעורי בית
חלק מהמטלות שאני אתן הן קלות, וחלק יותר מאתגרות.
מה שחשוב זה להנות ולא להיבהל...
גם כשלא מצליחים - זהו חלק חשוב מתהליך הלימוד!- הוסף מיון לשאילתת השמות, כך שהיא תציג את שמות התורמים לפי סדר הא"ב.
- הוסף לשאילתת הסיכום גם שדה בשם DonationCount שיכיל את מספר התרומות של כל תורם. דהיינו עבור אברהם יופיע 3, ליצחק יופיע 1, וליעקב יופיע 0 בדוגמא שלנו.
- סדר את שאילת הסכומים לפי סכום התרומות בסדר יורד, כלומר שבשורה הראשונה יופיע התורם שתרם הכי הרבה.
- אני רוצה לשמור עבור כל תורם את פרטי ההתקשרות עמו. כלומר שעבור כל תורם יהיה יומן שיחות עם תאריך השיחה ותקציר השיחה. צור טבלה חדשה בשם DonorConversation כדי לנהל את פרטי ההתקשרות. חשוב: אילו שדות אמורים להופיע בטבלה?
- צור קשר גומלין מתאים בין טבלת התורמים לטבלת השיחות.
- צור טופס חדש בשם frmDonorConversation שמציג את פרטי ההתקשרות.
- הטמע את הטופס הנ"ל בטופס התורמים הראשי, כך שליד רשימת התרומות תוצג גם היסטוריית השיחות.
בהצלחה לכולם!
בפוסט הבא נציג את הפתרונות בע"ה. -
חלק יא: פתרונות לשיעורי הבית
שוב יש"כ ל @בערל על התשובות, למעשה הוא ענה נכון על כל המטלות. אני בכל זאת אביא כאן גם את התשובות שלי:
- כדי לא להשאיר את המדריך חסר
- כי יש לי הערות קטנות פה ושם
אז מה היה לנו?
- הוסף מיון לשאילתת השמות, כך שהיא תציג את שמות התורמים לפי סדר הא"ב.
טוב, אז אמנם לא למדנו איך למיין עמודות, אבל מי שקצת מחפש רואה את השורה של המיון:
- הוסף לשאילתת הסיכום גם שדה בשם DonationCount שיכיל את מספר התרומות של כל תורם. דהיינו עבור אברהם יופיע 3, ליצחק יופיע 1, וליעקב יופיע 0 בדוגמא שלנו.
גם כאן, מי ששם לב כשבנינו את עמודת הסיכום בשאילתת הקיבוץ, הבחין שיש עוד נוסחאות לבחירה, אחת מהן היא Count, שהיא פשוט סופרת את מספר הרשומות עבור שדה מסויים:
הערה: בתור פרקטיקה טובה, אני רגיל לספור את המופעים של השדה המזהה בטבלה, דהיינו השדה ID, מפני שהוא תמיד מכיל ערך. אם הייתי בוחר בשדה שחלק מהשורות שלו מכילות לפעמים NULL, הפונקציה Count לא סופרת את השורות האלו. אכן לעתים זה כן שימושי להתעלם מהן, והכל לפי העניין.- סדר את שאילת הסכומים לפי סכום התרומות בסדר יורד, כלומר שבשורה הראשונה יופיע התורם שתרם הכי הרבה.
זה פשוט, על ידי הוספת מיון לעמודת הסכומים:
4.אני רוצה לשמור עבור כל תורם את פרטי ההתקשרות עמו. כלומר שעבור כל תורם יהיה יומן שיחות עם תאריך השיחה ותקציר השיחה. צור טבלה חדשה בשם DonorConversation כדי לנהל את פרטי ההתקשרות. חשוב: אילו שדות אמורים להופיע בטבלה?
גם זה ממש דומה לטבלת התרומות, ישר כח לכל מי שזכר גם פה לספק ערך ברירת מחדל לזמן השיחה:
- צור קשר גומלין מתאים בין טבלת התורמים לטבלת השיחות.
בבקשה:
הערה: בפתרון ש@בערל הביא, הוא ייבא את טבלת התורמים שוב, עם הכינוי Donor_1. זהו כינוי שנוצר אוטומטית על ידי אקסס כשמייבאים פעמיים את אותה הטבלה למשטח העבודה. אני מעדיף את הדרך שלי, כי היא נראית יותר קריאה, הכינוי Donor_1 נותן אשליה של שתי טבלאות. מלבד הקריאוּת של קשרי הגומלין, אני לא חושב שיש הבדל בשטח בין הפתרון שלו לשלי. אשמח אם מישהו יוכל להוסיף ולהרחיב בעניין.
- צור טופס חדש בשם frmDonorConversation שמציג את פרטי ההתקשרות.
שני קליקים וסיימתי:
7.הטמע את הטופס הנ"ל בטופס התורמים הראשי, כך שליד רשימת התרומות תוצג גם היסטוריית השיחות:
שוב, קלי קלות, פשוט לגרור את הטופס לתוך הטופס הראשי:
סיכום:
המטלות האלו הראו לנו באיזו קלות (יחסית) ומהירות אקסס מאפשרת לנו ליצור מערכת עובדת ולהוסיף לה פונקציונאליות.
זה דבר שככל הידוע לי, אין לו עדיין אח ורע במערכות אחרות.
אקסס משלבת בין בסיס נתונים, וממשק משתמש בצורה הכי חלקה וקלה שאפשר.כאן בונים
מי שחוש העיצוב שלו מפותח אפילו מעט, יכול להרים גבה ולומר: מה זה העיצוב הזה? ככה לא בונים טופס, ואיך אפשר להשתמש בזה בכלל?
על זה יש לי שתי תשובות:
א. MVP
לא, אני לא מתכוון לדגם של מאזדה. MVP הוא קיצור של Minimum Viable Product.ההגדרה הרשמית (לפי המכלול) : מוצר בר-קיימא מינימלי הוא מוצר עם המינימום ההכרחי של תכונות כך שלקוח יוכל להשתמש במוצר.
ההגדרה הרחובית משהו היא: בוא ניקח את הרעיון החלומי שלך, נוריד ממנו את כל חווית המשתמש, ואת כל הרעש והצלצולים, ונשאיר רק את השלד והעצמות כך שזה עדיין יוכל לעבוד...
אבל צחוק בצד, זה השלב הראשון והחשוב מאוד בפיתוח תוכנה.
באופן עקרוני אני לא מתכוון כרגע להשקיע כמעט כלום בעיצוב. אני אתייחס לאקסס במדריך הזה ככלי שאמור לפתור לי את הבעיה שאני מבקש לפתור, והיא בניית מערכת לניהול תורמים.
ברגע שהמערכת עובדת, אוכל להשקיע מזמני החופשי שאין לי, ולעצב כל קוצו של יוד.
ב. סבתא אקסס
כנגד היכולות הגבוהות שלה בפיתוח, אקסס מאד דלה בצד של חווית המשתמש.למרות שאפשרי בהחלט לשוות לה מראה מתקדם יותר, אנחנו די מוגבלים בזה. אפשרי לייבא מבחוץ כל מיני פקדים חיצוניים, או לכתוב בעצמנו שורות ארוכות של קוד, אבל ככל הנראה עדיף שנוותר. אם אנחנו מחפשים ממשק מתקדם ועכשווי, צריך להכיר בעובדה שאמנם אקסס יפה היא, אלא שהעניות מנוולתה.
טוב די עם הפיטפוטים.
בשיעור הבא נוסיף עוד מימד למערכת ניהול התורמים, והוא: רשימת הנתמכים.
יהיה מעניין בעז"ה. -
חלק יב: רשימת הנתמכים
בואו נראה מה עשינו עד עכשיו.
יש לנו מערכת שיכולה לנהל מאגר תורמים.
לכל תורם יש רשימת תרומות וכן יומן התקשרויות.
אני יודע לסכם את כל התרומות שהתקבלו לפי תורם.כעת אני רוצה לעבור לצד השני של הנתמכים.
אני רוצה לנהל מאגר של משפחות נתמכות, ולייעד אליהם את התרומות שהתקבלו.
אבל יש לי כמה כללים:
-
לכל משפחה במערכת, אני רוצה לנהל רשימה של תורמים פוטנציאליים, שנראה לי שהם יתחברו למקרה שלה, ויהיו מעוניינים לתרום לה, כך שעבודת השנור שלי תהיה יותר ממוקדת.
-
התורם הנדיב, בבואו לקיים מצווות צדקה, יוכל לבחור מתוך שתי אפשרויות של תרומה:
א. לייעד את התרומה עבור משפחה מסויימת, מתוך רשימת המשפחות שמשודכות אליו.
או
ב. לתרום תרומה כללית לקופה, ללא ייעוד למשפחה מסויימת. במקרה הזה, תהיה לי האפשרות, כמנהל של המערכת (נרו יאיר), לייעד את התרומה לאיזו משפחה שארצה.נשמע מורכב? בואו נתחיל עם מה שכבר אנו יודעים.
ניצור טבלה חדשה עבור המשפחות, עם השדות הבאים:
הערה: מובן שרשימת השדות לעיל היא חלקית ואינה מספקת. לצורך ההדרכה, יצרתי בכוונה רק את השדות הבסיסיים ביותר.
שימו לב, ששדה תיאור המקרה מוגדר כטקסט קצר. ולכן הוא מוגבל עד 255 תווים. לכאורה טיפוס הנתונים העדיף לשדה זה היא "טקסט ארוך". באופן כללי אני נמנע מלהשתמש בטיפוס הנתונים הזה באקסס.
למה?
טקסט ארוך מאוחסן בבסיס הנתונים באופן שונה לגמרי מטקסט קצר. בעבר יצא קול על אקסס בנוגע לשגיאות שהטיפוס הזה גרם. במקרים שבהם אני ממש זקוק לשדה עם טקסט ארוך, אני שומר את השדה הזה בקובץ נתונים נפרד לגמרי משאר הנתונים.
הערה: שימו לב גם לשדה האחרון, ששומר את זמן יצירת הרשומה. אקסס לא מספקת לנו מידע על זמן יצירת הרשומה בטבלה, והרבה פעמים זה מסייע מאוד בתחזוקה. בתור הרגל טוב אני מוסיף אותו כמעט לכל טבלה.
נשמור את הטבלה בשם Family.
עכשיו יש לנו בעיה.
איך מגדירים את קשרי הגומלין בין המשפחות לתורמים?
אחרי ההתבוננות, נשים לב כי לכל תורם יכולות להיות כמה משפחות קשורות, אז לכאורה זה קשר של יחיד-רבים... אבל גם לכל משפחה יכולים להיות כמה תורמים קשורים!
אחרי עוד קצת עיון
, נראה שהדרך לשמור את הנתונים על הקשרים בין התורמים למשפחות, היא על ידי רשימה של זוגות:משפחה-תורם.
משפחה-תורם.
משפחה-תורם.לקשר הזה קוראים קשר של רבים לרבים. והדרך ליישם אותו בבסיס הנתונים היא על ידי טבלת עזר, או טבלת זוגות.
צרו טבלה חדשה עם השדות הבאים:
נסו לשמור את הטבלה בשם: Family_Donor... אקסס תקפיץ לכם את החלון הבא:
מה קרה?
אקסס דואגת לנו שנעבוד נכון!לא הגדרנו שדה מזהה בטבלה, ואקסס מתריעה על כך.
הממ... נוכל להוסיף את השדה ID כמו קודם... אבל אם נחשוב על כך, אין לו כל כך משמעות בטבלה הזו, כי עבור כל מזהה משפחה FamilyID, אין לי חשיבות לשורה מסויימת אחת בלבד של מזהה תורם DonorID, אלא אני תמיד אצטרך לייבא את כל השורות ששייכות אליה בבת אחת, מבלי חשיבות לסדר.
הנכון הוא לקבוע כי שתי השדות גם יחד הם שדה המפתח של הטבלה. הגדרה כזו נקראת מפתח מרוכב (Composite Key)
לשם כך נבחר את שתי השורות יחד, על ידי גרירה של הריבוע האפור מימין לשם השדה:
עכשיו נלחץ בתפריט על הסמל של המפתח הראשי, ונבחין כי נוסף סימון של מפתח לפני שתי השורות:
מעולה. עכשיו נוכל לשמור את הטבלה בשלווה, בלי שום התראות מאקסס.
נעבור כעת להגדרת קשרי הגומלין, ונוסיף את הטבלאות החדשות Family, Family_Donor למשטח העבודה.
נגדיר את קשרי הגומלין לפי התמונה הבאה:
שימו לב שניתן להסיק מהתמונה המדהימה הזו, כי הקשר בין התורמים למשפחות הוא קשר של רבים-לרבים, כי לפי הסימון על החיצים לכל משפחה יכולות להיות הרבה רשומות קשורות בטבלת הזוגות, וכן לכל תורם יכולות להיות הרבה רשומות קשורות בטבלה הזו.
כל הכבוד!שיעורי בית
צור שני טפסים חדשים, אחד עבור המשפחות, ואחד עבור טבלת הזוגות.בפוסט הבא ניצור את הטפסים המתאימים לניהול הטבלאות שהוספנו.
-
-
(אני זוכר שהבטחתי להמשיך עם הטפסים, אבל עכשיו זה הזמן המתאים להכיר את... )
חלק יג: נפלאות התיבה המשולבת
כל טופס מכיל בתוכו פקדים. הפקדים הם הרכיבים החזותיים המרכיבים את ממשק המשתמש.
אם נתבונן בטפסים שאקסס הכינה עבורנו, נראה שיש שם שני סוגי פקדים: תווית, ותיבת טקסט:
נבחין בין שתי מחלקות של פקדים:
- פקד סטטי - פקד שהערך שהוא מציג הוא קבוע. כלומר המשתמש לא יכול לשנות ישירות את הערך שלו. התווית, למשל, היא פקד סטטי.
- פקד דינמי - פקד שהערך שהוא מציג אינו קבוע. כלומר המשתמש יכול לשנות ישירות את הערך שלו. תיבת הטקסט, למשל, היא פקד דינמי.
את הפקדים הדינמיים גם נחלק לשני סוגים:
- פקד מאוגד - פקד הקשור ישירות לשדה כלשהו בטבלה. שינוי של הערך בפקד - גורר עדכון מיידי של הערך שלו בטבלה. תיבת שם המשפחה של התורם, למשל, היא פקד מאוגד.
שימו לב שזה נכון רק בכיוון אחד, דהיינו מהטופס לטבלה, לעומת זאת בכיוון ההפוך זה לא כך: אם הערך של השדה בטבלה ישתנה – השינוי לא ישתקף בפקד עד לריענון הטופס. (ישנם אירועים שונים שגורמים לטופס להתרענן, וכן ניתן תמיד לרענן את הטופס על ידי הקשה על מקש F5)
- פקד לא מאוגד - לא מאוגד לשדה כלשהו בטבלה.
שימו לב, הפקד הלא מאוגד מסוגל להכיל ערך, אך הערך אינו נשמר בטבלה, אלא מאוחסן ברמת הטופס בלבד. לפקד הלא מאוגד שימושים חשובים.
התוית ותיבת הטקסט הם מן הסתם הפקדים הנפוצים ביותר.
במקום השלישי והמכובד, נמצאת התיבה המשולבת (ComboBox)
בשונה מתיבת טקסט פשוטה, התיבה המשולבת מאפשרת לנו לבחור ערכים מתוך רשימה מוגדרת מראש, ובכך היא:
- מקצרת את זמן הכנסת הנתונים – כי היא יכולה להשלים את הטקסט באופן אוטומטי.
- מוודאת את תקינות הנתונים שהכנסנו – כי היא מאפשרת ערכים מוגדרים מראש, כנ"ל.
- מאפשרת לנו להציג באלגנטיות את הנתונים בצורה קריאה, עבור שדות מפתח.
היתרונות האלו משדרגים את חווית המשתמש באופן מהותי.
בואו נראה.פתחו את טבלת התרומות Donation בתצוגת עיצוב. עברו לשדה DonorID, ולמטה במפייני השדה שלו, בחרו בלשונית "בדיקת מידע":
נבחין כי כרגע השדה מוגדר כתיבת טקסט. אם נקליק עם הסמן על השורה, נראה בצד שמאל שלה חץ קטן למטה, נלחץ עליו ונבחר מהרשימה את התיבה המשולבת:
נקבל שלל של אפשרויות חדשות!
הכניסו את הערכים הבאים, כפי שהם מופיעים בתמונה:
שימרו את הטבלה ועיברו לתצוגת גליון נתונים. בשורה הראשונה, כנסו לשדה של DonorID ותבחינו כי יש חץ קטן למטה בסוף השדה. זה מציין כי השדה מוצג כתיבה משולבת. לחצו על החץ:
אקסס תציג לנו את שם התורם המלא ליד המזהה שלו!איך זה קרה?
בואו נחזור על מה שעשינו:א. שינינו את מאפייני השדה כך שיציג תיבה משולבת במקום תיבת טקסט רגילה.
ב. הגדרנו את מקור השורה של התיבה המשולבת להיות השאילתא qryDonor_FullName שכזכור, מציגה את שם התורם המלא לצד המזהה שלו.
ג. הגדרנו את מונה העמודות ל-2, כך שיוצגו שתי העמודות הראשונות בשאילתא.כעת שימו לב שבהגדרות התיבה המשולבת, מוגדרת העמודה המאוגדת להיות מספר 1:
העמודה המאוגדת היא הערך האמיתי שנשמר בשדה שבטבלה. במקרה שלנו, העמודה הראשונה של השאילתא qryDonor_FullName היא המספר המזהה-ID של התורם (ולא השם המלא-FullName)מה שמביא אותנו לשלב הבא:
בואו נסתיר לגמרי את המזהה של התורם!כנסו שוב למאפייני השדה של DonorID, והפעם, הוסיפו במאפיין "רוחב עמודות" את הטקסט הבא
4;0:
כך אנו מגדירים את רוחב העמודות שיוצגו בתיבה המשולבת (משמאל לימין). דהיינו:
עמודה מספר 1 תוצג ברוחב 0 ס"מ (=מוסתרת)
עמודה מספר 2 תוצג ברוחב 4 ס"מ
שימרו את הטבלה ועברו לתצוגת גליון נתונים. שימו לב שעכשיו מוצגים שמות התורמים בלבד, ללא המזהה שלהם:
שנו את תרומה מספר 1 לתורם ישראל יעקב:
ראיתם כמה שזה קל וברור! זוהי העוצמה שבתיבה המשולבת. הנה כל היתרונות שציינתי בהתחלה:1. מקצרת את זמן הכנסת הנתונים – ברור.
2. מוודאת את תקינות הנתונים שהכנסנו – נסו להקליד שם תורם שלא קיים...
3. מאפשרת לנו להציג באלגנטיות את הנתונים בצורה קריאה, עבור שדות מפתח - כמו שציינתי, למרות שהערך המוצג הוא שם התורם, הנתון שמאוחסן בפועל בטבלה הוא המזהה של התורם (ID).מקווה שהכל היה ברור. מעכשיו התיבה המשולבת תהיה הפקד החביב עלינו.
שיעורי בית:
- צרו שאילתא חדשה: qryFamily_FullName ובה שני שדות: המזהה של המשפחה ID, ושדה מחושב FamilyFullName שמחבר את שם המשפחה, שם האב, וא"ו החיבור ושם האם, כך שתתקבלנה התוצאות הבאות:
ID FamilyFullName 1 ראובני אברהם ושרה 2 שמעוני יצחק ורבקהוכו'.
- בטבלה Family_Donor שנו את הגדרות השדה FamilyID כך שיציג (בתיבה משולבת) את שם המשפחה המלא במקום את המספר FamilyID (השתמשו כמובן בשאילתא שהכנתם בסעיף א), ואת הגדרות השדה DonorID כך שיציג את שם התורם.
בהצלחה רבה!
בפוסט הבא נציג אי"ה את הפתרונות (גם לשיעור שעבר).
-
חלק יד: פתרונות לשיעורים הקודמים ויישום של התיבה המשולבת
צור שני טפסים חדשים, אחד עבור המשפחות, ואחד עבור טבלת הזוגות.
טופס המשפחות
שני קליקים וסיימנו:
שימו לב טוב שאקסס הוסיפה עבורנו בתחתית הטופס פקד המכיל טבלה. מזהים? זו רשימת התורמים שמשוייכים למשפחה הזו בטבלת הזוגות Family_Donor.
אקסס זיהתה לבד את סוג הקשר שיש בין טבלת המשפחות וטבלת התורמים (רבים לרבים) ובחרה ליצור עבורנו את התצוגה הזו, כדי שנוכל לנהל בקלות את היחס ביניהם. חכם מצידה.
טופס הזוגות:
לכאורה כמו קודם, נבחר את הטבלה, נלחץ על הכפתור ליצירת טופס - והנה:
אבל רגע.
שימו לב שזה טופס שמציג בכל פעם רשומה אחת בלבד. זה מאוד לא נוח לעבודה שלנו.הייתי רוצה טופס שמציג את כל הרשומות, שורה אחר שורה, כך הרבה יותר קל לנהל את רשימת הזוגות. לשם כך ניצור טופס טבלאי. זהו טופס שמכיל רשומות מרובות, שורה אחר שורה.
בואו נמחק את הטופס שיצרנו, והפעם נשתמש באשף ליצירת טופס טבלאי.
כמו קודם, ראשית נבחר את הטבלה Familiy_Donor, אך הפעם נבחר מתפריט העיצוב: "טפסים נוספים" > "פריטים מרובים":
נקבל את הטופס הזה:
נשמור אותו בשם frmFamily_Donor.
יופי. הלאה.
צרו שאילתא חדשה: qryFamily_FullName ובה שני שדות: המזהה של המשפחה ID, ושדה מחושב FamilyFullName שמחבר את שם המשפחה, שם האב, וא"ו החיבור ושם האם.
פתרון:
עברית קשה שפה:
יש כאן חלק מעצבן עם וא"ו החיבור, כששמים אותה באותה מחרוזת עם תו הרווח, זה נראה כאילו האות קודמת לרווח. אהבתי את הפתרון היצירתי של @בערל, להוסיף את הרווח בנפרד ואחר כך את האות וא"ו.
שתי נקודות!בטבלה Family_Donor שנו את הגדרות השדה FamilyID כך שיציג (בתיבה משולבת) את שם המשפחה המלא במקום את המספר FamilyID (השתמשו כמובן בשאילתא שהכנתם בסעיף א), ואת הגדרות השדה DonorID כך שיציג את שם התורם.
הנה מאפייני השדה FamilyID:
מאפייני השדה DonorID כמעט זהים, אלא שמקור השורה שם הוא שונה, כמובן, דהיינו qryDonor_FullName.
והנה התוצאה:
קריא וברור!אבל יש בונוס: הואיל וביצענו שינוי בהגדרות תצוגת השדה DonorID בטבלת הזוגות, כך שיציג תיבה משולבת ולא תיבת טקסט פשוטה, כעת השתנתה גם התצוגה בטופס המשפחה לתיבה משולבת, ושם התורם מופיע במלואו:
כמה קל עכשיו לשייך תורמים למשפחה!אבל משום מה בטופס הזוגות, התצוגה לא השתנתה, ומוצגים שם רק המזהים של המשפחות והתורמים, בתיבת טקסט פשוטה ולא בתיבה משולבת. תראו:
למה?
כי יצרנו את הטופס לפני ששינינו את הגדרות התצוגה לשדות אלו.
מה נעשה?
אפשר למחוק את הטופס וליצור שוב. הפעם אקסס תזהה את הגדרות התצוגה העדכניות של השדות ותיצור תיבות משולבות. זה הפתרון הכי קל.אני לא רוצה לבחור בדרך ההרסנית הזו... כי מה קורה אם כבר בינתיים הוספנו עוד דברים בטופס, כגון לשנות את הכותרת שלו לדבר יותר קריא, למשל:
ומה אם כבר נקשרנו לטופס הזה, ואנחנו רוצים לחוס על חייו?בואו נלמד עכשיו איך אפשר להגדיר את השינוי בתצוגה באופן ידני בטופס הקיים, מבלי לאבד את מעשה ידינו עד כה.
פתחו את הטופס בתצוגת עיצוב.
הקליקו קליק ימני על תיבת הטקסט של FamilyID > שנה ל: > תיבה משולבת:
תיבת הטקסט הפכה לתיבה משולבת. עכשיו נגדיר את המאפיינים של התיבה המשולבת:
- בכרטסת נתונים הגדירו את מקור השורה להיות: qryFamily_FullName:
2.בכרטסת תבנית הגדירו את מונה העמודות להיות 2, ואת רוחב העמודות ל 4;0:
(נשמע מוכר, לא?)
אני משאיר לכם להבין איך הפכתי גם את תיבת הטקסט השנייה של DonorID לתיבה משולבת.
הנה התוצאה (לאחר קוסמטיקה קלה של הרחבת העמודות בטופס):
את הפוסט הבא נקדיש לטיפול יסודי ולכמה שינויים בטבלת התרומות ובטופס של התרומות, כך שנוכל לשייך כל תרומה חדשה למשפחה, כפי שתכננו.
בתור הכנה לשלב הבא:
- הוסיפו שדה חדש לטבלת התרומות, בשם: TargetFamilyID, מטיפוס מספר שלם ארוך, שיכיל את המזהה של המשפחה שעבורה מיועדת התרומה.
- קיבעו את ערך ברירת המחדל לשדה זה להיות NULL במקום 0 (למתבוננים: מדוע?)
- הגדירו את קשרי הגומלין עבור שדה זה.
- בכרטסת נתונים הגדירו את מקור השורה להיות: qryFamily_FullName:
-
חלק טו: שדרוג התרומות
קודם כל פתרונות:
- הוסיפו שדה חדש לטבלת התרומות, בשם: TargetFamilyID, שיכיל את המזהה של המשפחה שעבורה מיועדת התרומה.
- קיבעו את ערך ברירת המחדל לשדה זה להיות NULL במקום 0.
פתרון:
(למתבוננים: מדוע?)
כי אין משמעות לערך 0. הרי מעולם לא תהיה משפחה עם ID 0, לכן יותר מדוייק להקצות את NULL, שפירושו: כלום.
הערה: גם אם אקסס לא מאפשרת ערך 0 בשדה מספור אוטומטי, והוא תמיד מתחיל מ-1, יש מערכות DB שמאפשרות ליצור שדה מספור אוטומטי שמתחיל מ-0 (או אפילו מערך אחר). במקרה כזה הנתונים שלנו עלולים להטעות אותנו!
גם לגבי פונקצית ספירה Count, שדה המכיל ערך 0 - נספר, בעוד שדה המכיל NULL - לא נספר.בתור פרקטיקה טובה, נשתמש בכלל הבא:
בכל מקום שאין משמעות לערך ברירת המחדל 0, נעדיף את NULL.אם אנחנו כבר כאן, אז על הדרך בואו נשנה גם את המאפיינים של השדה כך שיציג את שם המשפחה המיועדת כתיבה משולבת:
- הגדירו את קשרי הגומלין עבור שדה זה.
פתרון:
יש לנו הרבה עבודה היום, אז קדימה:
נפתח את טופס התרומות, ונראה כי שדה המשפחה המיועדת אינו קיים בו כלל, לכן פשוט נמחק את הטופס.
הערה: הפעם נמחק וניצור טופס חדש, כי הטופס הקיים הוא בתצוגת גליון נתונים, משולל כל עיצוב וחן, וממילא לא נפסיד כלום אם פשוט ניפטר ממנו.
הנה הטופס החדש, שימו לב לשדה של משפחת הייעד:
הערה: בפעם הראשונה שיצרנו את טופס התרומות, שינינו את תבנית העיצוב שלו לגליון נתונים. שימו לב שהפעם, בניגוד לטופס הקודם, נשאיר את הטופס בתבנית שלו, שמציגה רשומה בודדת.
הסיבה לכך היא כי אני מעוניין להשתמש בטופס הזה כדי להקליד נתונים. ובדרך כלל מכניסים תרומה בודדת בכל פעם.
תיכף ניצור טופס נוסף עבור התרומות, אשר יהיה בתצוגת גליון נתונים, המציגה רשומות מרובות.נשמור את הטופס החדש בשם frmDonationCard.
נחזור על השלב האחרון שוב, ניצור טופס לתרומות כמו קודם, אך הפעם נשנה את תבנית העיצוב שלו לגליון נתונים, וכן נמחק את הפקדים של ID ו- DonorID:
את הטופס השני נשמור בשם: frmDonationList.
עדכון הטופס של התורם
נסו לפתוח את טופס התורמים frmDonor. תקבלו הודעת שגיאה:
השגיאה נוצרה כי מחקנו את הטופס frmDonation, שהופיע כטופס משנה בטופס התורם, ואקסס לא מוצאת אותו.
לחצו על אישור, שימו לב כי נותר חלל ריק במקום שבו היה טופס המשנה:
בואו נתקן את הבעיה. נעבור לתצוגת עיצוב (אקסס שוב תתריע על השגיאה. נלחץ אישור)
נבחר את פקד טופס המשנה:
ובמאפיינים, נשנה את אובייקט המקור של הפקד להיות frmDonationList:
שימו לב שקישור שדות האב עדיין מכיל את הערך ID, וקישור שדות הצאצא עדיין מכיל את הערך DonorID:
הערה חשובה: אני מקווה שחלקכם מרימים גבה ושואלים:
כיצד יתכן שטופס המשנה יהיה מקושר על ידי השדה DonorID? והלא מחקנו את הפקדים של DonorID מטופס המשנה!
שאלה מצויינת!
התשובה היא, שלמרות שמחקנו את הפקדים מן הטופס - לא מחקנו את השדה DonorID עצמו.
הטופס מכיל את ערכי כל השדות של הטבלה שעליה הוא מבוסס, גם אם הוא אינו מציג את כולם.
הפקדים משמשים רק לתצוגה של השדות, ומחיקת פקד מאוגד אינה מוחקת את השדה שאליו הוא מאוגד, בדיוק כמו שסגירת התריס אינה מסירה את השמש מן הרקיע.נעבור לתצוגת טופס ונראה שהכל תקין:
נסגור ונשמור.עדיין חסרה לנו פונקציה עיקרית וחשובה.
הגבלת רשימת התורמים לתורם
נזכר באפיון המערכת שהגדרנו בחלק יב:התורם הנדיב, בבואו לקיים מצווות צדקה, יוכל לבחור מתוך שתי אפשרויות של תרומה:
א. לייעד את התרומה עבור משפחה מסויימת, מתוך רשימת המשפחות שמשודכות אליו.
או
ב. לתרום תרומה כללית לקופה, ללא ייעוד למשפחה מסויימת. במקרה הזה, תהיה לי האפשרות, כמנהל של המערכת (נרו יאיר), לייעד את התרומה לאיזו משפחה שארצה.נתחיל באפשרות א:
רצינו שכל תורם יוכל לשייך את תרומותיו רק אל המשפחות המשוייכות אליו, וכרגע מוצגות לכל תורם כל המשפחות הקיימות.
לשם כך אנחנו רוצים לסנן את התיבה המשולבת של TargetFamilyID שבטופס המשנה frmDonationList, כך שתציג רק המשפחות המשוייכות לתורם הנוכחי בטופס האב frmDonor.אבל קודם כל, נפתח את הטופס frmFamily_Donor ונמלא קצת יותר נתונים:
המשפחות "ראובני אברהם ושרה" וכן "שמעוני יצחק ורבקה" מקושרות לכל התורמים. לעומתן, המשפחות "זבולוני יעקב ולאה", וכן "אשרי אדם וחוה", משוייכות כל אחת רק לתורם אחד, כדלקמן:
שלב א: יצירת שאילתת עזר לתיבה המשולבת
כרגע, התיבה המשולבת מכילה רק נתונים לגבי המשפחות, ולא לגבי התורמים המשוייכים אליהן. כדי להציג רק בתיבה המשולבת רק את המשפחות המשוייכות לתורם הנוכחי, אנו צריכים קריטריון לסינון.למעשה, אנו מבקשים להציג שלש שדות:
ID, FamilyFullNameמתוך השאילתה qryFamily_FullNameDonorIDמתוך הטבלה Family_Donor
לשם כך ניצור שאילתת עזר, שמצרפת את שתי הטבלאות הנ"ל.
הערה: מי שאזניו רגישות (או יותר נכון, עיניו) שם לב שאני לפעמים קורא לשאילתה בשם "טבלה". מבחינת אקסס, אין הבדל עקרוני בין השניים. ניתן לבסס טפסים הן על שאילתות והן על טבלאות, ניתן לבסס שאילתות על שאילתות וכו'.
יצירה > עיצוב שאילתה > ונבחר להציג את הכל:
יתכן והתצוגה בגרסת אקסס שלכם שונה מעט. הפאנל האפור שבתמונה נכנס רק לאחרונה כשדרוג לאקסס.עובדה מעניינת:
למרות שבשנת 2017, כשאקסס הגיעה לגיל המופלג של 25 שנה, מייקרוסופט הודיעו קבל עם ועדה כי הם מתכוונים לזנוח סופית את אקסס, (ואף קבעו לה תאריך פטירה), בשקט אלגנטי הם שינו את דעתם, הוציאו גרסאות חדשות בשנים 2019 ו-2020, ועדיין ממשיכים לעדכן את אקסס.נחזור לענייננו.
נוסיף למשטח העבודה את הטבלה Family_Donor, ואת השאילתא qryFamily_FullName.
נגדיר את הצירוף הפנימי בין השדות ID ו-FamilyID, ונוסיף לשאילתא את השדות לפי הסדר הבא:- ID
- FamilyFullName
- DonorID
נעבור לתצוגת עיצוב ונראה שליד כל משפחה מופיעה גם עמודת התורם המשוייך אליה:
מעולה.נשמור את השאילתה בשם: qryFamily_FullName_Donor.
שלב ב: החלפת מקור השורה של התיבה המשולבת בטופס המשנה
נפתח את טופס המשנה qryDonationList בתצוגת עיצוב, נסמן את התיבה המשולבת של משפחת הייעד, ובמאפיין מקור שורה נמחק את הכתוב לגמרי, כך:
כעת נלחץ על שלושת הנקודות משמאל למקור השורה:
אקסס תכניס אותנו למסך עורך השאילתות. נוסיף למשטח העבודה את שאילתת העזר שלנו: qryFamily_FullName_Donor, ונוסיף את השדות לפי הסדר כמקודם:
כעת לשלב המכריע:
נעמוד על הקריטריון של DonorID, ונפתח את הבונה:
- בחלונית שנפתחה, נבחר מצד ימין את הקובץ של אקסס שלנו > Forms > כל הטפסים > frmDonorCard. (טופס האב)
- מקטגוריות הביטויים נבחר את ID. (מזהה התורם)
- ומתוך ערכי הביטויים נקליק פעמיים על "<ערך>" (הפריט הראשון)
אקסס תבנה עבורינו את הביטוי הבא:
[Forms]![frmDonor]![ID]להלן ממוספרים כל שלבי התהליך:
נלחץ על אישור ונסגור את עורך השאילתות.
שימו לב לקריטריון שנוסף לעמודה DonorID:
הנתיב
[Forms]![frmDonor]![ID]מציין את השדה ID בטופס frmDonor.פירוש הדבר הוא שאקסס תסנן עבורנו את תוצאות השאילתא, ותציג רק את הרשומות שבהם DonorID זהה לשדה ID שמופיע בטופס frmDonor.
יתר על כן, אקסס תדאג לכך שכל פעם שערך השדה ID ישתנה בטופס האב (דהיינו כשנציג תורם אחר) - השאילתה תרוץ מחדש, תעדכן בטופס הצאצא את התיבה המשולבת המבוססת על השאילתא הזו, ותספק לנו נתונים עדכניים לתורם החדש.הערה: כבר הזכרנו לעיל שאקסס משלבת בין בסיס נתונים, וממשק משתמש בצורה הכי חלקה וקלה שאפשר. היכולת של אקסס לערבב בין נתונים של שדות טופס לבין קריטריונים של שאילתות מקנה לה עוצמה רבה, והיא המאפשרת לנו ליצור את הלוגיקה העסקית של המערכת, מבלי לכתוב שורה אחת של קוד.
וזה רבותי - פשוט מדהים.כעת נסגור את עורך השאילתות, על ידי האיקס למעלה:
אקסס תשאל האם אנו רוצים לשמור את השינויים שעשינו במאפיין של מקור השורה:
נבחר כן.
נסגור את תצוגת העיצוב של הטופס, ונשמור כמובן את השינויים שעשינו בו.נפתח את טופס האב frmDonor, ונראה לאילו משפחות ניתן כעת לשייך את התרומות של כהן אברהם:
נשים לב שאלו המשפחות המשוייכות אליו בטבלה Family_Donor:
נעבור ללוי יצחק, ונראה שאצלו קיימות 3 משפחות אפשריות, כי אקסס עידכנה את התיבה המשולבת עבורו:
זו בדיוק הפונקציונאליות שביקשנו!
אבל היתה עוד אפשרות:ב. לתרום תרומה כללית לקופה, ללא ייעוד למשפחה מסויימת. במקרה הזה, תהיה לי האפשרות, כמנהל של המערכת (נרו יאיר), לייעד את התרומה לאיזו משפחה שארצה.
פירוש: אם התורם לא יבחר משפחה ביעד, לי כמנהל תהיה האפשרות לשייך בעצמי את התרומה, גם למשפחה שאינה משודכת עמו.
נראה כי אופציה זו כבר קיימת בטופס התרומה frmDonationCard, כי שם לא ביצענו שום סינון על השדה TargetFamilyID.
למשל עבור אברהם כהן, נוכל לבחור גם את משפחת זבולוני או אשרי:
המשימה הושלמה בהצלחה!
ננוח קצת, כי כבר עלו בדעתי שתי בעיות שעלולות לצוץ במערכת שלנו.
אולי אתם כבר יודעים? (אם כן, ציינו בתגובות)
בפוסט הבא נציין אותן, ונציע שיפורים חשובים למערכת שיצרנו כאן, כפתרון לשתי הבעיות האלו.
עד כאן להיום, שבת שלום!
-
חלק טז: מחשב מסלול מחדש
הערה: החלק הזה במדריך לכאורה חורג מעט מההדרכה, ומנסה לתאר מצב שאליו אנו עלולים להגיע כמפתחים. הוא נולד כתוצאה משיחה שהיתה לי אמש עם מפתח אחר, עם זאת, אני רואה בו חלק חשוב מהמדריך, וחושב שהתועלת ממנו רבה למפתחים בכלל, לא רק באקסס.
אז אם אתם מחכים רק לפתרון של החלק הקודם, תמחלו לי.
אני מרגיש נוח באכסניה הזו להשתמש לפעמים בסגנון בלוגי משהו...איפה עצרנו?
בחלק הקודם הזכרתי שיש שתי בעיות במערכת שיצרנו.
- כשהמנהל ישייך תרומה למשפחה שאינה קשורה לתורם, אז ברשימת התרומות שבתחתית טופס התורם לא יופיע שם המשפחה עבור התרומה הזו (כיוון שכך הגדרנו את התיבה המשולבת שמציגה את שם המשפחה, שלא תציג רשומות עבור משפחות שאינן קשורות לתורם).
זה עלול ליצור את הרושם כאילו התרומה לא משוייכת לאף משפחה, ואז יתכן שמשתמש רגיל (שאינו מנהל) ינסה בתמימות לשייך את המשפחה לאחת המשפחות שכן קשורות לתורם, ובעצם יעביר את התרומה מהמשפחה שיועדה על ידי המנהל למשפחה אחרת... בקיצור תוהו ובוהו.
מה שמוביל אותי לבעיה השנייה והעקרונית:
- האם בכלל אפשר להעביר תרומה ממשפחה אחת לאחרת? כל זמן שהתרומה לא בוצעה בפועל, כלומר שהתורם העביר למערכת את הכסף, אך אנחנו עדיין לא העברנו אותו למשפחה – ייתכן ונרצה לאפשר לשנות את הייעוד. אבל אם התרומה כבר הועברה למשפחה, אסור שתהיה אפשרות לשנות את הייעד.
אל תדאגו, יש פתרון לבעיות הנ"ל, חשבתי אפילו על כמה.
אז מה עושים?
(אזהרה: הקטע הבא עלול לגרום לחלק מהקוראים תחושת בלבול, זה מכוון ואני מתנצל מראש. ניסיתי להעלות על הכתב מה שעבר במוחי הקודח כשצצו בו הבעיות הנ"ל.)
פתרון ראשון: בלי חרטות.
אפשר להחליט לנעול רשימת תרומה שכבר מיועדת למשפחה, כך שלא תהיה אפשרות בכלל לשנות את הייעד.זו האופציה הכי קלה. החיסרון הגדול שלה הוא שאם בחרנו בטעות משפחה לא נכונה – השדה יינעל ולא נוכל לשנות את הייעד. נצטרך למחוק את הרשומה וליצור חדשה.
נוכל לשפר את האפשרות הזו על ידי אפשרות פתיחת הנעילה על ידי הקשת קוד מנהל.
אבל נצטרך להשתמש בקוד VBA (או במאקרו רח"ל) - ואין לנו עדיין שום הכירות עם התחומים האלו.
מה נעשה, נתפשר על נעילה וזהו?
במחשבה שניה זה לא נורא, בטופס של כרטיס התרומה הרי לא ננעל את שדה משפחת היעד, כך נוכל לשנות אותה אם נרצה.
רגע, ואם נשנה בטעות?
אוף!פתרון 2: אזהרה
אפשר להציג ליד כל תרומה שמיועדת למשפחה שאינה שייכת לתורם, סימן התראה כלשהו שמציין שאע"פ ששדה המשפחה נראה ריק, אין האמת כן, אלא ידע הנציג שהתרומה כבר משוייכת ולא יעז לגעת בה.זה ממש פתרון עלוב, כי הוא משאיר את האחריות ביד המשתמש.
ומה אם הוא לא ישים לב לסימן?
בעיני זה שווה ערך ל"פתרון" שבו נתלה מצידי המסך פשקאווילים שמזהירים באותיות קידוש לבנה: "אסור לשייך תרומה לפני שבדקת בשבע בדיקות וחקירות האם המשפחה שייכת לתורם זה. ראה הוזהרת!"
שוב אוף!פתרון שלישי: אחורה פנה
אפשר להחזיר את המצב לקדמותו, לבטל את הסינון מהתיבה המשולבת, כך שתשוב להציג את כל המשפחות, אבל כאשר נבחר משפחה שאינה משוייכת לתורם, המערכת תגיב באחת מהדרכים הבאות:
- לא תאפשר לנו בכלל
- תאפשר לנו, אבל תציג הערת אזהרה
- תבקש סיסמת מנהל על מנת לאפשר את החריגהאפשר לשפר את האפשרות הזו, על ידי שילוב עם הפתרון השני, הצגת כל המשפחות בתיבה המשולבת, אבל להוסיף סימון מיוחד ליד משפחות שאינן קשורות לתורם, כך שמראש ידע הנציג שנדרש עבורם אישור מנהל.
קצת כואב לי שכל העמל של החלק הקודם ירד לטמיון. אבל לכאורה זה הפתרון הכי טוב.
אבל הוא דורש ידע בכתיבת קוד VBA, שלעת עתה אין לנו. אולי נעזוב הכל ונתחיל ללמוד VBA?
וואו. כואב לי הראש.כל השקלא וטריא הזו מעלה נקודה מאוד חשובה.
סוף מעשה במחשבה תחילה
השאלות האלו היו אמורות לצוץ כבר בשלב התכנון.
את ההדרכה פתחתי במשפט מעין: השלב הראשון, והחשוב ביותר ביצירת מערכת הוא התכנון.
אבל לא שמעתי לעצתי, התחלתי מיד ליצור טבלאות, טפסים, שאילתות, וכו' וכו'.
למה?
כי לדעתי זו דרך מצויינת ללמוד. ליגרס איניש והדר ליסבר.
אבל זו דרך גרועה מאוד ליישם למעשה את מה שלמדתי.
באופן אישי, הדחף הראשוני שלי כמתכנת, הוא ישר להתחיל ליצור. אני כבר רוצה לראות את הכל עובד, בלי להיכנס לדקדוקי עניות איך בדיוק כל פיפס ייושם. אבל, אם אני מתפתה לזה, אני מזמין לעצמי צרות.
כי להכניס שינויים במערכת קיימת זה כמעט תמיד הרבה יותר מסובך מאשר לבנות אותה כך מעיקרא.
אמת היא, כי יש גם צד שני למטבע, והוא להיתקע עד אין סוף בשלב התכנון, מבלי להוריד את הרעיון מעולם האצילות לעולם המעשה.
אבל הניסיון מוכיח שהאופציה הראשונה היא הכי גרועה, ושתכנון טוב ואפיון מדוקדק מחזיר את ההשקעה בכפלי כפליים.תשבו ותתכננו עם דף ועפרון:
- מה המטרות של המערכת
- אילו ישויות קיימות בה (תורמים, משפחות, נציגים, מנהלים, וכו' וכו')
- סרטטו על דף סקיצה כללית של המסכים השונים
- מה הפונקציונאליות שתתאפשר למשתמש בכל מסך
כשהכל על הנייר, אין בעיה לשנות, למחוק, ולהתחיל דף חדש בלי נקיפות מצפון.
אם עברתם את השלב הזה, אתם על דרך המלך.
זהו לבינתיים.ולעניין עצם ההדרכה והפתרון, בפוסט הבא (בקרוב בלי נדר) אני אציג פתרון שאפשר ליישם בלי VBA. הוא לא מושלם, אבל נוכל ללמוד ממנו עוד על אקסס, וזו המטרה העיקרית של המדריך הזה.
- כשהמנהל ישייך תרומה למשפחה שאינה קשורה לתורם, אז ברשימת התרומות שבתחתית טופס התורם לא יופיע שם המשפחה עבור התרומה הזו (כיוון שכך הגדרנו את התיבה המשולבת שמציגה את שם המשפחה, שלא תציג רשומות עבור משפחות שאינן קשורות לתורם).
-
חלק י"ז: פתרון (חלקי) לבעיית שיוך התרומות
נתחיל דווקא מהבעיה השניה:
האם בכלל אפשר להעביר תרומה ממשפחה אחת לאחרת?
וניישם את הכלל שחשבנו עליו:
כל זמן שהתרומה לא בוצעה בפועל, כלומר שהתורם העביר למערכת את הכסף, אך אנחנו עדיין לא העברנו אותו למשפחה – ייתכן ונרצה לאפשר לשנות את הייעוד. אבל אם התרומה כבר הועברה למשפחה, אסור שתהיה אפשרות לשנות את הייעד.לשם כך אנו צריכים שדה נוסף שמציין את הסטטוס של התרומה.
לאחר מחשבה, החלטתי שאני מעוניין בשלוש אפשרויות לשדה הסטטוס:- זמינה התרומה נכנסה למערכת, אך עדיין לא מיועדת עדיין לשום משפחה. בידינו ליעדה למי שנרצה.
- מיועדת התרומה כבר מיועדת לאחת המשפחות, אך עדיין לא הועברה אליה בפועל. כעת רק מנהל יוכל לשנות את ייעוד התרומה.
- בוצעה התרומה הועברה כבר למשפחה, ואין לשנות בה שום דבר.
יתר על כן,
אני רוצה שהסטטוס ייקבע על ידי התוכנה, ולא באופן ידני, כדי למנוע את כל הטעויות שהזכרנו. לשם כך ניצור מנגנון האחראי לעדכון שדה הסטטוס, ולא נאפשר שינוי ישיר שלו.שלב א: הוספת שדה סטטוס תרומה
ניצור טבלה חדשה בשם DonationStatus עם השדות הבאים:
IDמטיפוס מספור אוטומטי. זהו שדה המפתח כמובן.
StatusDescמטיפוס טקסט קצר, אורך מקסימלי 10:
עברו לתצוגת גליון נתונים והכניסו את השורות הבאות:
נפתח את הטבלה Donation ונוסיף שדה חדש StatusID מטיפוס מספר שלם ארוך. ונקצה לו ערך ברירת מחדל 1 (כלומר סטטוס=זמינה):
גם כאן, נעצב את התצוגה של השדה להראות תיבה משולבת, כך שיוצג התיאור של הסטטוס במילים ולא ע"י מספר:
נעבור לתצוגת קשרי גומלין ונקשר את הטבלה החדשה לשדה החדש:
יפה.בואו נפתח את טבלת התרומות בתצוגת גליון נתונים, ונראה כי השדה נוסף וברירת המחדל היא "זמינה":
אבל,שימו לב כי עבור התרומות שכבר קיימות במערכת, השדה ריק. מדוע? כי באקסס, כאשר מגדירים ערך ברירת מחדל לשדה, ההגדרה תקפה רק עבור רשומות חדשות שיתווספו מעתה, ולא מעדכנת עמודות ישנות.אבל ברור שהמצב הזה לא מקובל. אני מעוניין שלכל תרומה יהיה ערך תקין בשדה סטטוס. לכן אין לי ברירה אלא לטפל בעצמי בסטטוס של רשומות ישנות.
שלב ב: עדכון שדה הסטטוס עבור הרשומות הקיימות
דקה למחשבה:
לשלושת התרומות הראשונות אנו יכולים לבחור כל אחד משני הסטטוסים: "מיועדת" או "בוצעה", כי הן כבר מיועדות למשפחה. אבל הסטטוס של התרומה הרביעית יוכל להיות רק "זמינה", כי היא לא מיועדת עדיין לשום משפחה.
כרגע לא נסמן שום תרומה כ"בוצעה".בדוגמא שלנו יש רק 4 שורות, ונוכל לעבור שורה שורה באופן ידני ולשנות אותה.
אבל בדרך כלל, לא נעשה כן.
מה יקרה אם יש 40 שורות? ואם יש 4000? ואם בטעות נסמן סטטוס "בוצעה" לתרומה לא משוייכת? צריך מנגנון שיעשה זאת באופן אוטומטי ונטול טעויות. לשם כך נכיר סוג נוסף של שאילתא:שאילתת עדכון
יצירה > עיצוב שאילתא > הוסיפו את טבלת התרומות, ומתוכה את השדה StatusID לרשימת השדות למטה. כעת בחרו סוג שאילתה "עדכן" מהתפריט:
שימו לב שנוספה כותרת שורה: "עדכן ל:"
בשדה זה אנו אמורים לציין את הערך המעודכן של השדה StatusID. אבל כפי שציינתי, ערך זה תלוי:- אם התרומה לא משוייכת למשפחה, אנו רוצים שערך השדה יתעדכן למספר 1 (=זמינה)
- אחרת, השדה יתעדכן למספר 2 (=מיועדת)
יש יותר מאפשרות אחת לעשות כן. היום נשתמש בפונקצית התנאי הכפול IIF
פונקצית התנאי IIF מקבלת שניים או שלושה פרמטרים, ומחזירה ערך לפי ההגיון הבא:
אם ערך הפרמטר הראשון הוא אמת – יוחזר ערך הפרמטר השני. אם לא – יוחזר ערך הפרמטר השלישי (אם צייננו פרמטר שלישי. אחרת יוחזר NULL)
כנסו לשדה ופתחו את הבונה, ושם כתבו את הנוסחא הבאה:
IIf([TargetFamilyID] Is Null, 1, 2)
חשוב מאד: שימו לב להוסיף את הסוגריים המרובעות מסביב לשם השדה TargetFamilyID, אחרת אקסס תפרש אותו בתור מחרוזת, ותוסיף לו גרשיים, והתוצאות יהיו שגוייות, כי התנאי בפרמטר הראשון תמיד יחזיר שקר.
זהו.
כל מה שנדרש זה פשוט להריץ את השאילתה.
בחרו מהתפריט למעלה: "הפעל". אקסס תשאל האם אתם בטוחים שברצונכם לעדכן 4 שורות:
נבחר כן. ואקסס תעשה את העבודה בשבילנו.
כעת נסגור את השאילתא. אקסס תשאל אם ברצוננו לשמור:
נחשוב: האם אנו צריכים לשמור את השאילתה? כנראה שלא. אנו לא אמורים לשנות באופן גורף את ערך השדה בעתיד.
נבחר "לא".
נפתח את טבלת התרומות ונבדוק שכל הרשומות עודכנו בצורה נכונה:
נראה מצוין!
רבע שעה הפסקה ונמשיך.שלב ג: הגדרת שדה הסטטוס כשדה חובה
למרות שאקסס מספקת לנו ערך ברירת מחדל לסטטוס, עדיין המשתמש יכול למחוק לגמרי את ערך השדה, ולהשאירו על NULL. זה מצב שאנו רוצים לחסום. לכן נגדיר את הסטטוס כשדה נדרש.
שדה נדרש
אקסס מאפשרת לנו להגדיר כי שדה מסויים הוא נדרש, כלומר: מוכרח להכיל ערך. אם השדה הוגדר כנדרש, אקסס לא תאפשר לנו לשמור את הרשומה מבלי לספק ערך תקין לשדה.
כנסו לטבלת התרומות, בחרו את שדה הסטטוס, ושנו במאפיינים את המאפיין נדרש לערך "כן":
שמרו את הטבלה ביציאה ממעצב הטבלה. אקסס תציג את ההודעה הבאה:
הודעה זו מופיעה בכל פעם שמגדירים שדה כנדרש וכבר קיימות רשומות בטבלה.
אקסס שואלת האם ברצוננו לבדוק שהרשומות הקיימות מקיימות את ההגדרות החדשות, או במקרה שלנו, שהשדה StatusID מכיל ערך עבור כל רשומה ורשומה.נבחר "כן", ואקסס תבדוק שהרשומות תקינות. וכיוון שכבר דאגנו לכך מראש, לא תהיה שום בעיה, ואקסס תסגור את מעצב הטבלה.
אם הנתונים היו שגויים (כלומר, היה חסר ערך לשדה הסטטוס בחלק מהרשומות), אקסס הייתה שואלת מה ברצוננו לעשות: לבטל את ההגבלה על השדה, או להתעלם מחוסר התקינות של הנתונים הקיימים, ורק לדאוג לכך מכאן ולהבא (וכן, זה באמת נשמע רעיון מסוכן, ורק לעתים נדירות נבחר בו, לא ניכנס כעת לפרטים).
שלב ד: הוספת שדה הסטטוס לטופס רשימת התרומות ונעילתו
אנו רוצים להציג למשתמש את הסטטוס של כל תרומה. נפתח את הטופס frmDonationList בתצוגת עיצוב, ונבחר מהתפריט למעלה: "הוסף שדות קיימים":
אקסס תציג את כל השדות בטבלה שעליה מבוסס הטופס, דהיינו טבלת התרומות Donation. נגרור את השדה StatusID לתוך הטופס, לפני שדה משפחת הייעד:
כעת ננעל את השדה לשינויים:
נעבור לתצוגת המאפיינים של השדה על ידי מקש F4, ובלשונית נתונים נשנה את ערך המאפיין נעול ל-כן:
שלב ד: ביטול שדה משפחת היעד עבור רשומות עם סטטוס שונה מ"זמינה"
אנו רוצים שהמשתמש לא יוכל לשנות את היעד עבור משפחה שהסטטוס של שונה מ-"זמינה". לשם כך נשתמש בעיצוב מותנה על שדה TargetFamilyID:
סמנו את השדה ובחרו בתפריט "עיצוב" את האפשרות "עיצוב מותנה":
בחרו כלל חדש ובעצב רק תאים שבהם: את האפשרות הביטוי הינו
ובתיבת הביטוי הקלידו
[StatusID] <> 1
נסמן את האפשרות הפוך לזמין, ונשים לב כי התצוגה המקדימה מראה פקד אפור (=לא זמין)
נבחר אישור פעמיים כדי להחיל את כלל העיצוב שיצרנו.
נסגור את הטופס ונשמור. כעת נפתח את טופס כרטיס התורם, ונראה שהשדה החדש מופיע, וכן נראה שכעת יש אינדיקציה על תרומה המיועדת למשפחה שאינה משוייכת לתורם הנוכחי:
נסו לשנות את הסטטוס עבור משפחה כלשהי. שימו לב שאין אפשרות כי השדה נעול.
הוסיפו כעת עוד תרומה לתורם הנדיב כהן אברהם, ייעדו את התרומה עבור "ראובני אברהם ושרה".
שימו לב כי למרות שהתרומה מיועדת, הסטטוס עדיין נמצא על "זמינה":
שלב ה: שאילתה לעדכון הסטטוס
נצטרך ליצור מנגנון שמשנה את הסטטוס עבור כל תרומה כזו. הטוב ביותר הוא לשנות את ערך הסטטוס ברגע שמעדכנים את המשפחה בשדה היעד, אך לצורך זה אנו צריכים להשתמש בקוד VBA. כיוון שמדריך זה באופן עקרוני לא אמור לכלול קוד VBA, נשתמש בשאילתת עדכון.
השאילתה תהפוך את שדה הסטטוס למיועדת עבור כל תרומה שיש לה משפחה בשדה הייעד, אך הסטטוס שלה עדיין נמצא על ברירת המחדל "זמינה".צרו שאילתת עדכון חדשה, והוסיפו למשטח העבודה את טבלת התרומות.
הוסיפו לרשימת השדות למטה את שני השדות StatusID ו- TargetFamilyID.
כעת השלימו את הפרמטרים כפי שמופיע בתמונה:
הגדרה זו פירושה:
עדכן את השדה StatusID כאשר הוא מכיל את הערך 1 (=זמינה) וגם שדה משפחת היעד אינו ריק.
לשם שעשוע, עברו לתצוגת SQL וראו את תחביר השאילתה:
UPDATE Donation SET Donation.StatusID = 2 WHERE (((Donation.StatusID)=1) AND ((Donation.TargetFamilyID) Is Not Null));
המילה UPDATE מציינת כי ברצוננו לעדכן את הטבלה Donation. הפקודה SET… פירושה קבע את שדה Donation.StatusID לערך 2. המילה WHERE מציינת את התנאי לעדכון, והמילה AND מציינת כי שני חלקי התנאי צריכים להתקיים. פשוט, לא?שמרו את השאילתה בשם: qryUpdate_SetDonationStatusToTargeted.
שימו לב, כי אקסס מקצה סמל מיוחד של עיפרון עם סימן קריאה עבור שאילתות עדכון:
כדי לציין שהשאילתה משנה ערכי שדות, ועלינו להשתמש בה בזהירות.כדי להריץ את השאילתה, נלחץ עליה פעמיים. אקסס תשאל האם עשינו זאת בכוונה:
נבחר כן.
אקסס תבדוק כמה רשומות עתידות להיות מושפעות כתוצאה מהרצת השאילתה (במקרה שלנו רק הרשומה האחרונה שיצרנו), ותתריע על השינויים הצפויים:
שוב נבחר כן.
נפתח את טופס התורם ונראה כי הסטטוס לתרומה האחרונה השתנה למיועדת, ושדה משפחת היעד ננעל:
אנחנו מתקדמים.לסיום
הסרת האפשרות לשינוי הסטטוס על ידי המשתמש היא צעד חשוב. אך עדיין יש עוד מה לעשות:- להריץ את שאילתת העדכון כשהדבר מתבקש, למשל עם סגירת הטופס. (ככל הנראה לא יהיה מנוס משימוש ב-VBA למרות הכל...)
- לאפשר שינוי הסטטוס ל"בוצעה" במקרה שאכן התרומה הועברה למשפחה.
נשאיר זאת לפעם הבאה.
שיעורי בית: עריכת טופס כרטיס התרומה.
על פי התכנון, טופס כרטיס התרומה frmDonationCard מיועד למנהל המערכת בלבד. אבל בטופס הזה חסרים שני דברים חשובים:- לא מוצג שדה הסטטוס.
- אנו מעוניינים לאפשר למנהל לשנות רק תרומה זמינה, ולא שכבר מיועדת או בוצעה.
למעשה, זה בדיוק אותו תהליך כפי שעשינו עם הטופס של רשימת התרומות.
בהצלחה! -
חלק י"ח: VBA - חלק א: היכרות, עורך הקוד, ואירועים
המערכת שלנו מוגנת מטעויות אנוש של העברת תרומות בטעות בין משפחות, כי דאגנו לנעול את השדה של היעד ברגע שהסטטוס השתנה למשוייכת. את הסטטוס עצמו לא ניתן לשנות בחופשיות על ידי המשתמש, כי השדה נעול בכל הטפסים.
ברור כי אפשר להכנס לטבלת התרומות ולשנות שם כל דבר. במערכת אמיתית אין למשתמש גישה ישירה לטבלאות בשום אופן. כרגע נתעלם מהנושא, ונניח שהמשתמש יכול לתקשר עם הנתונים רק על ידי הטפסים.
יצרנו שאילתה שמעדכנת את הסטטוס של כל התרומות המשוייכות מ-זמינה ל-משוייכת. כעת עלינו לדאוג שהשאילתא אכן תרוץ בזמן המתאים. זמן טוב לכך הוא כאשר המשתמש סוגר את טופס התורם. כמובן שהתהליך צריך להתבצע באופן אוטומטי.
בתור קו מנחה, נמנעתי מלהשתמש בשפת VBA, הואיל וכתיבת קוד (בכל שפה) הוא נושא מורכב, ורציתי לפשט את המדריך עד כמה שיתן. אבל המציאות הוכיחה שהפתרון הכי טוב הוא בקוד. ולכן אני שמח לחזור בי ולהזמין לבמה את VBA.
60 שניות על VBA
על קצה המזלג, VBA הוא קיצור של Visual Basic for Applications. זוהי שפת תכנות, שמשמשת בעיקר בתוך היישומים הנפוצים של אופיס, דהינו וורד, אקסל וכמובן אקסס.(למעשה זו תת-גרסא ל Visual Basic או בקיצור VB, שפת תכנות ותיקה שפותחה על ידי מייקרוסופט בשנות התשעים (ואשר בעצמה מבוססת על שפת Basic...), ועברה כמה גלגולים עד שנתמזגה לתוך פרוייקט הדוט נט של מייקרוסופט)
ל-VBA כמה יתרונות חשובים:
- היא מאפשרת לנו להרחיב את היכולות של ישומי אופיס מעבר לגבולות הקיימים.
ניתן למשל להוסיף פונקציה לאקסל, (למשל המרת תאריך לועזי לעברי), ולהשתמש בה בדיוק כאילו היא פונקציה מובנית של אקסל, כמו למשל SUM(). - היא שפה מונחית-אירועים, דהיינו אפשר לקבוע שקוד ירוץ רק באירוע מסוים, למשל סגירה של טופס.
- היא מאפשרת לשנות את הקוד בצורה דינמית, כלומר גם בזמן הריצה, דבר שמאוד מקל בפיתוח.
- היא כוללת סביבת פיתוח מובנית עם עורך קוד בשם VBE הכולל כלי פיתוח בסיסיים ומועילים.
כדי לשמור על המדריך פשוט, נשתמש בה רק בטעימות פה ושם. אבל מומלץ למי שמשתמש קבוע ביישומי אופיס להכיר אותה לעומק. זה פותח עולם שלם של אפשרויות.
נחזור לענייננו:
אנו רוצים שבסגירת טופס רשימת התרומות, יקרה התהליך הבא:
- המערכת תבדוק האם קיימות ברשימת התרומות רשומות לעדכון, כלומר משוייכות למשפחה אך הסטטוס = 1 (זמינה).
- המערכת תשאל את המשתמש האם הוא רוצה לעדכן את הסטטוס של התרומות המשוייכות. ואם כן,
- המערכת תריץ את שאילתת עדכון התרומות שיצרנו בפרק שעבר.
כדי לא לגרום לפוסט כבד ועמוס, הפוסט הזה יטפל רק בשלב הראשון בתהליך.
האזנה לאירוע סגירת הטופס
כפי שהזכרנו, ניתן להצמיד קוד לארוע מסויים. לכל פקד (תיבת טקסט, לשונית וכו') קיימים אירועים שונים ומשונים שאפשר להאזין להם. אנו מעוניינים להאזין לארוע הסגירה של פקד הטופס, דהיינו הטופס עצמו.פתחו את טופס רשימת התרומות (טופס המשנה) frmDonationList בתצוגת עיצוב.
שימו לב: כשפותחים טופס בתצוגת עיצוב, כברירת מחדל, אקסס בוחרת את פקד הטופס.
סימן ההיכר לכך הוא הריבוע השחור בפינה השמאלית העליונה, ליד הסרגל של הטופס:
אם בטעות לחצתם ובחרתם פקד אחר, עליכם לבחור בפקד הטופס, על ידי לחיצה על הפינה הנ"ל, או בחירה של פקד הטופס מסרגל המאפיינים:
יפה, כעת בחרו מסרגל המאפיינים את הלשונית "אירוע". שימו לב לרשימה הארוכה של האירועים האפשריים בפקד הטופס:
בחרו את הארוע: "בעת סגירה". בסוף השורה, לחצו על שלושת הנקודות
...ובחלון בחירת הבונה בחרו באפשרות האחרונה: בונה קוד, ולחצו על אישור:
אקסס תפתח עבורנו את עורך ה-VBA:
כברירת מחדל, מראה עורך ה VBA שלכם שונה מהתצלום המצורף. אני שיניתי את ההגדרות לפי טעמי.
בואו נתעלם מכל מה שמסביב ונתמקד בשתי שורות הקוד הבאות:
אקסס יצרה את השורות האלו באופן אוטומטי כשביקשנו להצמיד קוד לארוע סגירת הטופס. שורות אלו מהוות תבנית כללית (ריקה) של תהליך.
בכל תוכנה, מחלקים משימות מורכבות לתהליכים קטנים, כדי להקל על התחזוקה, הקריאות, והיעילות של הקוד.
- השורה הראשונה היא משפט פתיחה של תת-שגרה, כלומר תהליך קטן, או באנגלית SubRoutine. (כאן השימוש בעברית מסרבל יותר מאשר מקל...)
- השורה האחרונה היא משפט סיום של התהליך הזה.
- במקום הריק שבין שתי שורות אלו אנו נכתוב את הקוד שהתהליך אמור לבצע.
ב- VBA יש הבחנה בין שני סוגי תהליכים: Sub-Routine (תת-שגרה) ו- Function (פונקציה), כאשר הסוג ראשון לא מחזיר ערך, והאחרון כן. בשפות מודרניות כיום מקובל להשתמש רק במושג פונקציה עבור שני הסוגים הנ"ל (את הסוג הראשון, מכנים בשם פונקציה שלא מחזירה ערך). לשם פשטות אני לא אדקדק בלשוני ואשתמש גם אני רק במילה פונקציה, אא"כ אהיה מוכרח אחרת.
הלאה,
- המילה Private היא מילה שמורה בשפת VB, שמציינת כי הפונקציה היא פרטית, כלומר: אין אליה גישה מחוץ לתחומי הטופס שלנו (טפסים אחרים, למשל, לא יוכלו לגשת אליה).
- המילה השמורה Sub מציינת כי הפונקציה לא תחזיר ערך.
- צמד המילים עם שני הסוגריים Form_Close() מציינות את שם הפונקציה.
במקרה שלנו, השם Form_Close שמור באקסס כמוסכמה והוא קובע שהקוד יוצמד לאירוע הסגירה של הטופס. - הסוגריים הריקות מציינים כי הפונקציה לא מקבלת פרמטרים מבחוץ (לא נרחיב בעניין בינתיים)
יצירת קוד לריצה באירוע סגירת הטופס
בואו נקפוץ למים, ונוסיף בין השורות הנ"ל את הקוד הבא:
הסבר:
- השורה הראשונה מתחילה במילה השמורה
Dimשמשמעותה הגדרת משתנה חדש. משתנה הוא אובייקט בזיכרון המחשב שיכול להכיל ערך.
כאשר מגדירים משתנה, חובה לתת לו שם. שם המשתנה מופיע מיד אחרי המילהDim.
במקרה שלנו, נתתי לו את השםdonationsToUpdate.
ככלל, נקצה שמות משמעותיים למשתנים, אשר מעידים על תוכנם, ולא נשתמש בקיצורים או ראשי תיבות, או שמות עלומים כגון
x,yוכו'.לאחר שם המשתנה, באה המילה השמורה
Asולאחריה מציינים את טיפוס הנתונים של המשתנה (איזה סוג נתונים הוא יכול להכיל). במקרה שלנו הואLongשפירושו מספר שלם ארוך.המילה השמורה
Asהיא קידומת לשונית שהוכנסה לשפת VB. שפת Basic תוכננה עם כל מיני מילים מיותרות כאלו, כדי להקל על קריאות הקוד.- השורה השנייה מבצעת קריאה לפונקציית המנייה DCount ומכניסה את הערך שהחזירה הפונקציה לתוך המשתנה donationsToUpdate.
DCount היא פונקציה ייחודית לאקסס ואינה חלק משפת VBA. הפונקציה מונה את כל הערכים של השדה בטבלה, ומחזירה את מספרם.
לפונקציה ניתן להעביר 2 או 3 פרמטרים, לפי הסדר הזה:
- שם השדה שאותו אנו מונים:
TargetFamilyID - שם הטבלה שמכילה את השדה:
Donation - תנאי למניית השדה:
StatusID=1
הפרמטר האחרון של התנאי הוא אופציונאלי, ואם נשמיט אותו, הפונקציה תחזיר את מספר כל הרשומות שבהם יש ערך לשדה.
שימו לב שלא הוספנו בתנאי בדיקה אם השדה TargetFamilyID אינו ריק (כלומר התרומה משוייכת למשפחה), מדוע?
כפי שציינתי בעבר, פונקציות מנייה ממילא מתעלמות משדה המכיל Null.
הואיל והשדה TargetFamilyID הוא בעצמו השדה הנמנה, אין צורך להוסיף בפירוש בתנאי כי הוא אינו ריק, הבדיקה ממילא מבוצעת על ידי הפונקציה DCount.- השורה האחרונה והחביבה מדפיסה את ערך המשתנה לחלון הערכים המיידיים
Window Immediate, כמו שנראה מיד. חלון זה משמש אותנו להדפסה של פלט, להרצה ידנית של קוד במקרה הצורך, ועוד.
רוצים לראות מה עשינו?
בואו נחזור לטופס שלנו.
נלחץ על Alt+F11 או על הסמל של אקסס (הסמל העתיק...) בצד שמאל למעלה בחלון עורך ה-VBA:
אפשר גם לסגור לגמרי את העורך כדי לחזור לאקסס.
נסגור את הטופס וכמובן נשמור את השינויים.כעת נפתח את טופס כרטיס התורם.
ומיד לאחר מכן נסגור אותו.
שמתם לב מה קרה?
כלום.רגע, בואו נחזור לעורך ה-VBA (על ידי הקשה שוב על Alt+F11).
שימו לב לחלונית ה Immediate בתחתית העורך: רואים את האפס הקטן?
זהו הפלט שיצרה הפונקציה בסגירת הטופס. הוא כרגע 0 כי אין לנו תרומות לטיפול (עם סטטוס =1 שמשוייכות למשפחה). אז בואו וניצור רשומה כזו.
חיזרו שוב לאקסס והוסיפו תרומה חדשה לכהן אברהם, על סך 72 שקלים, ושייכו אותה למשפחה:
סיגרו את הטופס, וחיזרו לעורך הקוד. שימו לב שעתה נוספה שורה מתחת לאפס ובה מופיע המספר 1:
שמציין כי כעת זוהתה רשומה אחת לטיפול.צעד גדול למתכנת המתחיל.
בפוסט הבא ב"ה נסיים את הפונקציה.
שיעורי בית
- הוסיפו לפונקציה שיצרנו עוד משתנה מסוג Double בשם amountToUpdate, והכניסו לתוכו את סכום התרומות שעומדות לעדכון. השתמשו בפונקצית הסכום DSum (וכן, היא קרובת משפחה של DCount)
- הדפיסו בחלון ה-immediate גם את ערך המשתנה שיצרתם, מתחת למספר הרשומות לעדכון.
- צרו משתנה שלישי בשם userMessage מסוג מחרוזת String, שיכיל בתוכו את המשפט הבא:
"ברשימת התרומות קיימות לעדכון X תרומות על סך כולל של Y"
כאשר במקום X ו-Y יופיעו הערכים הנכונים (השתמשו במשתנים שיצרתם ובשרשור מחרוזות על ידי האופרטור&. הדפיסו גם את המשפט בחלון הפלט.
בהצלחה!
- היא מאפשרת לנו להרחיב את היכולות של ישומי אופיס מעבר לגבולות הקיימים.
-
פרק יט: VBA - חלק ב: כתיבה נכונה של קוד, הערות
ראשית, פתרון שיעורי הבית
כל הכבוד ל @בערל החרוץ!)- הוסיפו לפונקציה שיצרנו עוד משתנה מסוג Double בשם amountToUpdate, והכניסו לתוכו את סכום התרומות שעומדות לעדכון. השתמשו בפונקצית הסכום DSum (וכן, היא קרובת משפחה של DCount).
- הדפיסו בחלון ה-immediate גם את ערך המשתנה שיצרתם, מתחת למספר הרשומות לעדכון.
פתרון:
שימו לב לכמה נקודות:
- כאן צריך להוסיף בפירוש את התנאי
TargetFamilyID Is Not Nullכי הפונקציה Sum סופרת את השדה Amount ולא את TargetFamilyID. - הקפתי בסוגריים כל חלק של התנאי בנפרד, למרות ששפת VBA תפרש נכון את התנאי גם בלי סוגריים מסביב לכל חלק (כי קדימות המילה AND היא נמוכה ביחס לקדימות לאופרטורים
=אוIs). זה פשוט בתור הרגל טוב המשפר את קריאות הקוד. - במקרה שלא נמצאו כלל רשומות המקיימות את התנאי, הפונקציה Sum לא תחזיר את הערך 0 אלא תחזיר NULL. ואז תיווצר לנו שגיאה בזמן הריצה של הקוד, כי המשתנה
amountToUpdateהוא מטיפוסLongואינו יכול לקבל ערך Null. לכן משתמשים בפונקציית ההמרהNz(). הפונקציה בודקת את ערך הפרמטר הראשון (במקרה שלנו = הערך המוחזר מ-DSum), ואם הוא Null, היא מחזירה את ערך הפרמטר השני. (במקרה שלנו = 0).
בפונקציה DCount לא השתמשנו בבדיקה זו, כי בשונה מ-DSum, הפונקציה DCount, תחזיר 0 גם במקרה שלא נמצאו שום רשומות המקיימות את התנאי. אם תחשבו על כך, זה מאוד הגיוני, כי תפקידה לספור רשומות המקיימות את התנאי, ויתכן שלא יהיו כאלו. מה שאין כן DSum מחשבת סכום של שדה מסויים, ואם אין רשומות – הפונקציה אמורה לא להחזיר שום ערך, כי אין שום סכום (0 נחשב סכום).
- צרו משתנה שלישי בשם userMessage מסוג מחרוזת String, שיכיל בתוכו את המשפט הבא:
"ברשימת התרומות קיימות לעדכון X תרומות על סך כולל של Y"
כאשר במקום X ו-Y יופיעו הערכים הנכונים (השתמשו במשתנים שיצרתם ובשרשור מחרוזות על ידי האופרטור&. הדפיסו גם את המשפט בחלון הפלט.
פתרון:
כמה מוסכמויות בכתיבת קוד:
-
עבור שמות פונקציות יש להשתמש ב upper camel case, או בתרגום חופשי: כתיבת גמל (?) או כתיבה גמלונית(?)
, דהיינו להתחיל כל מילה בשם הפונקציה באות גדולה, למשל:
()GetUserName
או
()CleanMyDesk -
עבור שמות משתנים או שמות פרמטרים (לפונקציה) על ידי lower camel case דהיינו להתחיל כל מילה באות גדולה, למעט המילה הראשונה בשם המשתנה, שמתחילה באות קטנה, למשל:
donationsToUpdate
או
MakeMeSomeCoffee(addSugar As Boolean, numberOfCups As Long)
למרות ש-VBA לא תמיד שומרת על מוסכמויות אלו בעצמה (נו נו נו VBA...), כדאי להתרגל בהן כבר מתחילת הדרך. הדבר ישתלם בהמשך, כשנלמד עוד מוסכמויות או נרצה לעבור לשפה אחרת.
וכעת להשלמת פונקציית הארוע:
כעת נותר לנו להשלים את שני הצעדים הבאים:- המערכת תשאל את המשתמש האם הוא רוצה לעדכן את הסטטוס של התרומות המשוייכות. ואם כן,
- המערכת תריץ את שאילתת עדכון התרומות שיצרנו.
נוסיף לפונקצית הארוע את השורות הבאות:
הסבר הקוד:
- השורה הראשונה היא בדיקת תנאי. כדי לבדוק אם דבר מסויים הוא נכון, משתמשים בפקודה
Ifשפירושה: "אם" ולאחריה מופיע התנאי לבדיקה, ואחריו המילהThenשפירושה "אז" (שוב תוספת מיותרת בשפה כדי להקל על קריאות הקוד)
אם התנאי נכון, יבוצע כל בלוק הקוד שמופיע לאחר ה-Thenעד לפקודהEnd If.
על מקטעים או בלוקים:
שימו לב שבדוגמא שלנו יש שתי בדיקות תנאי
Ifמדורגות (Cascaded), ולכן יש שתי פקודותEnd If. במקרה כזה, המהדר (=הקומפיילר, החלק שאחראי על פיענוח הקוד) יפרש את סיום התנאי לפי מדיניות המחסנית (בדומה למחסנית של קליעים, בה הקליע הראשון שנכנס למחסנית הוא האחרון שיוצא, או באנגלית: Last In First Out או בקיצור LIFO) לכן התנאי הראשון מסתיים בEnd If האחרון, והתנאי השני מסתיים ב End If שלפני האחרון וכו'.כדי להקל את קריאות הקוד ולציין בפירוש מה דירוגו של כל קטע קוד, אנו משתמשים בהזחה (Indentation) ימינה בכל פעם שנכנסים למקטע (Scope) עם דירוג חדש, וחוזרים שמאלה בסוף המקטע.
ברוב השפות אין חשיבות להזחה מבחינת הפעולה של הקוד, אך זוהי מיומנות בסיסית חשובה והכרחית בכל כתיבת קוד, שמקילה מאוד על הקריאות ומונעת טעויות רבות, ולרוב סביבת הפיתוח משתדלת לבצע את כל זה עבורנו (זה מאד מפליא ומוזר לראות לפעמים כאלו שלא מקפידים על כך)- השורה השניה היא יצירת ההודעה שברצוננו להציג למשתמש. אנו מחברים את ההודעה שיצרנו קודם, עם התו המיוחד
vbNewLine, שמשמש לירידת שורה (כי אי אפשר להכניס אנטר בתוך מחרוזת בצורה רגילה בקוד), ומוסיפים שאלה למשתמש. את כל העסק הזה אנו מכניסים לתוך המשתנהuserMessage.
אף על פי שהשתמשנו במשתנה userMessage בשני צדדי סימן השווה
=, אין בכך בעיה של הפניה מעגלית, כי הקומפיילר מעריך קודם כל את הצד הימני של הפקודה (לתוך משתנה אנונימי בזיכרון), ואחר כך מבצע השמה של התוצאה לתוך המשתנה userMessage.-
השורה השלישית מציגה תיבת דו-שיח (דיאלוג) למשתמש על ידי קריאה לפונקציה MsgBox, כאשר הפרמטר הראשון הוא ההודעה שברצוננו להציג, והשני הוא דגלי עיצוב של תיבת הדיאלוג. ישנם דגלים רבים ומגוונים, אך השתמשנו כעת בדגל vbYesNo, שמעצב את תיבת הדיאלוג כך שתציג אחרי ההודעה את האפשרויות כן ו-לא.
הפונקציה MsgBox מחזירה את תגובת המשתמש לדיאלוג, ואז מתבצעת השוואה לערך השמור vbYes, בכך אנו מוודאים שהמשתמש בחר באפשרות כן. ואם התוצאה אמת, מתבצע הבלוק הבא – השורה הרביעית. -
השורה הרביעית מבצעת קריאה לפונקצית ההרצה Execute על בסיס הנתונים הנוכחי CurrentDb, ומפעילה את שאילתת העדכון שלנו.
להלן הקוד בשלמותו, אחר הסידור:
Private Sub Form_Close() Dim donationsToUpdate As Long Dim amountToUpdate As Double Dim userMessage As String donationsToUpdate = DCount("TargetFamilyID", "Donation", "StatusID=1") Debug.Print donationsToUpdate amountToUpdate = Nz(DSum("Amount", "Donation", "(TargetFamilyID Is Not Null) AND (StatusID=1"), 0) Debug.Print amountToUpdate userMessage = "ברשימת התרומות קיימות לעדכון " & donationsToUpdate & " תרומות על סך כולל של " & amountToUpdate Debug.Print userMessage If donationsToUpdate > 0 Then userMessage = userMessage & vbNewLine & "האם ברצונך לעדכן את הסטטוס עבורן?" If MsgBox(userMessage, vbYesNo) = vbYes Then CurrentDb.Execute "qryUpdate_SetDonationStatusToTargeted", dbFailOnError End If End If End Subלמרות שאין הדבר בגדר חיוב, מקובל לסדר את כל ההגדרות של המשתנים בתחילת הפונקציה, כדי להפריד בין החלק ההגדרתי לחלק התפעולי.
מוכנים לראות מה עשינו?
פתחו את טופס התורם frmDonor הוסיפו כמה תרומות ושייכו אותן למשפחה:
לאברהם כהן:
וללוי יצחק:
סיגרו את הטופס. אקסס תציג את ההודעה הבאה:
בחרו כן.
פתחו את הטופס שוב ובידקו כי הסטטוס עודכן עבור הרשומות החדשות:
לאברהם כהן:
וללוי יצחק:
מעולה!שינוי כיוון הכתב בתיבת הדיאלוג
מסתמא שמתם לב כי אקסס מציגה את ההודעה בתיבת הדיאלוג משמאל לימין. כדי להפוך את כיוון התצוגה, יש להוסיף לקוד את הדגלים הבאים:If MsgBox(userMessage, vbYesNo + vbMsgBoxRtlReading + vbMsgBoxRight) = vbYes Thenכעת ההודעה תוצג נכון:
הוספת הערות בקוד
פתחו שוב את עורך ה-VBA, והתבוננו בקוד שכתבנו, נשים לב כעת כי יש בו כמה שורות מיותרות, סימנתי אותן להלן:
אין לנו יותר צורך בהדפסת התוצאות בחלון הערכים המיידיים. זה שימש אותנו רק כשלב ביניים כדי לבדוק את התוצאות שמתקבלות מהפונקציות.
נוכל פשוט למחוק אותן, אבל ייתכן ונרצה אותן בעתיד, ולכן במקום למחוק אותן, נבטל את השורות על ידי הפיכתן להערות (Comments).
הערות משמשות לביטול שורות קוד באופן זמני, או להוספת הסברים בתוך הקוד.
בשפה נמוכה (כגון אסמבלר) הסברים בקוד הם הכרחיים, אך בשפה גבוהה (כמעט כל השפות הנפוצות) עדיף למעט בהערות, ובמקום, להתאמץ ככל שניתן שהקוד יהיה כתוב בצורה שתהיה מובנת גם ללא הערות.בשפת VBA, כל הערה מתחילה בתו הגרש:
'.הוסיפו את התו
'לפני שלושת השורות הנ"ל.ניתן גם לעמוד על שורה ולבחור מהתפריט למעלה את לחצן בלוק ההערות:
שימו לב שהעורך משנה את צבע שורת ההערה (ייתכן והצבעים אצלכם שונים):
מעתה, הקומפיילר יתעלם משורות אלו בהרצת הקוד.
עד כאן להיום.
בפרק הבא אי"ה נוסיף עוד רובד למערכת שלנו, והוא הפקת קבלות לתורמים.
על הדרך נכיר את מחולל הדוחות, ונלמד על עוד סוג קשר חשוב בין טבלאות: יחיד ליחיד.
