מאמר זה מסביר כיצד להשתמש בשאילתות ערכים עליונים ובשאילתות סיכומים כדי למצוא את התאריכים העדכניים ביותר או המוקדמים ביותר בקבוצת רשומות. אפשרות זו יכולה לסייע לך לענות על מגוון של שאלות עסקיות, כגון כאשר הלקוח הציב לאחרונה הזמנה, או אילו חמישה רבעים הפכו לטובים ביותר עבור מכירות, לפי עיר.
במאמר זה
מבט כולל
באפשרותך לדרג נתונים ולסקור את הפריטים המדורגים ביותר באמצעות שאילתת ערכים עליונים. שאילתת ערך עליון היא שאילתת בחירה המחזירה מספר או אחוז מסוים של ערכים מראש התוצאות, לדוגמה, חמשת הדפים הפופולריים ביותר באתר אינטרנט. באפשרותך להשתמש בשאילתת ערכים עליונים כנגד כל סוג של ערכים – הם אינם חייבים להיות מספרים.
אם ברצונך לקבץ או לסכם את הנתונים לפני שאתה מדרג אותו, אינך צריך להשתמש בשאילתת ערכים עליונים. לדוגמה, נניח שעליך למצוא את מספרי המכירות עבור תאריך נתון עבור כל עיר שבה פועלת החברה שלך. במקרה זה, הערים הופכות לקטגוריות (עליך למצוא את הנתונים בכל עיר), כך שאתה משתמש בשאילתת סכומים.
בעת שימוש בשאילתת ערכים עליונים לאיתור רשומות המכילות את התאריכים העדכניים ביותר או המוקדמים ביותר בטבלה או בקבוצת רשומות, באפשרותך לענות על מגוון שאלות עסקיות, כגון הפעולות הבאות:
-
מי מבצע את המכירות העדכניות ביותר לאחרונה?
-
מתי לקוח מבצע את ההזמנה בפעם האחרונה?
-
מתי שלושת ימי ההולדת הבאים בצוות?
כדי ליצור שאילתת ערך עליון, התחל על-ידי יצירת שאילתת בחירה. לאחר מכן, מיין את הנתונים בהתאם לשאלה שלך-אם אתה מחפש את החלק העליון או התחתון. אם עליך לקבץ או לסכם את הנתונים, הפוך את שאילתת הבחירה לשאילתת סכומים. לאחר מכן, באפשרותך להשתמש בפונקציית צבירה, כגון Max או Min כדי להחזיר את הערך הגבוה ביותר או הנמוך ביותר, או הראשון או האחרון כדי להחזיר את התאריך המוקדם ביותר או המאוחר ביותר.
מאמר זה מניח שערכי התאריכים שבהם אתה משתמש מכילים את סוג הנתונים ' תאריך/שעה '. אם ערכי התאריך שלך נמצאים בשדה טקסט,.
שקול להשתמש במסנן במקום בשאילתת ערכים עליונים
מסנן עדיף בדרך כלל אם יש לך תאריך ספציפי. כדי לקבוע אם עליך ליצור שאילתת ערכים עליונים או להחיל מסנן, שקול את הפרטים הבאים:
-
אם ברצונך להחזיר את כל הרשומות שבהן התאריך מתאימים, הוא מופיע לפני התאריך הספציפי או מאוחר יותר, השתמש במסנן. לדוגמה, כדי לראות את התאריכים עבור מכירות בין אפריל ליולי, עליך להחיל מסנן.
-
אם ברצונך להחזיר כמות שצוינה של רשומות הכוללות את התאריכים העדכניים ביותר או העדכניים ביותר בשדה, ואינך יודע את ערכי התאריכים המדויקים, או שהם אינם מתעניינים, עליך ליצור שאילתת ערכים עליונים. לדוגמה, כדי לראות את חמשת מגורי המכירות הטובים ביותר, השתמש בשאילתת ערכים עליונים.
לקבלת מידע נוסף אודות יצירת מסננים ושימוש בהם, עיין במאמר החלת מסנן להצגת רשומות נבחרות במסד נתונים של Access.
הכנת נתונים לדוגמה למעקב יחד עם הדוגמאות
השלבים במאמר זה משתמשים בנתונים בטבלאות לדוגמה הבאות.
הטבלה Employees
שם_משפחה |
שם פרטי |
כתובת |
עיר |
CountryOrR egion |
תאריך לידה |
תאריך ההעסקה |
Barnhill |
Josh |
רח' שלומציון 1 |
תל אביב |
USA |
05-פבר-1968 |
10-Jun-1994 |
פיינברג |
יניב |
רח' האורגים 3 |
ירושלים |
USA |
22-May-1957 |
22-Nov-1996 |
כץ |
פלונסקי |
Ave 75 של 3122. S.W. |
חיפה |
USA |
11-נוב-1960 |
11-Mar-2000 |
ייגל |
ז'אן פיליפ |
דרך קונטוסו 123 |
גבעת עדה |
UK |
22-Mar-1964 |
22-Jun-1998 |
מחיר |
ג'וליאן |
האצל 3 |
קרית שמונה |
מקסיקו |
05-Jun-1972 |
05-Jan-2002 |
יוז |
כריסטין |
3122 75 של St. S. |
Seattle |
USA |
23-Jan-1970 |
23-Apr-1999 |
Riley |
Steve |
רח' לוי אשכול 19 |
אור יהודה |
USA |
14-Apr-1964 |
14-Oct-2004 |
סרור |
דנה |
2 יצמן 10 חוטם |
Portland |
USA |
29-Oct-1959 |
29-Mar-1997 |
הטבלה EventType
TypeID |
סוג אירוע |
1 |
הפעלת מוצר |
2 |
הפונקציה Corporate |
3 |
הפונקציה Private |
4 |
גיוס כספים |
5 |
תערוכה מסחרית |
6 |
רצאה |
7 |
קונצרט |
8 |
תערוכה |
9 |
יריד רחוב |
הטבלה 'צרכנים'
CustomerID |
חברה |
איש קשר |
1 |
קונטוסו בע”מ גרפיקה |
יונתן האס |
2 |
צעצועי טיילספין |
אלן אדמס |
3 |
Fabrikam |
קרול פיליפס |
4 |
' צעצועי קסם ' |
לוסיו Iallo |
5 |
א. דנון |
Mandar Samant |
6 |
עבודות הרפתקאות |
בריאן בורק |
7 |
מכון עיצוב |
ג'אקה |
8 |
בית הספר לאמנות משובחת |
מילנה Duomanova |
הטבלה Events
EventID |
סוג אירוע |
לקוח |
תאריך אירוע |
מחיר |
1 |
הפעלת מוצר |
קונטוסו בע”מ |
4/14/2011 |
$10,000 |
2 |
הפונקציה Corporate |
צעצועי טיילספין |
4/21/2011 |
₪8,000 |
3 |
תערוכה מסחרית |
צעצועי טיילספין |
01/05/11 |
$25,000 |
4 |
תערוכה |
המכון לעיצוב גרפי |
5/13/2011 |
₪4,500 |
5 |
תערוכה מסחרית |
קונטוסו בע”מ |
5/14/2011 |
$55,000 |
6 |
קונצרט |
בית הספר לאמנות משובחת |
5/23/2011 |
₪12,000 |
7 |
הפעלת מוצר |
א. דנון |
6/1/2011 |
₪15,000 |
8 |
הפעלת מוצר |
' צעצועי קסם ' |
6/18/2011 |
21,000 ש"ח |
9 |
גיוס כספים |
עבודות הרפתקאות |
6/22/2011 |
$1,300 |
10 |
רצאה |
המכון לעיצוב גרפי |
6/25/2011 |
$2,450 |
11 |
רצאה |
קונטוסו בע”מ |
04/07/11 |
$3,800 |
12 |
יריד רחוב |
המכון לעיצוב גרפי |
04/07/11 |
$5,500 |
הערה: השלבים בסעיף זה מניחים שהטבלאות לקוחות וסוגי אירועים נמצאות בצד ה"יחיד" של קשרי גומלין של יחיד לרבים עם הטבלה Events. במקרה זה, הטבלה Events משתפת את השדות CustomerID ו-TypeID. שאילתות הסכומים המתוארות בסעיפים הבאים לא יפעלו ללא קשרי גומלין אלה.
הדבקת הנתונים לדוגמה לגליונות עבודה של Excel
-
הפעל את Excel. חוברת עבודה ריקה נפתחת.
-
הקש SHIFT + F11 כדי להוסיף גליון עבודה (תזדקק לארבעה).
-
העתק את הנתונים מכל טבלה לדוגמה לגליון עבודה ריק. כלול את כותרות העמודות (השורה הראשונה).
יצירת טבלאות של מסד נתונים מתוך גליונות העבודה
-
בחר את הנתונים מגליון העבודה הראשון, כולל כותרות העמודות.
-
לחץ באמצעות לחצן העכבר הימני על חלונית הניווט ולאחר מכן לחץ על הדבק.
-
לחץ על כן כדי לאשר שהשורה הראשונה מכילה כותרות עמודות.
-
חזור על שלבים 1-3 עבור כל אחד מגליונות העבודה הנותרים.
חיפוש התאריך המאוחר ביותר או הפחות
השלבים בסעיף זה משתמשים בנתונים לדוגמה כדי להמחיש את תהליך היצירה של שאילתת ערכים עליונים.
יצירת שאילתה בסיסית של ערכים עליונים
-
בכרטיסיה יצירה, בקבוצה שאילתות, לחץ על עיצוב שאילתה.
-
לחץ פעמיים על הטבלה Employees ולאחר מכן לחץ על סגור.
אם אתה משתמש בנתונים לדוגמה, הוסף את הטבלה Employees לשאילתה.
-
הוסף את השדות שבהם ברצונך להשתמש בשאילתה לרשת העיצוב. באפשרותך ללחוץ פעמיים על כל שדה, או לגרור ולשחרר כל שדה בתא ריק בשורת השדות .
אם אתה משתמש בטבלה לדוגמה, הוסף את השדות שם פרטי, שם משפחה ותאריך הלידה.
-
בשדה המכיל את הערכים העליונים או התחתונים (שדה תאריך הלידה, אם אתה משתמש בטבלה לדוגמה), לחץ על השורה מיין ובחר בסדר עולה או יורד.
סדר המיון בסדר יורד הפונקציה מחזירה את התאריך האחרון וסדר המיון בסדר עולה מחזירה את התאריך המוקדם ביותר.
חשוב: עליך להגדיר ערך בשורה מיון רק עבור שדות המכילים את התאריכים שלך. אם תציין סדר מיון עבור שדה אחר, השאילתה לא תחזיר את התוצאות הרצויות.
-
בכרטיסיה עיצוב , בקבוצה כלים , לחץ על החץ למטה לצד All (רשימת הערכים העליונים ) והזן את מספר הרשומות שברצונך לראות, או בחר אפשרות מהרשימה.
-
לחץ על הפעלת כדי להפעיל את השאילתה ולהציג את התוצאות בתצוגת גליון נתונים.
-
שמור את השאילתה כNextBirthDays.
באפשרותך לראות ששאילתה זו של ערכים עליונים יכולה לענות על שאלות בסיסיות, כגון מי האדם הישן ביותר או הצעיר ביותר בחברה. השלבים הבאים מסבירים כיצד להשתמש בביטויים ובקריטריונים אחרים כדי להוסיף מתח וגמישות לשאילתה. הקריטריונים המוצגים בשלב הבא מחזירים את שלושת ימי ההולדת הבאים של העובדים.
הוספת קריטריונים לשאילתה
שלבים אלה משתמשים בשאילתה שנוצרה בהליך הקודם. באפשרותך לעקוב יחד עם שאילתת ערכים עליונים שונים כל עוד היא מכילה נתוני תאריך/שעה בפועל, ולא ערכי טקסט.
עצה: אם ברצונך להבין טוב יותר כיצד השאילתה פועלת, עבור בין תצוגת עיצוב לתצוגת גליון נתונים בכל שלב. אם ברצונך לראות את קוד השאילתה בפועל, עבור לתצוגת SQL. כדי לעבור בין תצוגות, לחץ באמצעות לחצן העכבר הימני על הכרטיסיה בחלק העליון של השאילתה ולאחר מכן לחץ על התצוגה הרצויה.
-
בחלונית הניווט, לחץ באמצעות לחצן העכבר הימני על השאילתה NextBirthDays ולאחר מכן לחץ על תצוגת עיצוב.
-
ברשת העיצוב של השאילתה, בעמודה משמאל לתאריך הלידה, הזן את הפרטים הבאים:
MonthBorn: DatePart ("m", [BirthDate]).
ביטוי זה מחלץ את החודש מ-BirthDate באמצעות הפונקציה DatePart . -
בעמודה הבאה של רשת עיצוב השאילתה, הזן את הפרטים הבאים:
DayOfMonthBorn: DatePart ("d", [birthdate])
ביטוי זה מחלץ את היום בחודש מתאריך הלידה באמצעות הפונקציה DatePart . -
נקה את תיבות הסימון בשורה ההצגה עבור כל אחד משני הביטויים שהזנת זה עתה.
-
לחץ על השורה מיין עבור כל ביטוי ולאחר מכן בחר סדר עולה.
-
בשורה קריטריונים של העמודה תאריך הלידה , הקלד את הביטוי הבא:
month ([תאריך לידה]) > חודש (date ()) או חודש ([תאריך לידה]) = MONTH (Date ()) ו-day ([תאריך לידה]) >יום (date ())
ביטוי זה מבצע את הפעולות הבאות:-
Month ( [תאריך לידה]) > Month (date ()) מציין שתאריך הלידה של כל עובד חל בחודש עתידי.
-
החודש ( [תאריך לידה]) = Month (date ()) ו-Day ([תאריך לידה]) >day (date ()) מציין שאם תאריך הלידה מתרחש בחודש הנוכחי, יום ההולדת מתקיים או אחרי היום הנוכחי.
בקצרה, ביטוי זה אינו מכלול את כל הרשומות שבהן יום ההולדת מתרחש בין 1 בינואר לבין התאריך הנוכחי.
עצה: לקבלת דוגמאות נוספות של ביטויים של קריטריוני שאילתה, עיין במאמר דוגמאות לקריטריוני שאילתה.
-
-
בכרטיסיה עיצוב , בקבוצה הגדרת שאילתה , הקלד 3 בתיבה החזרה .
-
בכרטיסיה עיצוב, בקבוצה תוצאות, לחץ על הפעל .
הערה: בשאילתה משלך באמצעות נתונים משלך, ייתכן שלעתים תראה רשומות נוספות מאלה שציינת. אם הנתונים שלך מכילים רשומות מרובות החולקות ערך שבין הערכים העליונים, השאילתה תחזיר את כל הרשומות האלה גם אם היא מתכוונת להחזיר רשומות נוספות מכפי שרצית.
איתור התאריכים האחרונים ביותר או הפחות עבור קבוצות של רשומות
אתה משתמש בשאילתת סיכומים כדי למצוא את התאריכים המוקדמים ביותר או האחרונים עבור רשומות הנמצאות בקבוצות, כגון אירועים המקובצים לפי עיר. שאילתת סכומים היא שאילתת בחירה המשתמשת בפונקציות צבירה (כגון Group By, Min, Max, Count, Firstו- Last) כדי לחשב ערכים עבור כל שדה פלט.
כלול את השדה שבו ברצונך להשתמש עבור קטגוריות – כדי לקבץ לפי – והשדה עם ערכים שברצונך לסכם. אם אתה כולל שדות פלט אחרים-ציין, את שמות הלקוחות כאשר אתה מקבץ לפי סוג אירוע – השאילתה תשתמש גם בשדות אלה כדי ליצור קבוצות, לשנות את התוצאות כך שלא יענו לשאלה המקורית. כדי להוסיף תווית לשורות באמצעות שדות אחרים, עליך ליצור שאילתה נוספת המשתמשת בשאילתת הסכומים כמקור ולהוסיף את השדות הנוספים לשאילתה זו.
עצה: בניית שאילתות בשלבים היא אסטרטגיה יעילה מאוד למענה על שאלות מתקדמות יותר. אם אתה נתקל בבעיות בקבלת שאילתה מסובכת לעבודה, שקול אם באפשרותך להפריד אותה לסידרה של שאילתות פשוטות יותר.
יצירת שאילתת סכומים
הליך זה משתמש בטבלה לדוגמה אירועיםובטבלה לדוגמה של EventType כדי לענות על שאלה זו:
מתי היה האירוע האחרון של כל סוג אירוע, כולל קונצרטים?
-
בכרטיסיה יצירה, בקבוצה שאילתות, לחץ על עיצוב שאילתה.
-
לחץ פעמיים על הטבלאות Events ו-EventType.
כל טבלה מופיעה בחלק העליון של מעצב השאילתות. -
לחץ פעמיים על השדה EventType של הטבלה EventType והשדה EventDate מהטבלה Events כדי להוסיף את השדות לרשת העיצוב של השאילתה.
-
ברשת העיצוב של השאילתה, בשורה קריטריונים של השדה EventType , הזן<>קונצרט.
עצה: לקבלת דוגמאות נוספות של ביטויי קריטריונים, עיין במאמר דוגמאות לקריטריוני שאילתה.
-
בכרטיסיה עיצוב, בקבוצה הצגה/הסתרה, לחץ על סכומים.
-
ברשת העיצוב של השאילתה, לחץ על שורת הסכום של השדה EventDate ולאחר מכן לחץ על Max.
-
בכרטיסיה עיצוב, בקבוצה תוצאות, לחץ על תצוגה ולאחר מכן לחץ על תצוגת SQL.
-
בחלון SQL, בסוף משפט SELECT, מיד לאחר מילת המפתח, החלף את MaxOfEventDate באמצעות MostRecent.
-
שמור את השאילתה כMostRecentEventByType.
יצירת שאילתה שניה להוספת נתונים נוספים
הליך זה משתמש בשאילתת MostRecentEventByType מההליך הקודם כדי לענות על שאלה זו:
מי היה הלקוח באירוע האחרון של כל סוג אירוע?
-
בכרטיסיה יצירה, בקבוצה שאילתות, לחץ על עיצוב שאילתה.
-
בכרטיסיה שאילתות , לחץ פעמיים על השאילתה MostRecentEventByType.
-
בכרטיסיה טבלאות , לחץ פעמיים על הטבלה events ועל הטבלה customers.
-
במעצב השאילתות, לחץ פעמיים על השדות הבאים:
-
בטבלה Events, לחץ פעמיים על EventType.
-
בשאילתת MostRecentEventByType, לחץ פעמיים על MostRecent.
-
בטבלה Customers, לחץ פעמיים על חברה.
-
-
ברשת העיצוב של השאילתה, בשורה מיין של העמודה EventType , בחר סדר עולה.
-
בכרטיסיה עיצוב, בקבוצה תוצאות, לחץ על הפעל.