เพิ่มประสิทธิภาพฐานข้อมูล Access: เพิ่มคอลัมน์คำนวณสำหรับการคำนวณข้ามตารางอัตโนมัติและแม่นยำ

Microsoft Access เสนอคุณสมบัติที่ทรงพลังสำหรับทำให้การคำนวณซับซ้อนข้ามตารางที่เชื่อมโยงกันทำงานอัตโนมัติ โดยลดการป้อนข้อมูลด้วยมือ ลดข้อผิดพลาด และรักษาความสอดคล้องของฐานข้อมูลในเวลาจริง คอลัมน์คำนวณ (Calculated fields) สามารถทำให้ฐานข้อมูลเป็นอัตโนมัติมากขึ้น แทนที่จะให้ผู้ใช้ป้อนยอดรวม ส่วนลด วันที่ครบกำหนด หรือค่ากำไรด้วยตนเอง Access สามารถคำนวณค่าเหล่านี้จากฟิลด์ที่มีอยู่แล้ว

ในบทช่วยสอนนี้ เราจะแสดงวิธีเพิ่มคอลัมน์คำนวณในตาราง Access สำหรับการคำนวณข้ามตารางที่เชื่อมโยงกันอัตโนมัติ มาสร้างคอลัมน์คำนวณที่ดึงและคำนวณค่าข้ามตารางที่เกี่ยวข้องกันโดยอัตโนมัติ

จำไว้ว่าในฐานข้อมูลขั้นสูง คอลัมน์คำนวณไม่ควรใช้แบบสุ่ม กฎสำคัญที่สุดคือ:

• ใช้คอลัมน์คำนวณสำหรับค่าที่ขึ้นอยู่กับฟิลด์ในเรคคอร์ดเดียวกัน
• ใช้ Query สำหรับการคำนวณที่ขึ้นอยู่กับตารางที่เชื่อมโยงหรือหลายเรคคอร์ด

ขั้นตอนที่ 1: ตั้งค่าตารางตัวอย่างที่เชื่อมโยงกัน

คอลัมน์คำนวณที่เข้าถึงข้ามตาราง ขึ้นอยู่กับความสัมพันธ์ที่มั่นคง เริ่มที่นี่ก่อนเขียนนิพจน์ใดๆ

สร้างความสัมพันธ์ (Create the Relationship):

• ไปที่แท็บ Database Tools >> เลือก Relationships
• เพิ่มตาราง
  • ลาก CustomerID จากตาราง Customers ไปที่ CustomerID ในตาราง Orders
  • ลาก ProductID จากตาราง Products ไปที่ ProductID ในตาราง OrderDetails
  • ลาก OrderID จากตาราง Orders ไปที่ OrderID ในตาราง OrderDetails
• เปิดใช้งาน Enforce Referential Integrity
• คลิก OK

การตั้งค่านี้อนุญาตให้การคำนวณข้ามตารางได้ (เช่น ยอดรวมออเดอร์เป็นผลรวมของยอดรายการ) การเชื่อมโยงนี้คือสิ่งที่ทำให้การค้นหาข้ามตารางน่าเชื่อถือ โดยไม่มีมัน คอลัมน์คำนวณอาจคืนค่า null แบบเงียบๆ เมื่อเรคคอร์ดที่อ้างอิงถูกลบหรือไม่ตรงกัน

ขั้นตอนที่ 2: เพิ่มคอลัมน์คำนวณง่ายๆ ลงในตาราง

• เปิดตาราง OrderDetails ในมุมมอง Design View
• ในแถวว่างแรก:
  • ชื่อฟิลด์: LineTotal
  • ชนิดข้อมูล: Calculated
• Access จะเปิด Expression Builder
• ป้อนสูตรของคุณ:

• หรือสร้างด้วยภาพ: ขยายตาราง OrderDetails >>ดับเบิลคลิก Quantity และ UnitPrice แล้วเพิ่มตัวดำเนินการ *
• ตั้งค่า Result Type (Currency, Number, Text, ฯลฯ) ให้ตรงกับสิ่งที่นิพจน์คืนค่า
  • ขยาย Field Properties >> เลือก Currency
• Save ตาราง

รวมส่วนลด (Total with Discounts):

[Quantity] * [UnitPrice] * (1 - [DiscountRate])

Access จะคำนวณ LineTotal อัตโนมัติสำหรับทุกเรคคอร์ด — ไม่ต้องใช้ VBA ไม่ต้องอัปเดตด้วยมือ ทุกครั้งที่คุณเพิ่มหรือแก้ไข Quantity หรือ UnitPrice ในมุมมอง Datasheet View LineTotal จะอัปเดตโดยอัตโนมัติ

ขั้นตอนที่ 3: จัดการการคำนวณข้ามตาราง — ใช้ Domain Aggregate Functions ใน Query

การอ้างอิงข้ามตารางโดยตรงไม่ได้รับอนุญาตในคอลัมน์คำนวณของตาราง คุณต้องใช้ Query หรือ VBA ฟังก์ชัน Domain Aggregate เป็นกลไกในตัวของ Access สำหรับดึงค่าที่คำนวณแล้วจากตารางหรือ Query อื่นเข้าไปในนิพจน์ ฟังก์ชันที่มีประโยชน์ที่สุดคือ:

สร้าง Query:

• ไปที่แท็บ Create >> เลือก SQL Query
• จากหน้าต่าง Add Tables เพิ่มตาราง Orders และ Customers
• เพิ่มฟิลด์: CustomerName, OrderID
• ในคอลัมน์ Field ที่ว่าง สร้างคอลัมน์คำนวณสำหรับ Total
• ใส่นิพจน์ต่อไปนี้:

Total: DSum("[LineTotal]","OrderDetails","[OrderID]=" & [OrderID])

• DSum() ผลรวม LineTotal สำหรับ OrderID ที่ตรงกัน (ฟังก์ชัน Domain Aggregate ที่ทำงานข้ามตาราง)
• บันทึกเป็น qryOrderSummary
• คลิก Run

Query จะคำนวณใหม่ทุกครั้งที่รัน ใช้ Query นี้เป็น Record Source สำหรับฟอร์ม รายงาน หรือเป็นพื้นฐานสำหรับการคำนวณต่อไป

จัดรูปแบบเป็นสกุลเงิน (Format with Currency):

Total: CCur(DSum("[LineTotal]","OrderDetails","[OrderID]=" & [Orders].[OrderID]))

ขั้นตอนที่ 4: สร้าง Query คำนวณหลายตารางเป็นแหล่งคำนวณ

สำหรับสถานการณ์ที่ซับซ้อนกว่า — เช่น การคำนวณยอดรวมหลังหักส่วนลดที่เกี่ยวข้องกับระดับลูกค้าจากตารางหนึ่ง และราคาสินค้าจากอีกตาราง — คุณควรสร้าง Base Query ที่ Join ตารางที่เกี่ยวข้องทั้งหมด แล้วอ้างอิง Query นั้นในคอลัมน์คำนวณหรือฟอร์มของคุณ

ขั้นตอน:

• ไปที่แท็บ Create >> เลือก SQL Query
• เพิ่ม Orders, Products, และ OrderDetails ลงใน Query
• เพิ่มฟิลด์: OrderID, ProductName
• เพิ่มคอลัมน์คำนวณในเซลล์ Field ที่ว่าง:

Profit: [DiscountedTotal] - [CostPrice]

• บันทึก Query เป็น qryOrderProfit
• คลิก Run

ตอนนี้คุณมีรายงานกำไรสำหรับทุกออเดอร์พร้อมชื่อสินค้า

ตอนนี้ ฟอร์ม รายงาน หรือคอลัมน์คำนวณระดับล่างใดๆ สามารถใช้ DLookup() หรือ Subquery กับ qryOrderProfit เพื่อได้ค่าที่คำนวณครบถ้วน — ทั้งหมดข้ามตารางและอัตโนมัติ

ขั้นตอนที่ 5: ทำให้การอัปเดตอัตโนมัติด้วย Data Macros

เมื่อคุณต้องการให้ผลลัพธ์ที่คำนวณได้ถูกเก็บไว้ (ไม่ใช่แค่แสดง) — เช่น เขียนยอดรวมที่คำนวณได้กลับไปที่ตาราง Orders ทุกครั้งที่เรคคอร์ด OrderDetails เปลี่ยนแปลง — ให้ใช้ Data Macro ที่แนบอยู่กับตารางลูก (child table)

เพิ่มฟิลด์ Total ลงในตาราง Orders:

• ก่อนอื่น เปิดตาราง Orders ใน Design View
  • ชื่อฟิลด์: Total
  • ชนิดข้อมูล: Currency

ฟิลด์นี้ต้องเป็นฟิลด์ Currency ธรรมดา ไม่ใช่คอลัมน์คำนวณ

การตั้งค่า (Setup):

• เปิด OrderDetails ใน Design View
• ไปที่แท็บ Table Tools >> เลือกแท็บ Table >> เลือก After Insert / After Update

• ใน Macro Editor ใช้ Action SetField และ LookupRecord:
• เลือก LookupRecord

Lookup Up A Record In: Orders Where Condition: [Orders].[OrderID] = [OrderDetails].[OrderID]

• เลือก EditRecord >> เลือก SetField

Name: [Orders].[Total] Value: DSum("[LineTotal]","OrderDetails","[OrderID]=" & [OrderID])

• คลิก Save

สิ่งนี้จะทำงานอัตโนมัติทุกครั้งที่มีการแทรกหรืออัปเดต OrderDetails ดันยอดรวมที่คำนวณใหม่กลับไปที่ตาราง Orders แม่ — อัตโนมัติทั้งหมด ไม่ต้องใช้ VBA

ทำซ้ำสำหรับ After Update:

Macro After Insert อัปเดตยอดรวมเฉพาะเมื่อเพิ่มแถวรายละเอียดใหม่ หากผู้ใช้เปลี่ยน Quantity, UnitPrice, หรือ DiscountRate ยอดรวมก็ต้องอัปเดตด้วย

เพิ่ม Macro เดียวกัน:

Look Up A Record In Orders Where Condition: [Orders].[OrderID]=[OrderDetails].[OrderID]
EditRecord
SetField Name: [Orders].[Total] Value: DSum("[LineTotal]","OrderDetails","[OrderID]=" & [OrderDetails].[OrderID])

• Save มัน

ตอนนี้ ทุกครั้งที่มีการแทรกหรืออัปเดตเรคคอร์ดใน OrderDetails Access จะคำนวณยอดรวมออเดอร์ใหม่และเก็บไว้ในเรคคอร์ดที่ตรงกันในตาราง Orders โดยอัตโนมัติ

ขั้นตอนที่ 6: แสดงและใช้การคำนวณ

• ในมุมมอง Datasheet View: คอลัมน์คำนวณจะปรากฏและอัปเดตแบบเรียลไทม์
• ใน Forms/Reports: ใช้ Query (qryOrderSummary) เป็นฐานสำหรับฟอร์มหรือรายงานเพื่อผลลัพธ์ข้ามตารางครบถ้วน เพิ่ม Text Box แบบ Unbound พร้อมนิพจน์
• การกรอง/การจัดเรียง (Filtering/Sorting): คุณสามารถใช้คอลัมน์คำนวณในเงื่อนไข Query หรือการจัดเรียงได้

เคล็ดลับ: เพื่อแก้ไขคอลัมน์คำนวณของตารางภายหลัง:

• ในมุมมอง Datasheet View: เลือก คอลัมน์ >> เลือกแท็บ Fields >> แก้ไข Expression
• หรือกลับไปที่ Design View >> เลือก Properties >> เลือก Expression

แนวทางปฏิบัติที่ดีและข้อควรพิจารณาด้านประสิทธิภาพ

• เลือก Query มากกว่าคอลัมน์คำนวณของตาราง สำหรับทุกอย่างที่เกี่ยวข้องกับหลายตาราง การรวมข้อมูล หรือการเปลี่ยนแปลงในอนาคต Query มีความพกพาสูงกว่า (เช่น ย้ายไป SQL Server ได้) และยืดหยุ่นกว่า
• หลีกเลี่ยงการเก็บการคำนวณ เว้นแต่ประสิทธิภาพจะบังคับ (เช่น ชุดข้อมูลใหญ่มากที่การรวมบน-the-fly ช้า) ให้คำนวณใหม่ใน Query แทน
• Normalization: เก็บเฉพาะข้อมูลดิบ (Raw inputs) คำนวณผลลัพธ์ (Outputs) แบบไดนามิก
• การทดสอบ (Testing): หลังจากการเปลี่ยนแปลง ตรวจสอบเสมอด้วยข้อมูลตัวอย่าง โดยเฉพาะหลังเพิ่มความสัมพันธ์
• ประสิทธิภาพ (Performance): คอลัมน์คำนวณเยอะเกินไป หรือการเรียก DSum() ซับซ้อนบนตารางใหญ่ อาจทำให้ช้า ให้สร้าง Index สำหรับ Foreign Keys
• ข้อจำกัดสรุป (Limitations Recap):
  • ไม่มีฟิลด์จากตารางอื่นโดยตรงในนิพจน์คอลัมน์คำนวณของตาราง
  • ฟังก์ชันจำกัดในคอลัมน์คำนวณของตาราง (ใช้ Query เพื่อเข้าถึง VBA ทั้งหมด)
  • ผลลัพธ์เป็น Read-only
• เคล็ดลับการขยาย (Scaling Tip): สำหรับความต้องการขั้นสูงมาก พิจารณาย้าย Logic ไปยัง SQL Views หรือ Backend เช่น SQL Server ที่ Computed Columns ให้พลังมากกว่า

หลุมที่พบบ่อยและการแก้ไขปัญหา (Common Pitfalls and Troubleshooting)

• #Error หรือ #Name?: ตรวจสอบว่าชื่อฟิลด์อยู่ในวงเล็บ [] ชนิดข้อมูลตรงกัน และความสัมพันธ์ Active
• Circular References: อย่าอ้างอิงคอลัมน์คำนวณในนิพจน์ของตัวมันเอง หรือสร้างลูป
• Data Type Mismatch: ตั้งค่า Result Type อย่างชัดเจน (เช่น Currency สำหรับฟิลด์เงิน)
• Cross-Table Fails: ย้าย Logic ไป Query ที่มี Join หรือใช้ DSum()
• หากใช้ Linked Tables (เช่นไป SharePoint หรือฐานข้อมูลอื่น) คอลัมน์คำนวณอาจมีข้อจำกัดแถวหรือปัญหา Refresh

สรุป

เมื่อทำตามขั้นตอนข้างต้น คุณสามารถเพิ่มคอลัมน์คำนวณในตาราง Access สำหรับการคำนวณข้ามตารางที่เชื่อมโยงกันอัตโนมัติได้ คอลัมน์คำนวณมีประโยชน์สำหรับการทำให้การคำนวณระดับแถว (row-level) ใน Microsoft Access ทำงานอัตโนมัติ — ทำงานได้ดีสำหรับค่าเช่น ยอดรายการ ส่วนลด วันที่ครบกำหนด และกำไรต่อรายการออเดอร์ สำหรับการคำนวณข้ามตารางขั้นสูง Query เป็นเครื่องมือที่ถูกต้อง: มันปฏิบัติตามความสัมพันธ์ระหว่างตาราง และคำนวณยอดรวมออเดอร์ ยอดขายรวมลูกค้า ยอดคงเหลือสินค้า และสรุปกำไร การเชี่ยวชาญคอลัมน์คำนวณและการคำนวณข้ามตารางใน Access จะเปลี่ยนที่เก็บข้อมูลแบบคงที่ (static data store) ให้กลายเป็นระบบที่ดูแลรักษาตัวเอง (self-maintaining system)

รับแบบฝึกหัด Excel ขั้นสูงพร้อมเฉลยฟรี!