Transcript XP שאילתא
XP שאילתות Queries צריך לדעת איך לשאול כדי לקבל את התשובות 1 XP • • • • • שאילתא מאפשרת להגדיר את התוצאות שנקבל מהשאילתה מאפשרת להגדיר את השדות המסוימים שיכללו בשאילתה מאפשרת לנו גם להגדיר קריטריונים לרשומות שיכללו בתוצאות השאילה כל עמודה בשאילתא מציג שדה שיהיה בשימוש אפשר להפעיל את השאילתא בכל רגע לקבלת תוצאות נכונות בהתאם להגדרות שתי סוגי שאילתות : • – רגילות – לתצוגה ו/או עדכון נתונים – פעילות – שאילתות שמטרתן לבצע פעולות ע"ג הנתונים 2 XP 3 חלון עיצוב שאילתה XP • • • • 4 הוספת שדות לשאילתא משתמשים בטבלת העיצוב לבחור שדות ורשומות הרצויים לנו הוספת שדה ע"י לחיצה כפולה על שם השדה ברשימת השדות או ע"י גרירתו ל"טבלת העיצוב" יש לזכור תוצאות המתקבלות הם מבט זמני של נתונים התוצאות שמתקבלות הם בהתאם לשדות ולקריטריונים שנבחרו XP יצירה הרצה ושמירת שאילתות • מרשימת השדות יש לבחור שדה שברצוננו לקבל בתוצאות (לחיצה כפולה או גרירה) – תוצאות של שאילתה בניגוד למבט בגיליון הטבלא יציגו רק את אותם שדות שנבחרו • ניתן למחוק שדה שלא רוצים בשאילתא ע"י בחירת העמודה ולחיצה על מקש Delete • תוצאות השאילה לפי סדר המפתח של הטבלה אלא אם נבחרה אופציה מיון • שמירת הגדרות השאילתא ע"י לחיצת Saveבסרגל הכלים 5 XP 6 בחירה ומיון השדות XP 7 דוגמא לתוצאות שאילתא XP שימוש בשאילתא לעדכון נתונים • אפשר לעדכן נתונים בטבלה דרך גיליון תוצאות של שאילתא • יש לזכור שהשאילתא מספקת מבט זמני של הנתונים בטבלה אבל עדכון דרך הגיליון יבצע שינוי ישירות מול הטבלה ! • ניתן לעדכן רק אותם שדות שנבחרו בשאילתא 8 XP מיון נתנוים דרך שאילתא • אפשר לשלוט במיון תוצאות השאילתא ע"י שינוי מפתח המיון • בחלון עיצוב השאילתא בוחרים את השדה שלפיו רוצים מיון .יש גם לבחור סדר עולה ()Ascending או סדר יורד)Descending(. • אפשר לבחור יותר משדה מיון אחד לצורך קבלת מיון בתוך קבוצות 9 XP 10 צורת המיון בהתאם לסוג נתום XP 11 מיון של שדה או מספר שדות XP • • • • 12 קריטריון מדויק Exact Match לצורך קבלת רשומות שעונות בצורה מדוייקת לקריטריון אפשר להגדיר "קריטריון מדויק" לצורך שימוש בקריטריון כזה יש לבחור את שורת ה Criteriaבטבלת עיצוב השאילתא בהפעלת השאילתא יתקבלו רק אותן רשומות שממלאות את תנאי הקריטריון ויוצגו משמעותו של קריטריון מדויק :רק כאשר ערך השדה ברשומה זהה לקריטריון אזי פרטי הרשומה יוצגו בתוצאות XP 13 קריטיריונים Criteria XP 14 שימוש בקריטרון לקבלת טווח ערכים XP • • • • • שימוש ב מספר קריטריונים במקביל כאשר יש צורך במספר קריטריונים אפשר לצרף אותם ע"י שימוש בפעולות לוגיות - Andמחייב ששני התנאים ימולאו - ORמספיק שאחד התנאים יתקיים כאשר שני תנאים מופעים באותה שורה זה תנאי לוגי מסוג AND כאשר התנאים מופעים בשורות שונות זה תנאי לוגי מסוג OR 15 XP 16 תרשים זרימה של פעולות And / OR XP 17 יצירת תנאי לוגי מסוג And XP תרגילים יש להשתמש בבסיס נתונים בשם Targilim.mdb )1הכן שאילתא לקבלת רשימת לקוחות מעיר .Madridלרשימה יש צורך ב: שם לקוח ,איש קשר ,תפקיד איש הקשר ,ומספר טלפון. )2הכן שאילתא לקבלת רשימת מוצרים שהם מסוג Beveragesאו Dairy Products לרשימה יש צורך בכל פרטי המוצר )3הכן שאילתא לקבלת הזמנות מחודש Nov. 1995 18 XP יצירת שדה מחושב בשאילתא • יש אפשרות לבצע חישוב כחלק מתוצאות השאילתא • במקום שדה מטבלה אפשר להזין ביטוי לחישוב כגון: – שדה * 1שדה - 2ערך השדה כפול ערך שדה – שדה - 1.15 * 1ערך שדה כפול ערך קבוע • יש לוודא את סדר הפעולות החשבונאיות • ישנם 3דרכים להזנת הביטוי: – ע"י כתיבתו ישירות בחלון העיצוב – ע"י חלון ההגדלה – ע"י בונה הביטויים של Access 19 XP חלון בונה הביטוים The Expression Builder dialog box 20 XP הוספת קרטריון משתנה • ברצוננו ליצור שאילתא כללית כך שבכל פעם שתופעל ניתן להזין את ערך הקריטריון • לדוגמא ע"י הוספת [עיר] כקריטריון לשדה Cityמכיוון שאין שדה [עיר] בטבלאות השאילתא Accessיבקש ערך בהפעלה. 21 XP תרגילים להשתמש בבסיס נתונים בשם Targilim.mdb )4הכן שאילתא המציגה מחירים נוכחיים ומחיר מחושב עם תוספת של 15% )5הכן שאילתא לקבלת רשימת מוצרים במלאי מתחת לכמות מינימום לפני הזמנה (שים לב ללא מוצרים שהם )Discontinuedלרשימה יש צורך בפרטי הספק (שם וטלפון) )6שנה שאילתא מתרגיל 4כך שבהפעלתו נוכל להזין את ערך אחוז הגידול במחיר 22 XP • שאילתות מסוג סיכום מטרת השאילתא לקבץ נתונים ממספר רשומות לתוך רשומה מסכמת. – • הפיכת השאילתא ע"י שימוש בסימן Sigmaבסרגל הכלים – 23 דוגמא :סה"כ מכירות לשנה מתקבלת בחלון העיצוב שורה נוספת לכל שדה שם השורה Total: XP פונקציות ואופציות אפשריות לשאילתות מסוג סיכום שדה לפיו נבנת רשומה חדשה ספירת מספר רשומות לאותה קבוצה Group By Count סיכום ערך השדה בקבוצה Sum ערך נמוך ביותר שמופיע בקבוצה ( NULLלא נחשב) ערך מקסימלי שמופיע בקבוצה ( NULLלא נחשב) Min 24 Max XP פונקציות ואופציות אפשריים לשאילתות מסוג סיכום ראשון בקבוצה ( NULLלא נחשב) אחרון בקבוצה ( NULLלא נחשב) ממוצע של ערך הקבוצה ( NULLלא נחשב) סטיית תקן ( NULLלא נחשב) שונות בקבוצה ( NULLלא נחשב) 25 First Last Avg StDev Var XP פונקציות ואופציות אפשריים לשאילתות מסוג סיכום לבנות חישוב מיוחד תנאי להגביל רשומות מבלי לבצע "הצג עמודה" 26 Expression Where לפעולות תאריכיםVBA פונקציות XP מחזיר חלק מהתאריך Datepart(dcode*,datefield) מחשב תאריך חדש DateAdd(dcode*,value2add,datefield) מחשב הפרש בין התאריכים DateDiff(dcode*,datefield1,datefield2) * קוד בהתאם לטבלה בהמשך 27 לפעולות תאריכיםVBA פונקציות “yyyy” “q” “m” Year Quarter Month “y” Day of year “d” “w” Day Weekday “ww” “h” “n” Week Hour Minute “s” Second XP 28 XP קשר בין טבלאות ברמת השאילתא אין קשר בין הטבלאות טבלה "ב" 29 טבלה "א" תוצאות: טבלה המכילה כפל הטבלאות צירוף של כל רשומה מטבלה "א" עם כל רשומה מטבלה "ב" XP קשר בין טבלאות ברמת השאילתא Inner Join טבלה "ב" טבלה "א" שדה1 שדה1 תוצאות: רשומה כאן רק כאשר בשתי הטבלאות קיים ערך זהה 30 XP קשר בין טבלאות ברמת השאילתא Left Outer Join טבלה "ב" טבלה "א" שדה1 שדה1 תוצאות: כל הרשומות מטבלה "א" ופרטים מ "ב" כאשר יש ערך זהה 31 XP קשר בין טבלאות ברמת השאילתא Left Outer Join טבלה "ב" טבלה "א" שדה1 שדה1 תוצאות: כל הרשומות מטבלה "ב" ופרטים מ "א" כאשר יש ערך זהה 32 XP תרגילים יש להשתמש בבסיס נתונים בשם Targilim.mdb )7הכן שאילתא המציגה סה"כ הזמנות לפי איש מכירות )8הכן שאילתא לחשב סה"כ תשלום עבור כל הזמנה (יש לקחת בחשבון גם הנחה). )9הכן שאילתא לחישוב סה"כ תשלומים לפי מדינת המשלוח )10הכן שאילתא לחישוב סה"כ תשלומים לפי חודש המשלוח 33 XP שאילתא מסוג הצלבה CrossTab Query • מאפשרת לקבל סיכום המשלב בין שני גורמים בו זמנית – לדוגמא :לסכם בעמודת חודש לפי שורת איש המכירות • חייבים לבחור שני שדות להגדרת הקבוצות תרגיל • יש להפוך את השאילתא מתרגיל 10ל CrossTab 34 XP שאילתות פעילות Action Queries • תוצאות לשאילתות מסוג זה מתבצעות ללא קבלתם במסך או בדו"ח • שאילתא יצירת טבלה – Make Table Query – התוצאות מייצרות טבלה חדשה (הטבלה החדשה מוחקת טבלה קודמת אם קיימת) • שאילתא הוספה – Add Query – תוצאות נכנסות כרשומות חדשות בטבלה קיימת • שאילתא עדכון – Update Query – מבצע שינוי נתונים ישירות – דוגמא :העלאת מחירי המוצרים ב 15% • שאילתא מחיקה – Delete Query – מוחקת את הרשומות מהטבלה 35 XP • • • • 36 תרגילים בסיס נתונים targilim.mdb : הכן שאילתא ליצירת טבלה חדשה המכילה כל הלקוחות מהעיר London הוסף לטבלת לקוחות מ Londonאת הלקוחות מ Nantes שנה שאילתא מתרגיל קודם כך שניתן להזין עיר בעת הפעלה