שימוש בשאילתת איחוד כדי לשלב שאילתות מרובות לתוצאה בודדת

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

SELECT Customers.Company, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT Employees.Company, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

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

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT "Supplier" As Employment, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT "In-house" As Employment, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

כך תיראה התוצאה בתצוגת גליון נתונים. Access מציג חמש רשומות לדוגמה אלה:

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

SELECT "Customer" As Employment, [Last Name], [First Name]
FROM Customers

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers

UNION

SELECT "In-house", [Last Name], [First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

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

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers
WHERE [State/Province] = "UT"

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers
WHERE [Job Title] = "Sales Manager"

UNION

SELECT "In-house", Employees.[Last Name], Employees.[First Name]
FROM Employees
WHERE City = "Seattle"

ORDER BY [Last Name], [First Name];

עבור לתצוגת גליון נתונים כדי לראות תוצאות דומות לזו:

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

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

נניח שברצונך לפצל את השדה Quantity לשניים – Buy ו- Sell. נניח גם שברצונך להגדיר ערך קבוע של אפס עבור שדה ללא ערך. כך ה- SQL ייראה עבור שאילתת איחוד זו:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], 0 As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, 0 As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC; 

אם תעבור לתצוגת גליון נתונים, תראה את עשר הרשומות האחרונות המוצגות כעת באופן הבא:

בהמשך לדוגמה זו, כיצד עליך לפעול אם ברצונך שהשדות הכוללים אפס יהיו ריקים? באפשרותך לשנות את ה- SQL כך שלא יציג דבר במקום להציג אפס על-ידי הוספת מילת המפתח Null באופן הבא:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

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

הסיבה לכך היא ש- Access קובע את סוגי הנתונים של השדות לפי השאילתה הראשונה. בדוגמה זו, Null אינו מספר.

מה קורה אם אתה מנסה להוסיף מחרוזת ריקה עבור הערך הריק של השדות? ה- SQL במסגרת ניסיון זה עשוי להיראות כך:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], "" As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, "" As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

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

כיצד תוכל לפתור חידה זו?

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

IIf(False, 0, Null)

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

כך תוכל להשתמש בביטוי זה בדוגמה השימושית שלנו:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], IIf(False, 0, Null) As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

שים לב שהדבר אינו נחוץ כדי לשנות את השאילתה השניה.

אם תעבור לתצוגת גליון נתונים, תראה תוצאה רצויה:

שיטה חלופית להשגת תוצאה זהה היא להוסיף לפני השאילתות בשאילתת האיחוד שאילתה נוספת:

SELECT 
    0 As [Product ID], Date() As [Order Date], 
    "" As [Company Name], "" As [Transaction], 
    0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

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

WHERE False

זהו טריק מאחר שמדובר תמיד ב- False והשאילתה אינה מחזירה דבר. השילוב של משפט זה עם ה- SQL הקיים ייצור את המשפט השלם שלהלן:

SELECT 
    0 As [Product ID], Date() As [Order Date], 
    "" As [Company Name], "" As [Transaction], 
    0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

UNION

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

מקרה מיוחד של שאילתת איחוד הוא שילוב של ערכת רשומות עם רשומה אחת המכילה את הסכום של שדה אחד או יותר.

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

1. צור שאילתה פשוטה חדשה כדי להציג את הרכישה של בירות (Product ID=34 במסד הנתונים של Northwind) באמצעות תחביר SQL הבא:

   SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   ORDER BY [Purchase Order Details].[Date Received];

2. עבור לתצוגת גליון נתונים. אתה אמור לראות ארבע רכישות:

   Date Received	Quantity
   22/01/2006	100
   22/01/2006	60
   04/04/2006	50
   05/04/2006	300

3. כדי לקבל את הסכום הכולל, צור שאילתה מצטברת פשוטה באמצעות ה- SQL הבא:

   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))

4. עבור לתצוגת גליון נתונים. אתה אמור לראות רשומה אחת בלבד:

   MaxOfDate Received	SumOfQuantity
   05/04/2006	510

5. שלב שתי שאילתות אלה בשאילתת איחוד כדי לצרף את הרשומה עם הכמות הכוללת לרשומות הרכישה:

   SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   UNION
   
   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   ORDER BY [Purchase Order Details].[Date Received];

6. עבור לתצוגת גליון נתונים. אתה אמור לראות את ארבע הרכישות, כשאחרי הסכום של כל אחת מהן מוצגת רשומה המסכמת את הכמות:

   Date Received	Quantity
   22/01/2006	60
   22/01/2006	100
   04/04/2006	50
   05/04/2006	300
   05/04/2006	510

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

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

בסעיף הקודם, הראינו לך כיצד ליצור סכום כולל בשאילתת איחוד. שנה את ה- SQL של שאילתת איחוד זו כך שיכלול את Product ID= 48:

SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION

SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Purchase Order Details].[Date Received];

עבור לתצוגת גליון נתונים. אתה אמור לראות תוצאה מטעה במידת מה:

כמובן, רשומה אחת אינה מחזירה כמות כפולה בסכום הכולל.

תוצאה זו מוצגת משום שבאחד הימים אותה כמות של שוקולדים נמכרה פעמיים – כפי שהדבר תועד בטבלה Purchase Order Details. להלן תוצאה של שאילתת בחירה פשוטה המציגה את שתי הרשומות במסד הנתונים לדוגמה של Northwind:

בשאילתת האיחוד שצוינה קודם לכן, ניתן לראות שהשדה Purchase Order ID אינו כלול וששני השדות אינם מהווים שתי רשומות נפרדות.

אם ברצונך לכלול את כל הרשומות, השתמש ב- UNION ALL במקום ב- UNION ב- SQL. סביר להניח שהדבר ישפיע על מיון התוצאות, לכן מומלץ לכלול גם פסוקית ORDER BY כדי לקבוע סדר מיון. הנה ה- SQL שעבר שינוי תוך התבססות על הדוגמה הקודמת:

SELECT [Purchase Order Details].[Date Received], Null As [Total], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION ALL

SELECT Max([Date Received]), "Total" As [Total], Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Total];

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

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

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

1. צור שאילתת בחירה פשוטה באמצעות תחביר SQL זה:

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees;

2. עבור לתצוגת גליון נתונים. אתה אמור לראות את התוצאות הבאות:

   City	Filter
   Seattle	Seattle
   Bellevue	Bellevue
   Redmond	Redmond
   Kirkland	Kirkland
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle

3. ייתכן שלא תפיק תועלת רבה מתוצאות אלה, כפי שהן מוצגות. הרחב את השאילתה והמר אותה לשאילתת איחוד באמצעות ה- SQL הבא:

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees
   
   UNION
   
   SELECT "<All>", "*" AS Filter
   FROM Employees
   
   ORDER BY City;

4. עבור לתצוגת גליון נתונים. אתה אמור לראות את התוצאות הבאות:

   City	Filter
   <All>	*
   Bellevue	Bellevue
   Kirkland	Kirkland
   Redmond	Redmond
   Seattle	Seattle

   Access מבצע איחוד של תשע הרשומות, שהוצגו קודם לכן, באמצעות ערכי שדות קבועים של <All> ו- "*".

   מאחר שפסוקית איחוד זו אינה מכילה את UNION ALL‏, Access מחזיר רשומות נפרדות בלבד, ומשמעות הדבר היא שכל עיר מוחזרת פעם אחת בלבד עם ערכים זהים קבועים.

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

   Me.Filter = "[City] Like '" & Me![FilterComboBoxName].Value & "'"
   Me.FilterOn = True

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

לראש הדף