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

ในตัวอย่างจากส่วนก่อนหน้าที่ใช้ฐานข้อมูล 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];

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

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

เมื่อต้องการทําความเข้าใจวิธีการทํางานนี้ เราจะใช้คิวรีแบบร่วม ธุรกรรมผลิตภัณฑ์ ในฐานข้อมูลตัวอย่าง 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:

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

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

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