การใช้ 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 จะดีกว่า

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

ทั่วโลก ในเว็บนี้

เกร็ดความรู้

สุขภาพอนามัย โภชนาการและอื่น ๆ การใช้ Windows จิปาถะ เบ็ดเตล็ด

พรรคการเมือง ยอดนิยม
ท่านชอบพรรคใดมากที่สุด?
ประชาธิปัตย์
พลังประชาชน
ชาติไทย
ยังไม่ตัดสินใจ