การใช้ Domain Aggregate Functions ของ MS Access

โดย ทองจุล ขันขาว [ ส่งเมล์ถึงผู้เขียน ]

Domain Aggregate Functions เป็นฟังชั่นที่มีประโยชน์สำหรับการค้นหาข้อมูลในตาราง หรือ ตารางสัมพันธ์เสมือน (query) เข่น ใช้ DCount นับจำนวนสิ่งต่าง ๆ ใช้ DLookup เพื่อค้นหาข้อมูลในตาราง ใช้ DSum เพื่อรวมข้อมูลตามเงื่อนไขที่กำหนด เป็นต้น นอกจากฟังชั่นดังกล่าวแล้วก็ยังมีฟังชั่นอื่น ๆ อีก เช่น DFirst, DLast, DMin, DMax เป็นต้น ซึ่งเป็นการหาข้อมูลจากตารางวิธีหนึ่งที่ไม่ต้องใช้ query หรือ ตารางสัมพันธ์เสมือน อีกทั้งไม่จำเป็นต้องเปิดตารางก็ได้ ฟังชั่นเหล่านี้ สามารถใช้ใน control ต่าง ๆ บน ฟอร์ม หรือบนรายงาน ที่มีการคำนวน หรือแม้แต่ใน query หรือตารางสัมพันธ์เสมือน เองก็ตาม ก็สามารถใช้ฟังชั่นต่าง ๆ เหล่านี้ได้ นอกจากนี้ยังสามารถใช้กับ macro และ module ได้อีกด้วย ซึ่งนับว่ามีประโยชน์มาก

รูปแบบการใช้มีดังนี้

ชื่อฟังชั่น(นิพจน์, ที่อยู่ [,เงื่อนไข])
ชื่อฟังชั่น หมายถึงฟังชั่นที่เราต้องการใช้ เช่น DCount, DLookup เป็นต้น
นิพจน์ เป็นชื่อ field ในตารางที่ต้องการนำข้อมูลมาใช้ ซึ่งอาจจะมีการคำนวนด้วยก็ได้
ที่อยู่ เป็นชื่อตาราง (table) หรือ ตารางสัมพันธ์เสมือน (query) ของ record ที่ต้องการข้อมูล
เงื่อนไข เทียบได้เท่ากับ WHERE ในการใช้ SQL เป็นการกำหนดขอบเขตของการเลือก เข่น เอาข้อมูลเฉพาะของผู้ชายเท่านั้น เงื่อนไขนี้อาจจะมีหรือไม่มีก็ได้ ถ้าไม่กำหนด โปรแกรมก็จะเอาข้อมูลทั้งหมดที่พบในตาราง หรือ ตารางสัมพันธ์เสมือน

ต่อไปนี้จะเป็นตัวอย่างการใช้งานบางส่วน ผมขอยกตัวอย่างจากโปรแกรม กองทุนหมู่บ้าน ที่พัฒนาขึ้น เพื่อจะได้เห็นเป็นรูปธรรม (โปรแกรมดาวน์โหลดได้ที่ www.thaijobmarket.com หรือ www.thaiware.com )

ตัวอย่างที่ 1 (เงื่อนไขอย่างเดียว)

ผมต้องการทำรายงานการส่งคืนเงินต้นเข้ากองทุนของสมาชิก โดยให้โปรแกรมไปรวมการส่งเงินของแต่ละคน เท่าที่เคยส่งมาแล้วให้หมดทุกครั้ง บนรายงานผมจะนำเอาเลขที่สมาชิกมาไว้บน control ที่ชื่อว่า custID ผมต้องการเรียกใช้ข้อมูลจากตารางโดยตรง จึงใช้ฟังชั่น DSum ดังนี้

DSum("[mPrincipal]","tblReturnMillion","[CustID] =[Reports]![rptCustomersM_short]![custID]")

ความหมาย ให้รวมข้อมูลในฟิลด์ mPrincipal ของตาราง tblReturnMillion เอาข้อมูลมาเฉพาะ เลขประจำตัว ซึ่งอยู่ในฟิลด์ custID (ของตารtblReturnMillion) ที่มีค่าเท่ากับหมายเลขประจำตัว ซึ่งอยู่บน control ที่ชื่อ custID ของรายงานชื่อ rptCustomersM_short

ใน MS Access การอ้างถึง ฟิลด์ในตาราง จะต้องอยู่ในเครื่องหมาย [ และ ] ดังนั้น [mPrincipal] จึงเป็นการอ้างถึงฟิลด์ในตาราง และเราทราบว่า ตารางที่หมายถึงก็คือตาราง tblReturnMillion เพราะตามรูปแบบแล้ว ส่วนนี้จะบอกให้ทราบถึงที่อยู่ของฟิลด์ ดังนั้นเราจะเห็นว่า แต่ละส่วนที่คั่นด้วยเครื่องหมายคอมม่า มีความสัมพันธ์กัน

ในส่วนเงื่อนไข ก็เหมือนกัน [CustID] อ้างถึงข้องมูลในตารางเดิม คือตาราง tblReturnMillion โดยให้นำข้อมูลมาเฉพาะข้อมูลใน record ที่มีข้อมูลใน [CustID] เท่ากับข้อมูลที่อยุ่บน control ของรายงาน

การอ้างถึงข้อมูลบนรายงาน หรือบนฟอร์ม มีรูปแบบเฉพาะ จากตัวอย่างข้างบน [Reports] เป็นการอ้างถึง collection ของรายงาน ซี่งจะมีรายงานเก็บเอาไว้ ดังนั้นเราขึงต้องระบุเฉพาะรายงานที่ต้องการ ในตัวอย่างคือรายงานที่ชื่อ rptCustomersM_short จะสังเกตว่า การเขียนก็ต้องอยู่ในเครื่องหมาย [ และ ] เช่นเดียวกัน และต้องใช้เครื่องหมาย ! คั่นระหว่าง collection และชื่อรายงานด้วย จากนั้นจึงอ้างถึง control ที่อยู่บนรายงาน โดยใช้รูปแบบทำนองเดียวกัน

ตัวอย่างที่ 2 (เงื่อนไขหลายอย่าง)

จากตัวอย่างที่ 1 ปรากฎว่า ได้ข้อมูลที่ยังไม่ตรงความต้องการ ทั้งนี้เพราะ สมาชิกบางคนเมื่อส่งเงินคืนหมดแล้ว ก็ขอกู้ใหม่ ทำให้แต่ละคนมีรายการกู้หลายครั้ง ปัญหาก็คือ มีสมาชิกบางคนที่ส่งเงินต้นคืนหมดแล้ว และขอกู้ใหม่ โปรแกรมจะนำมารวมกันทั้งหมด ตามเงื่อนไขที่กำหนด ทำให้เงินเกินไปมาก แต่ความจริงผมต้องการเฉพาะรายการกู้ที่ยังส่งเงินต้นไม่หมดเท่านั้น (โปรแกรมทำตามที่สั่ง แต่ไม่ทำตามที่เราคิด) เมื่อเป็นอย่างนี้ จึงต้องเพิ่มเงื่อนไขในการค้นหาใหม่ให้มากขึ้นกว่าเดิม

ในการกู้เงินแต่ละครั้งของสมาชิก จะมีการกำหนดรหัสการกู้แต่ละครั้งไว้ในฟิลด์ mLoanID ในตาราง tblReturnMillion ซึ่งจะนำมาเป็นเงื่อนไขเพิ่มเติม อย่างไรก็ตาม ยังมีคำถามว่า แล้วจะรู้ได้อย่างไร การกู้ครั้งไหนที่ใช้เงินต้นหมดแล้ว การกู้ครั้งไหนที่ยังใช้คืนเงินต้นไม่หมด

หลังจากที่สมาชิกมาส่งเงินกู้คืนกองทุนแต่ละครั้ง ผมเขียนให้โปรแกรมตรวจสอบว่าส่งชำระเงินต้นครั้งนี้ เป็นการชำระเงินต้นหมดหรือยัง ถ้าหมดแล้วก็จะเปลี่ยนข้อมูลในฟิลด์ isPaidM ของตาราง tblReturnMillion จากเป็นเท็จ (false) ให้เป็นจริง (true) ข้อมูลนี้จะใช้เป็นเกณฑ์ในการเลือก ผมได้นำเอามาไว้บน control ชื่อ txtMLoanID ของรายงาน rptCustomersM_short ซึ่งเป็นรายงานปัจจุบัน เพื่อใช้สำหรับอ้างอิง และปรับเกณฑ์เสียใหม่ ดังนี้

DSum("[mPrincipal]","tblReturnMillion","[CustID] =[Reports]![rptCustomersM_short]![custID] And [mLoanID] = [Reports]![rptCustomersM_short]![txtMLoanID]")

จะสังเกตเห็นว่า การกำหนดเงื่อนไข เราสามารถกำหนดได้มากกว่าหนึ่งอย่าง เหมือนกันกับการใช้ WHERE ในคำสั่ง SQL เหมือนกัน ส่วนที่เพิ่มขึ้นมาเป็นเงื่อนไขให้เอาข้อมูลเฉพาะการกู้ครั้งที่ยังชำระเงินต้นคืนไม่หมดเท่านั้น รายการที่ชำระคืนหมดแล้ว ไม่ต้องเอามาแสดง ซึ่งได้ผลตามที่ต้องการ

ตัวอย่างที่ 3 (เงื่อนไขที่เร็วขึ้น)

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

DSum("[mPrincipal]","tblReturnMillion","[CustID] = ‘" & [Reports]![rptCustomersM_short]![custID] & ”’”)

จากตัวอย่างนี้ จะเห็นว่ามีรูปแบบที่ผิดไปจากตัวอย่างแรก มีการใช้เครื่องหมาย & เพื่อเชื่อมข้อความ และเนื่องจาก custID มีลักษณะข้อมูลเป็นตัวอักษร (text) ไม่ใช่ตัวเลข (number) จึงต้องอยู่ในเครื่องหมายคำพูด ตามรูปแบบต้องใช้เพียงขีดเดียว ไม่ใช่สองขีดตามปกติ ตามหลักคำสั่งของ SQL และในตอนท้าย ก็ต้องเพิ่มเครื่องหมายคำพูดปิดให้ด้วย โดยส่งไปในเครื่องหมายคำพูดปกติ และเชื่อมด้วยเครื่องหมาย &

ตัวอย่างที่ 4 (การใช้ DCount)

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

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

การนับจำนวน ใช้ฟังชั่น DCount เนื่องจากต้องการให้แสดงผลในขณะที่กรอกข้อมูล ดังนั้นที่ที่เหมาะจึงควรอยู่ใน event procedure ในช่องที่ให้กรอกชื่อคนค้ำประกัน โดยใส่ไว้ใน event หลังจากที่กรอกเสร็จแล้ว หรือ on exit ซึ่งเขียนได้ดังนี้

DCount("[mGuantorID]", "tblGuaranteeMillion", "[mGuantorID]= '" & thisMGuarantor & "'And [isPaid]=" & False)

จากตัวอย่าง จะเห็นว่า ได้สั่งให้นับข้อมูลใน record ที่ชื่อ mGrantorID ซึ่งเก็บข้อมูลรหัสของผู้ค้ำประกัน ในตาราง tblGuaranteeMillion โดยมีเงื่อนไขว่า ให้นับรวมเฉพาะรหัสที่มีค่าเท่ากับ ค่าตัวแปร thisMGuarantor และ ค่าในฟิลด์ isPaid ในตาราง tblGuaranteeMillion มีค่าเป็น false เท่านั้น การกำหนดเช่นนี้ ก็จะทำให้ได้ค่าการค้ำประกันของคนที่ยังใช้เงินต้นไม่หมดเท่านั้น การค้ำประกันที่ผู้กู้ใช้เงินต้นหมดแล้ว ก็ถือว่าการค้ำประกันสิ้นสุดไปแล้ว จึงไม่นำมารวมในการนับครั้งนี้ด้วย

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

มีข้อสังเกตการใช้เครื่องหมาย & เชื่อมข้อความ เนื่องจากในส่วนเงื่อนไขต้องเป็นส่วนเดียวกันตามข้อกำหนด และ thisMGuarantor เป็นตัวแปร ไม่สามารถอยู่ในเครื่องหมายคำพูดได้ จึงต้องมีการเชื่อมต่อด้วยเครื่องหมาย & ดังกล่าวข้างต้น และเช่นเดียวกับตัวอย่างก่อน เนื่องจากลักษณะข้อมูลของเรคคอร์ด mGuarantorID มีลักษณะเป็นตัวอักษร จึงต้องอยู่ในเครื่องหมายคำพูด (ขีดเดียว) ด้วย

ตัวอย่างที่ 5 (การใช้ DLookup)

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

thisCustID = DLookup("[custID]", "qryCustomers", "[name] = '" & str & "'")

[custID] และ [name] เป็นฟิลด์ในตารางเสมือน qryCustomers ซี่งเก็บข้อมูลเกี่ยวกับเลขที่สมาชิก และชื่อสมาชิกตามลำดับ

เท่าที่ยกตัวอย่างการใช้มานี้ คิดว่าพอจะเป็นแนวทางในการใช้งานได้บ้างตามสมควร อย่างไรก็ตาม การอ่านข้อมูลจำนวนมาก โดยใช้ฟังชั่นพวกนี้ อาจจะไม่ค่อยเหมาะสมนัก เพราะจะใช้เวลาพอควรทีเดียว ควรใช้ DAO หรือ ADO จะดีกว่า

คงยังมีอีกหลายเรื่องที่น่าสนใจ แต่คราวนี้พอแค่นี้ก่อนก็แล้วกัน

อ.ทองจุล ขันขาว