הקלה על הקריאות של נתוני סיכום באמצעות שאילתת הצלבות

בעת שימוש בתצוגת עיצוב כדי ליצור את שאילתת ההצלבות, באפשרותך להשתמש במספר בלתי מוגבל של מקורות רשומות (טבלאות ושאילתות). עם זאת, כדי לשמור על עיצוב פשוט, צור תחילה שאילתת בחירה שמחזירה את כל הנתונים הרצויים ולאחר מכן השתמש בשאילתה זו כמקור הרשומות היחיד עבור שאילתת ההצלבות. לקבלת מידע נוסף על יצירת שאילתת בחירה, עיין בסעיף למידע נוסף.

כאשר אתה יוצר שאילתת הצלבות בתצוגת עיצוב, אתה משתמש בשורות סך הכל וטבלת הצלבות ברשת העיצוב כדי לציין אילו מערכי השדה יהפכו לכותרות עמודה, אילו מערכי השדה יהפכו לכותרות שורה, ואילו מערכי השדה ישמשו לחישוב סכום, ממוצע, ספירה או חישוב אחר.

1. ההגדרות בשורות אלה קובעות אם השדה הוא כותרת שורה, כותרת עמודה או ערך סיכום.

2. הגדרה זו מציגה את ערכי השדה ככותרות שורה.

3. הגדרה זו מציגה את ערכי השדה ככותרות עמודה.

4. הגדרות אלה מפיקות את ערכי הסיכום.

יצירת השאילתה

1. בכרטיסיה יצירה, בקבוצה שאילתות, לחץ על עיצוב שאילתה.

   

2. בתיבת הדו-שיח הצגת טבלה, לחץ פעמיים על כל אחת מהטבלאות או השאילתות שבה ברצונך להשתמש כמקור רשומות.

   אם אתה משתמש בכמה מקורות רשומות, עליך לוודא שהטבלאות או השאילתות מצורפות בשדות משותפים. לקבלת מידע נוסף על צירוף טבלאות ושאילתות, עיין בסעיף למידע נוסף.

3. סגור את תיבת הדו-שיח הצגת טבלה.

4. בכרטיסיה עיצוב, בקבוצה סוג שאילתה, לחץ על הצלבות.

5. בחלון עיצוב השאילתה, לחץ פעמיים על כל אחד מהשדות שברצונך להשתמש בהם כמקור לכותרות שורה. באפשרותך לבחור עד שלושה שדות עבור כותרות שורה.

6. ברשת העיצוב של השאילתה, בשורה טבלת הצלבות עבור כל שדה של כותרות שורה, בחר כותרת שורה.

   באפשרותך להזין ביטוי בשורה קריטריונים כדי להגביל את התוצאות עבור אותו שדה. כמו כן, באפשרותך להשתמש בשורה מיין כדי לציין סדר מיון עבור שדה.

7. בחלון עיצוב השאילתה, לחץ פעמיים על השדה שבו ברצונך להשתמש כמקור לכותרות עמודה. באפשרותך לבחור רק שדה אחד עבור כותרות עמודה.

8. ברשת העיצוב של השאילתה, בשורה טבלת הצלבות עבור השדה של כותרות העמודה, בחר כותרת עמודה.

   באפשרותך להזין ביטוי בשורה קריטריונים כדי להגביל את התוצאות עבור שדה כותרות העמודה. עם זאת, שימוש בביטוי קריטריונים עם שדה כותרות העמודה אינו מגביל את מספר העמודות המוחזרות על-ידי שאילתת ההצלבות. במקום זאת, הוא מגביל את סוג העמודות שיכולות להכיל נתונים. לדוגמה, נניח שיש לך שדה של כותרות עמודה הכולל שלושה ערכים אפשריים: אדום, ירוק וכחול. אם אתה מחיל את הקריטריון ='כחול' על השדה של כותרות העמודה, טבלת ההצלבות עדיין תציג עמודה עבור אדום ועמודה עבור ירוק, אך רק העמודה עבור כחול תכיל נתונים.

   אם ברצונך להגביל את הערכים שמוצגים ככותרות עמודה, באפשרותך לציין רשימה של ערכים קבועים על-ידי שימוש במאפיין כותרות עמודה של השאילתה. לקבלת מידע נוסף, עיין בסעיף הבא.

9. בחלון העיצוב של השאילתה, לחץ פעמיים על השדה שבו ברצונך להשתמש לחישוב ערכי סיכום. באפשרותך לבחור שדה אחד בלבד לשימוש עבור ערכי סיכום.

10. ברשת העיצוב של השאילתה, בשורה סך הכל עבור השדה של ערכי הסיכום, בחר פונקציית צבירה שתשמש לחישוב הערכים.

11. בשורה טבלת הצלבות עבור השדה של ערכי הסיכום, בחר ערך.

    לא ניתן לציין קריטריונים עבור שדה של ערכי סיכום או למיין לפיו.

12. בכרטיסיה עיצוב, בקבוצה תוצאות, לחץ על הפעל.

ציון ערכים קבועים עבור כותרות עמודה

אם ברצונך לציין ערכים קבועים לשימוש עבור כותרות עמודה, באפשרותך להגדיר את המאפיין כותרות עמודה של השאילתה.

1. פתח את שאילתת ההצלבות בתצוגת עיצוב.

2. אם גליון המאפיינים אינו גלוי, הקש F4 כדי להציגו.

3. בגליון המאפיינים, ממש מעל הכרטיסיה כללי, ודא שסוג בחירה הוא מאפייני שאילתה. אם לא, לחץ על נקודה ריקה בחלק שמעל רשת העיצוב של השאילתה.

4. בגליון המאפיינים, בכרטיסיה כללי, במאפיין כותרות עמודה, הזן רשימה המופרדת באמצעות פסיקים של ערכים שברצונך להשתמש בהם ככותרות עמודה.

   תווים מסוימים (כגון רוב סימני הפיסוק) אינם ניתנים לשימוש בכותרות עמודה. אם אתה משתמש בתווים אלה ברשימת הערכים, Access מחליף כל תו כזה בקו תחתון (_).

תחביר SQL עבור שאילתת הצלבות

שאילתת הצלבות מבוטאת ב- SQL כמשפט TRANSFORM. משפט TRANSFORM כולל את התחביר הבא:

TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]

משפט TRANSFORM כולל את החלקים הבאים:

חלק	תיאור
aggfunction	פונקציית צבירה של SQL הפועלת על הנתונים שנבחרו.
selectstatement	משפט SELECT.
pivotfield	השדה או ביטוי שבו ברצונך להשתמש כדי ליצור כותרות עליונות של עמודה בערכת התוצאות של השאילתה.
value1‏, value2	ערכים קבועים המשמשים ליצירת כותרות עליונות של עמודות.

תצוגת SQL אינה מגבילה את מספר הטבלאות או השאילתות שבהן ניתן להשתמש כמקורות רשומות עבור שאילתת הצלבות. עם זאת, כדי לשמור על עיצוב פשוט, צור תחילה שאילתת בחירה שמחזירה את כל הנתונים שבהם ברצונך להשתמש בשאילתת ההצלבות ולאחר מכן השתמש בשאילתת הבחירה כמקור הרשומות. לקבלת מידע נוסף על יצירת שאילתת בחירה, עיין בסעיף למידע נוסף.

1. בכרטיסיה יצירה, בקבוצה אחר, לחץ על עיצוב שאילתה.

2. סגור את תיבת הדו-שיח הצגת טבלה.

3. בכרטיסיה עיצוב, בקבוצה תצוגות, לחץ על תצוגה ולאחר מכן לחץ על תצוגת SQL.

4. בכרטיסיה אובייקט SQL, הקלד או הדבק את ה- SQL הבא:

   TRANSFORM 
   SELECT 
   FROM 
   GROUP BY 
   PIVOT 
   ;
   

5. בשורה הראשונה, אחרי TRANSFORM, הקלד ביטוי שישמש לחישוב ערכי סיכום; לדוגמה, Sum([Amount])‎.

   אם אתה משתמש ביותר מטבלה אחת או שאילתה אחת כמקור נתונים, כלול את שם הטבלה או השאילתה כחלק מכל שם שדה; לדוגמה, Sum([Expense].[Amount])‎.

6. בשורה השניה, אחרי SELECT, הקלד רשימה של השדות או ביטויי השדה שבהם ברצונך להשתמש עבור כותרות שורה. הפרד בין פריטי הרשימה באמצעות פסיקים; לדוגמה, ‎[Budget].[Dept_ID], [Expense].[Type]‎.

7. בשורה השלישית, אחרי FROM, הקלד את רשימת הטבלאות או השאילתות שבהן אתה משתמש כמקורות רשומות; לדוגמה, Budget, Expense.

8. בשורה הרביעית, אחרי GROUP BY, הקלד את רשימת השדות שבהם השתמשת במשפט SELECT, בשלב 6.

9. בשורה החמישית, אחרי PIVOT, הקלד שם שדה או ביטוי שבו ברצונך להשתמש עבור כותרות עמודה; לדוגמה, PIVOT [Budget].[Year]‎.

לעתים, במקום להשתמש בכל ערכי השדה עבור כותרות שורה או עמודה, ברצונך לקבץ את ערכי השדה בטווחים ולאחר מכן להשתמש בטווחים אלה עבור כותרות שורה או עמודה. לדוגמה, נניח שאתה משתמש בשדה 'גיל' עבור כותרות עמודה. במקום להשתמש בעמודה עבור כל גיל, ייתכן שתעדיף להשתמש בעמודות שמייצגות טווחי גילאים.

באפשרותך להשתמש בפונקציה IIf בביטוי כדי ליצור טווחים לשימוש עבור כותרות שורה או עמודה.

1. פתח את שאילתת ההצלבות בתצוגת עיצוב.

2. ברשת העיצוב של השאילתה, בשורה שדה, לחץ באמצעות לחצן העכבר הימני על עמודה ריקה ולאחר מכן לחץ על שינוי גודל תצוגה בתפריט הקיצור.

3. בתיבה שנה מרחק מתצוגה, הקלד כינוי שדה ולאחר מכן נקודתיים (:).

4. הקלד IIf()‎.

5. בתוך הסוגריים שאחרי IIf, הקלד ביטוי השוואה שמגדיר את הטווח הראשון של ערכי שדה.

   לדוגמה, נניח שאתה יוצר טווחים עבוד שדה מסוג 'גיל' וברצונך שכל טווח יהיה עשרים שנה. ביטוי ההשוואה עבור הטווח הראשון הוא ‎[Age]<21‎.

6. הקלד פסיק אחרי ביטוי ההשוואה ולאחר מכן הקלד שם עבור הטווח, כשהוא מוקף במרכאות. השם שאתה מציין הוא כותרת ההצלבות עבור ערכים שנכללים בטווח.

   לדוגמה, אחרי ‎[Age]<21‎, הקלד פסיק ולאחר מכן הקלד "0-20 years".

7. הקלד פסיק אחרי שם הטווח (מחוץ למרכאות) ולאחר מכן בצע אחת מהפעולות הבאות:

   • כדי ליצור טווח נוסף, הקלד IIf()‎ ולאחר מכן חזור על שלבים 5, 6 ו- 7.

   • עבור הטווח האחרון, פשוט הקלד שם.

     לדוגמה, ביטוי IIf מקונן מלא שמפריד שדה מסוג 'גיל' לטווחים של עשרים שנה, עשוי להיראות כך (מעברי שורה נוספו להקלה על הקריאות):

     IIf([Age]<21,"0-20 years",
      IIf([Age]<41,"21-40 years",
      IIf([Age]<61,"41-60 years",
      IIf([Age]<81,"61-80 years", "80+ years"))))
     

     הערה: כאשר Access מעריך את הביטוי, ההערכה מופסקת ברגע שאחד ממשפטי IIf מוערך כ- True. אין צורך לציין את הקצה הנמוך של כל טווח, מכיוון שכל ערך שנמצא מתחת לקצה התחתון של טווח נתון כבר היה מוערך כ- True.

8. ברשת העיצוב של השאילתה, בשורה סך הכל, בחר קיבוץ לפי.

9. בשורה טבלת הצלבות, ציין אם יש להשתמש בטווחים ככותרות שורה או ככותרות עמודה. זכור שבאפשרותך לציין 1‏-‏3 כותרות שורה וכותרת עמודה אחת.

ייתכן שתרצה ששאילתת ההצלבות תציג בקשה לקלט עם הפעלתה. לדוגמה, נניח שאתה משתמש במספר כותרות שורה, שאחת מהן היא 'מדינה/אזור'. במקום תמיד להציג את הנתונים עבור כל מדינה או אזור, ייתכן שתרצה שהשאילתה תציג בקשה לציון שם ולאחר מכן תציג נתונים בהתאם לקלט של המשתמש.

ניתן להוסיף בקשה לציון פרמטר לכל שדה של כותרת שורה.

1. פתח את שאילתת ההצלבות בתצוגת עיצוב.

2. בשורה קריטריונים עבור השדה של כותרת השורה שעבורו ברצונך לבקש מהמשתמש קלט, הקלד טקסט שאלה המוקף בסוגריים מרובעים. טקסט השאלה יופיע כבקשה עם הפעלת השאילתה.

   לדוגמה, אם אתה מקליד ‎[Which country or region?]‎ בשורה קריטריונים, עם הפעלת השאילתה, תופיע תיבת דו-שיח שמכילה את השאלה "Which country or region?‎", תיבת קלט ולחצן אישור.

   עצה: אם תרצה שהפרמטר יהיה גמיש, שרשר את הביטוי באמצעות תווים כלליים, על-ידי שימוש באופרטור Like. לדוגמה, במקום להשתמש ב- ‎[Which country or region?]‎ כקריטריון, תוכל להשתמש ב- Like [Which country or region?]&"*"‎ כדי שהפרמטר יתאים לטווח גדול יותר של קלט. השימוש ב- Like אינו משנה את המראה של הבקשה לציון פרמטר.

3. בכרטיסיה עיצוב, בקבוצה הצג/הסתר, לחץ על פרמטרים.

4. בתיבת הדו-שיח פרמטרים של שאילתה, בעמודה פרמטרים, הזן את אותה בקשה לציון פרמטר שבה השתמשת בשורה קריטריונים. כלול את הסוגריים המרובעים אך לא את חיבורי התווים הכלליים או את האופרטור Like.

5. בעמודה סוג נתונים, בחר את סוג הנתונים עבור הפרמטר. על סוג הנתונים להיות תואם לסוג הנתונים של שדה כותרת השורה.

אם השדה שבו אתה משתמש לחישוב ערכי סיכום בשאילתת ההצלבות מכיל ערכי Null, כל פונקציות הצבירה שבהן אתה משתמש מתעלמות מערכים אלה. עבור פונקציות צבירה מסוימות, עשויה להיות השפעה על התוצאות. לדוגמה, כדי לחשב ממוצע, אתה מוסיף את כל הערכים ומחלק את התוצאה במספר הערכים. אך אם השדה מכיל ערכי Null, ערכי Null אלה אינם נספרים כחלק ממספר הערכים.

במקרים מסוימים, ייתכן שתעדיף להחליף ערכי Null מסוימים באפסים כדי שהערכים ייספרו במהלך חישובי צבירה. באפשרותך להשתמש בפונקציה Nz כדי להחליף ערכי Null באפסים.

התחביר של Nz

Nz ( variant [, valueifnull ] )‎

תחביר הפונקציה Nz כולל את הארגומנטים הבאים:

ארגומנט	תיאור
variant	נדרש. משתנה של סוג נתונים של סוג נתונים.
valueifnull	אופציונלי (אלא אם נעשה בו שימוש בשאילתה). Variant המספק ערך להחזרה אם הארגומנט variant הוא Null. ארגומנט זה מאפשר לך להחזיר ערך השונה מאפס או ממחרוזת באורך אפס. הערה: בעת שימוש בפונקציה Nz בביטוי בשאילתה מבלי להשתמש בארגומנט valueifnull, התוצאות יהיו מחרוזת באורך אפס בשדות שמכילים את הערכים הריקים.

1. כאשר שאילתה פתוחה בתצוגת עיצוב, ברשת עיצוב השאילתה, לחץ באמצעות לחצן העכבר הימני על השדה ערך.

2. בתפריט הקיצור, לחץ על שינוי גודל תצוגה.

3. בתיבה שנה מרחק מתצוגה, הקף את שם השדה או הביטוי בסוגריים ולאחר מכן הקלד Nz לפני הסוגריים.

4. הקלד ‎, 0‎ ממש בתוך הסוגר הימני.

   לדוגמה, אם השתמשת ב- Nz עם שדה בשם "Hours Lost" כדי להפוך ערכי Null לאפסים, הביטוי המוגמר שלך ייראה כך:

Nz([Hours Lost], 0)

• שמור על פשטות    שאילתות הצלבות יכולות להפוך לקשות לקריאה ככל שמספר שילובי השורות עולה. אל תשתמש ביותר כותרות שורה מהנדרש.

• שקול לבנות את שאילתת ההצלבה בשלבים    אל תגביל את עצמך לשימוש בטבלאות בלבד. לעתים קרובות באפשרותך להתחיל בבניית שאילתת סכומים ולאחר מכן להשתמש בשאילתה זו כמקור רשומות עבור שאילתת הצלבות.

• בחר את שדה כותרות העמודה בקפידה    גליונות נתונים של שאילתות הצלבה נוטים להיות קלים יותר לקריאה כאשר מספר כותרות העמודות נשארים קטנים יחסית. לאחר זיהוי השדות שבהם ברצונך להשתמש ככותרות, שקול להשתמש בשדה שכולל את המספר הנמוך ביותר של ערכים שונים כדי להפיק כותרות עמודה. לדוגמה, אם השאילתה מחשבת ערך לפי גיל ומגדר, שקול להשתמש במגדר עבור כותרות עמודה במקום בגיל, מכיוון שמגדר כולל בדרך כלל פחות ערכים אפשריים בהשוואה לגיל.

• שימוש בשאילתת משנה במשפט WHERE    באפשרותך להשתמש בשאילתת משנה כחלק ממשאב WHERE בשאילתת הצלבות.

לראש הדף