ปรับปรุงฐานข้อมูลการเข้าถึงของคุณ:การเพิ่มฟิลด์จากการคำนวณสำหรับการคำนวณข้ามตารางอัตโนมัติและแม่นยำ

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

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

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

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

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

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

สร้างความสัมพันธ์:

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

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

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

• เปิดรายละเอียดคำสั่งซื้อ ตารางใน มุมมองการออกแบบ
• ในแถวแรกว่าง:
  • ชื่อฟิลด์:LineTotal
  • ประเภทข้อมูล:คำนวณแล้ว
• Access เปิด ตัวสร้างนิพจน์
• ป้อนสูตรของคุณ:

• หรือสร้างด้วยภาพ:ขยาย รายละเอียดการสั่งซื้อ ตาราง>> ดับเบิลคลิก ปริมาณ และ ราคาต่อหน่วย จากนั้นเพิ่ม * โอเปอเรเตอร์
• ตั้งค่า ประเภทผลลัพธ์ (สกุลเงิน ตัวเลข ข้อความ ฯลฯ) เพื่อให้ตรงกับนิพจน์ที่ส่งคืน
  • ขยาย คุณสมบัติของฟิลด์>> เลือก สกุลเงิน
• บันทึก ตาราง

ยอดรวมพร้อมส่วนลด:

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

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

ขั้นตอนที่ 3:การจัดการการคำนวณแบบเชื่อมโยงข้าม — ใช้ฟังก์ชันการรวมโดเมนในการสืบค้น

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

สร้างแบบสอบถาม:

• ไปที่สร้าง แท็บ>> เลือก แบบสอบถาม SQL
• จาก เพิ่มตาราง บานหน้าต่าง เพิ่มตารางคำสั่งซื้อและลูกค้า
• เพิ่มฟิลด์:ชื่อลูกค้า, รหัสคำสั่งซื้อ
• ในคอลัมน์ฟิลด์ว่าง ให้สร้างฟิลด์จากการคำนวณสำหรับ ผลรวม
• แทรกนิพจน์ต่อไปนี้:

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

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

แบบสอบถามจะคำนวณใหม่ทุกครั้ง ใช้แบบสอบถามนี้เป็นแหล่งบันทึกสำหรับแบบฟอร์ม รายงาน หรือเป็นพื้นฐานสำหรับการคำนวณเพิ่มเติม

รูปแบบด้วยสกุลเงิน:

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

ขั้นตอนที่ 4:การสร้างแบบสอบถามที่คำนวณด้วยคอมพิวเตอร์หลายตารางเป็นแหล่งการคำนวณ

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

ขั้นตอน:

• ไปที่สร้าง แท็บ>> เลือก แบบสอบถาม SQL
• เพิ่ม คำสั่งซื้อ , ผลิตภัณฑ์ และ รายละเอียดการสั่งซื้อ ไปยังแบบสอบถาม
• เพิ่มฟิลด์:OrderID, ProductName
• เพิ่มคอลัมน์จากการคำนวณในเซลล์ฟิลด์ว่าง:

Profit: [DiscountedTotal] - [CostPrice]

• บันทึกคำค้นหาเป็น qryOrderProfit
• คลิก เรียกใช้

ขณะนี้คุณมีรายงานผลกำไรสำหรับคำสั่งซื้อทั้งหมดที่มีชื่อผลิตภัณฑ์

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

ขั้นตอนที่ 5:การอัปเดตอัตโนมัติด้วยมาโครข้อมูล

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

เพิ่มฟิลด์ผลรวมลงในตารางคำสั่งซื้อ:

• ขั้นแรก เปิดคำสั่งซื้อ ตารางใน มุมมองการออกแบบ
  • ชื่อฟิลด์:ผลรวม
  • ประเภทข้อมูล:สกุลเงิน

นี่จะต้องเป็นฟิลด์สกุลเงินปกติ ไม่ใช่ฟิลด์จากการคำนวณ

ตั้งค่า:

• เปิด รายละเอียดคำสั่งซื้อ ในมุมมองการออกแบบ
• ไปที่เครื่องมือตาราง แท็บ>> เลือก ตาราง แท็บ>> เลือก หลังจากแทรก / หลังการอัปเดต

• ในตัวแก้ไขมาโคร ให้ใช้ SetField และ บันทึกการค้นหา การกระทำ:
• เลือก บันทึกการค้นหา

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

• เลือก แก้ไขบันทึก>> เลือก เซ็ตฟิลด์

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

• คลิก บันทึก

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

ทำซ้ำหลังจากอัปเดต:

หลังจากการแทรก มาโครจะอัปเดตผลรวมเมื่อมีการเพิ่มแถวรายละเอียดใหม่เท่านั้น หากผู้ใช้เปลี่ยนปริมาณ ราคาต่อหน่วย หรือ DiscountRate ยอดรวมจะต้องอัปเดตด้วย

เพิ่มมาโครเดียวกัน:

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

• บันทึก มัน

ในตอนนี้ เมื่อใดก็ตามที่ระเบียนถูกแทรกหรืออัปเดตใน OrderDetails Access จะคำนวณผลรวมของคำสั่งซื้อใหม่โดยอัตโนมัติ และจัดเก็บไว้ในระเบียนที่ตรงกันในตารางคำสั่งซื้อ

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

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

เคล็ดลับ: หากต้องการแก้ไขฟิลด์จากการคำนวณของตารางในภายหลัง:

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

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

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

ข้อผิดพลาดทั่วไปและการแก้ไขปัญหา

• #Error หรือ #Name?: ตรวจสอบว่าชื่อฟิลด์อยู่ในวงเล็บ [] ประเภทข้อมูลตรงกัน และความสัมพันธ์มีการใช้งานอยู่
• การอ้างอิงแบบวงกลม: อย่าอ้างอิงฟิลด์จากการคำนวณในนิพจน์ของตัวเองหรือสร้างลูป
• ประเภทข้อมูลไม่ตรงกัน: ตั้งค่าประเภทผลลัพธ์อย่างชัดเจน (เช่น ช่องสกุลเงินสำหรับเงิน)
• การข้ามตารางล้มเหลว: ย้ายตรรกะไปยังแบบสอบถามด้วยการรวมหรือใช้ DSum()
• หากใช้ตารางที่เชื่อมโยง (เช่น SharePoint หรือฐานข้อมูลอื่น) ฟิลด์ที่คำนวณอาจมีการจำกัดแถวหรือปัญหาการรีเฟรช

บทสรุป

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