ใช้คิวรีแบบร่วมเพื่อรวมหลายคิวรีลงในผลลัพธ์เดียว

ในตัวอย่างจากส่วนก่อนหน้าที่ใช้ฐานข้อมูล 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 การเรียงลำดับจะได้รับอนุญาตเพียงครั้งเดียว แต่สามารถกรองแต่ละคิวรีแยกกันได้ จากคิวรีแบบร่วมของส่วนก่อนหน้า ต่อไปนี้เป็นตัวอย่างของตำแหน่งที่เราได้กรองแต่ละคิวรีโดยเพิ่มส่วนคำสั่ง ตำแหน่ง

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];

สลับไปยังมุมมองแผ่นข้อมูล และคุณจะเห็นผลลัพธ์ที่คล้ายกับสิ่งนี้:

ถ้าคิวรีที่จะรวมแตกต่างกันมาก คุณอาจพบสถานการณ์ที่เขตข้อมูลผลลัพธ์ต้องรวมข้อมูลชนิดต่างๆ ถ้าเป็นเช่นนั้น คิวรีแบบร่วมมักจะส่งกลับผลลัพธ์เป็นชนิดข้อมูลข้อความ เนื่องจากชนิดข้อมูลดังกล่าวสามารถเก็บทั้งข้อความ และ ตัวเลข

เมื่อต้องการทำความเข้าใจวิธีการทำงานนี้ เราจะใช้คิวรีแบบร่วม ธุรกรรมของสินค้า ในฐานข้อมูลตัวอย่าง Northwind เปิดฐานข้อมูลตัวอย่าง แล้วเปิดคิวรี ธุรกรรมของสินค้า ในมุมมองแผ่นข้อมูล สิบระเบียนสุดท้ายควรคล้ายกับผลลัพธ์นี้:

ลองสมมติว่า คุณต้องการแยกเขตข้อมูลจำนวนเป็นสองส่วน ได้แก่ ซื้อและขาย สมมติว่า คุณต้องการให้มีค่าศูนย์คงที่สำหรับเขตข้อมูลที่ไม่มีค่า ต่อไปนี้คือลักษณะที่ปรากฏของ 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;

อย่างไรก็ตาม คุณอาจเห็นการสลับไปยังมุมมองแผ่นข้อมูล ขณะนี้คุณมีผลลัพธ์ที่ไม่คาดคิด ในคอลัมน์ซื้อ เขตข้อมูลทั้งหมดจะถูกล้าง:

เกิดเหตุการณ์นี้ขึ้นเพราะ 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 เรียกใช้ค่าซื้อ แต่แปลงค่าเป็นข้อความ คุณสามารถบอกได้ว่า เป็นค่าข้อความเนื่องจากจัดชิดซ้ายในมุมมองแผ่นข้อมูล สตริงว่างในคิวรีแรกไม่ใช่ตัวเลข ซึ่งเป็นสาเหตุที่คุณเห็นผลลัพธ์เหล่านี้ นอกจากนี้คุณจะเห็นว่าค่าซื้อยังแปลงเป็นข้อความเนื่องจากระเบียนการซื้อมีสตริงว่าง

คุณแก้ไขปัญหานี้อย่างไร

วิธีแก้ไขปัญหาคือ การบังคับคิวรีให้คาดว่าค่าเขตข้อมูลเป็นตัวเลข ซึ่งสามารถดำเนินการได้ด้วยนิพจน์:

IIf(False, 0, Null)

เงื่อนไขในการตรวจสอบ เท็จ จะไม่มีวันเป็น จริง ดังนั้นนิพจน์จะส่งกลับ 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

นี่เป็นเคล็ดลับเล็กๆ น้อยๆ เนื่องจากมักเป็นเท็จเสมอ แล้วคิวรีจะไม่ส่งกลับผลใดๆ รวมคำสั่งนี้กับ 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. สร้างคิวรีแบบง่ายใหม่เพื่อดูการซื้อเบียร์ (รหัสผลิตภัณฑ์=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. สลับไปยังมุมมองแผ่นข้อมูล และคุณจะเห็นการซื้อสี่รายการ:

   วันที่ได้รับ	จำนวน
   22/1/2006	100
   22/1/2006	60
   4/4/2006	50
   5/4/2006	300

3. เมื่อต้องการดูผลรวม ให้สร้างคิวรีการรวมอย่างง่ายโดยใช้ SQL ต่อไปนี้:

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

4. สลับไปยังมุมมองแผ่นข้อมูล และคุณจะเห็นเพียงระเบียนเดียว:

   วันที่สูงสุดที่ได้รับ	ยอดรวมจำนวน
   5/4/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. สลับไปยังมุมมองแผ่นข้อมูล และคุณจะเห็นการซื้อสี่รายการที่มีผลรวมของแต่ละรายการตามด้วยระเบียนที่มีจำนวนรวม:

   วันที่ได้รับ	จำนวน
   22/1/2006	60
   22/1/2006	100
   4/4/2006	50
   5/4/2006	300
   5/4/2006	510

ซึ่งครอบคลุมพื้นฐานของการเพิ่มผลรวมลงในคิวรีแบบร่วม คุณอาจยังต้องการรวมค่าคงที่ในทั้งสองคิวรี เช่น "รายละเอียด" และ "ผลรวม" เพื่อแยกระเบียนผลรวมจากระเบียนอื่นๆ คุณสามารถตรวจทานโดยใช้ค่าคงที่ในส่วน รวมตารางหรือคิวรีอย่างน้อยสามรายการในคิวรีแบบร่วม

คิวรีแบบร่วมใน Access ตามค่าเริ่มต้นจะรวมระเบียนเฉพาะเท่านั้น แต่จะเกิดอะไรขึ้นถ้าคุณต้องการรวมระเบียนทั้งหมด อีกตัวอย่างหนึ่งอาจเป็นประโยชน์ที่นี่

ในส่วนก่อนหน้า เราได้แสดงวิธีการสร้างผลรวมในคิวรีแบบร่วมให้คุณดู ปรับเปลี่ยน SQL ของคิวรีแบบร่วมเพื่อรวมรหัสผลิตภัณฑ์=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];

สลับไปยังมุมมองแผ่นข้อมูล และคุณจะเห็นผลลัพธ์ที่อาจทำให้เกิดการเข้าใจผิด:

ระเบียนหนึ่งจะไม่ส่งกลับจำนวนเป็นสองเท่า

เหตุผลที่คุณเห็นผลลัพธ์นี้เป็นเพราะในหนึ่งวัน จำนวนช็อกโกแลตเดียวกันถูกขายสองครั้ง ตามที่บันทึกไว้ในตารางรายละเอียดใบสั่งผลิตภัณฑ์ ต่อไปนี้เป็นผลลัพธ์ของคิวรีแบบใช้เลือกข้อมูลอย่างง่าย แสดงทั้งสองระเบียนในฐานข้อมูลตัวอย่าง Northwind

ในคิวรีแบบร่วมที่ระบุไว้ก่อนหน้านี้ คุณสามารถเห็นได้ว่า ไม่มีเขตข้อมูลรหัสใบสั่งผลิตภัณฑ์ และเขตข้อมูลทั้งสองไม่ได้สร้างระเบียนเฉพาะสองระเบียน

ถ้าคุณต้องการรวมระเบียนทั้งหมด ให้ใช้ 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. สลับมุมมองแผ่นข้อมูล และคุณจะเห็นผลลัพธ์ต่อไปนี้:

   เมือง	ตัวกรอง
   ซีแอตเทิล	ซีแอตเทิล
   เบลวิว	เบลวิว
   เรดมอนด์	เรดมอนด์
   เคิร์กแลนด์	เคิร์กแลนด์
   ซีแอตเทิล	ซีแอตเทิล
   เรดมอนด์	เรดมอนด์
   ซีแอตเทิล	ซีแอตเทิล
   เรดมอนด์	เรดมอนด์
   ซีแอตเทิล	ซีแอตเทิล

3. เมื่อดูผลลัพธ์เหล่านั้น คุณอาจไม่เห็นหลายค่า ขยายคิวรี และแปลงเป็นคิวรีแบบร่วมโดยใช้ SQL ต่อไปนี้:

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

4. สลับมุมมองแผ่นข้อมูล และคุณจะเห็นผลลัพธ์ต่อไปนี้:

   เมือง	ตัวกรอง
   <ทั้งหมด>	*
   เบลวิว	เบลวิว
   เคิร์กแลนด์	เคิร์กแลนด์
   เรดมอนด์	เรดมอนด์
   ซีแอตเทิล	ซีแอตเทิล

   Access ทำการรวมเก้าระเบียนที่แสดงก่อนหน้านี้ด้วยค่าเขตข้อมูลคงที่ของ <ทั้งหมด> และ "*"

   เนื่องจากส่วนคำสั่งแบบร่วมนี้ไม่มี UNION ALL Access จะส่งกลับระเบียนเฉพาะเท่านั้น ซึ่งหมายความว่าแต่ละเมืองจะถูกส่งกลับเพียงครั้งเดียวด้วยค่าเหมือนกันที่คงที่

5. ขณะนี้คุณมีคิวรีแบบร่วมที่เสร็จสมบูรณ์ซึ่งแสดงชื่อเมืองแต่ละแห่งเพียงหนึ่งครั้ง พร้อมด้วยตัวเลือกที่เลือกเมืองทั้งหมดอย่างมีประสิทธิภาพ คุณสามารถใช้คิวรีนี้เป็นแหล่งระเบียนสำหรับกล่องคำสั่งผสมบนฟอร์ม การใช้ตัวอย่างเฉพาะเหล่านี้เป็นโมเดล คุณสามารถสร้างตัวควบคุมกล่องคำสั่งผสมบนฟอร์ม ตั้งค่าคิวรีนี้เป็นแหล่งระเบียน ตั้งค่าคุณสมบัติความกว้างคอลัมน์ของคอลัมน์ตัวกรองเป็น 0 (ศูนย์) เพื่อซ่อน แล้วตั้งค่าคุณสมบัติคอลัมน์ที่ถูกผูกไว้เป็น 1 เพื่อระบุดัชนีของคอลัมน์ที่สอง ในคุณสมบัติตัวกรองของฟอร์ม คุณสามารถเพิ่มรหัสดังต่อไปนี้เพื่อเปิดใช้งานตัวกรองฟอร์ม โดยใช้ค่าที่ถูกเลือกในตัวควบคุมกล่องคำสั่งผสม:

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

   ผู้ใช้ของฟอร์มสามารถกรองระเบียนฟอร์มเป็นชื่อเมืองเฉพาะ หรือเลือก <ทั้งหมด> เพื่อแสดงรายการระเบียนทั้งหมดสำหรับทุกเมือง

ด้านบนของหน้า