บทที่ 5

การออกแบบแบบสอบถาม (Queries)

การสร้างเขตข้อมูลใหม่บนแบบสอบถาม (Query)

การสร้างเขตข้อมูลใหม่บนแบบสอบถาม (Query)

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

สร้างเขตข้อมูลใหม่ที่เกิดจากการคำนวณหรือการกำหนดค่า

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

ชื่อเขตข้อมูล ชื่อเขตข้อมูล : สูตรการคำนวณหรือการกำหนดค่า
ตาราง  


ตัวอย่าง ต้องการกำหนดเขตข้อมูลใหม่ที่เกิดจากสูตรการคำนวณตามเงื่อนไขต่อไปนี้

  1. คำนวณ tax (ภาษี/เดือน) โดยคิด 5 % ของ salary (เงินเดือน)
  2. คำนวณ lncome (รายได้สุทธิ) โดยคิดจาก เงินเดือน-ภาษี/เดือน
ชื่อเขตข้อมูล Sal Tax: [salary]*0.05 Income:[salary]-[tax]
ตาราง Person    


       หลังจากการกำหนดเขตข้อมูลใหม่ ถ้าต้องการกำหนดคุณสมบัติของเขตข้อมูลใหม่ ให้คลิกขวาที่เขตข้อมูลใหม่ เลือก คุณสมบัติ (Properties) จะปรากฏเมนูแผ่นคุณสมบัติ (Field Properties) ให้สามารถออกแบบเพิ่มเติมได้ และเลือก มุมมองแผ่นข้อมูล (Datasheet View) เพื่อดูผลการคำนวณบนตาราง


รูปที่ 5.10 แสดงการสร้างเขตข้อมูลใหม่ที่เกิดจากสูตรการคำนวณ

สร้างเขตข้อมูลใหม่ที่เกิดจากการรวมเขตข้อมูล

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

ชื่อเขตข้อมูลใหม่ : [ชื่อเขตข้อมูล] & [ชื่อเขตข้อมูล] &
“ข้อความหรือสัญลักษณ์” &………………


ตัวอย่าง การนำเขตข้อมูล name และเขตข้อมูล surname มารวมกัน

ชื่อ-สกุล : [name] & “    “ & [surname]

 

รูปที่ 5.11 แสดงเขตข้อมูลใหม่ที่เกิดจากการรวมเขตข้อมูล

การสร้างเงื่อนไข (Condition Test)

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

รูปแบบการสร้างเงื่อนไข

รูปแบบที่ 1 กรณี 1 เงื่อนไข
ความหมาย ถ้าเงื่อนไขที่กำหนด ถูกต้อง จะกระทำคำสั่ง 1
ถ้าเงื่อนไขกำหนด ไม่ถูกต้อง จะกระทำ คำสั่ง 2
ตัวอย่าง     ต้องการกำหนดภาษี/เดือน ตามเงื่อนไขต่อไปนี้
ถ้าเงินมากว่า 20,000 คิด 5% ของเงินเดือน
ถ้าเงินเดือนไม่เกิน 20,000 คิด 2.5 % ของเงินเดือน

 

tax : iif([salary] >20000,[salary]*0.05,[salary]*0.025)

รูปที่ 5.12 แสดงการคำนวณภาษีโดยการสร้างเงื่อนไข 1 เงื่อนไข

ตัวอย่าง คำนวณโบนัส (Bonus) ให้กับพนักงานตามเงื่อนไขต่อไปนี้
ตำแหน่ง (Position) บัญชี ได้โบนัส 5 เท่าของเงินเดือน
ตำแหน่ง (Position) อื่น ๆ ได้โบนัส 3 เท่าของเงินเดือน

 

bonus : iif ([position]= “บัญชี”, [salary]*5, [salary]*3)

รูปที่ 5.13 แสดงการคำนวณโบนัสโดยสร้างเงื่อนไข 1 เงื่อนไข

รูปแบบที่ 2 กรณีหลายเงื่อนไข
ความหมาย ถ้าเงื่อนไข 1ที่กำหนด ถูกต้อง จะกระทำคำสั่ง 1
ถ้าเงื่อนไข 2 ที่กำหนด ถูกต้อง จะกระทำคำสั่ง 2
ถ้าเงื่อนไขทั้ง 2 ไม่ถูกต้อง จะกระทำคำสั่ง 3
ตัวอย่าง     ต้องการกำหนดภาษี/เดือน ตามเงื่อนไขต่อไปนี้
ถ้าเงินเดือนมากว่า 20,000 คิด 5.0% ของเงินเดือน
ถ้าเงินเดือน5,001-20,000 คิด 2.5 % ของเงินเดือน
ถ้าเงินเดือนไม่เกิน 5,000 ไม่ต้องคิดภาษี

Tax : iif ([salary]>20000,[salary]*0.05
iif([salary]>5000,[salary]*0.025,0))

 

ตัวอย่าง คำนวณโบนัส (bonus) ให้กับพนักงานเงื่อนไขต่อไปนี้
ตำแหน่ง (position) บัญชี ได้โบนัส 6 เท่า ของเงินเดือน
ตำแหน่ง (position) การเงิน ได้โบนัส 4 เท่า ของเงินเดือน
ตำแหน่ง (position) อื่นๆ ได้โบนัส 2 เท่าของเงินเดือน

Bonus : iif([position]= “บัญชี”,[salary]*6,
iif([position]=“การเงิน”,[salary]*4,[salary]*2))
  

การคำนวณเกี่ยวกับวันที่/เวลา

         ในการคำนวณข้อมูลเกี่ยวกับวันที่/เวลา ที่มีรูปแบบการบันทึกเดียวกัน d/m/yy หรือ m/d/yy สำหรับรูปแบบวันที่และ h:m:ss สำหรับรูปแบบประเภทเวลา ดังนั้นการคำนวณจำเป็นจะต้องแยกข้อมูลประเภทนี้ออกเป็นหน่วยต่างๆ เช่น หน่วยเป็น ปี เดือน วัน ชั่วโมง นาที วินาที จึงต้องมีฟังก์ชันเดียวกับวันที่เวลา เข้ามาเกี่ยวข้องในการคำนวณ จึงขอแนะนำฟังก์ชันเกี่ยวกับวันที่/เวลา

ตารางแสดงฟังก์ชันวันที่และเวลา

ฟังก์ชัน

ความหมาย

Date()

ให้ค่าวันที่ปัจจุบัน

Now()

ให้ค่าวันที่/เวลาปัจจุบัน

Year (วันที่)

ให้ค่าเฉพาะ ปี ค.ศ. 4 หลัก

Month (วันที่)

ให้ค่าเฉพาะเดือน (1-12)

Day (วันที่)

ให้ค่าเฉพาะวัน (มีค่า1-31)

Time ()

ให้ค่าเวลาปัจจุบัน

Hour (เวลา)

ให้ค่าเฉพาะชั่วโมง (0-23)

Minute (เวลา)

ให้ค่าเฉพาะนาที (0-59)

Second (เวลา)

ให้ค่าเฉพาะวินาที (0-59)

 

การคำนวณวันที่หน่วยเป็นจำนวนปี

1. การคำนวณวันที่หน่วยเป็นจำนวนปี

1.1 รูปแบบการคำนวณทั่วไป

Year (วันที่) – Year (วันที่)


ตัวอย่าง 
            ต้องการคำนวณอายุ (Age) จากวันเกิด (Born) โดยคำนวณ ณ วันที่ปัจจุบัน

Age : year (now())-year([born])

1.2 รูปแบบการคำนวณ โดยการตรวจสอบเดือนก่อน

ถ้า month (วันที่ปัจจุบัน)<month (วันที่),
year(วันที่)-year(วันที่)-1
ถ้าmonth(วันที่ปัจจุบัน)>=(วันที่),
year(วันที่)-year(วันที่)


ตัวอย่าง 
            การคำนวณ อายุ (age) จากวันเกิด (born)

Age : iif(month(now())<month([born]),
year(now())-year([born])-1,
year(now())-year([born])

2 .การคำนวณวันที่หน่วยเป็นจำนวนเดือน

       หลักการคำนวณ ในกรณีที่เดือนปัจจุบันมีค่าน้อยกว่าเดือนที่คำนวณ  จะยืมหน่วยปีมา 1 ปีมีค่า 12 เดือนมาบอกกับเดือนปัจจุบันก่อน
รูปการคำนวณ

ถ้า month(ปัจจุบัน) < month(วันที่),
month(วันที่ปัจจุบัน )+ 12 - month(วันที่),
ถ้า month(ปัจจุบัน) >= month(วันที่),
month(วันที่ปัจจุบัน) - month(วันที่)


ตัวอย่าง การคำนวณอายุ (age) หน่วยเป็นจำนวนเดือนจากวันเกิด(born)

Age : iif(month(now()) <month([born]),
Month(now())+12-month([born]),
Month(now()) - month([born])



3.  การคำนวณวันที่หน่วยเป็นจำนวนวัน

       หมายถึง ผลต่างของวันที่ จะได้ค่าเป็นตัวเลขที่จะได้ค่าผลต่าง ของจำนวนวันได้

รูปแบบการคำนวณ

วันที่-วันที่



ตัวอย่าง ต้องการคำนวณจำนวนวันที่เช่า (dayrent) จากวันที่เข้าพัก (dayin) และวันที่ออกจากที่พัก (dayout)

Dayrent : [dayout]-[dayin]

 

4. การคำนวณเวลาหน่วยเป็นเวลา

       หมายถึง ผลต่างของเวลา จะให้ค่าผลต่างของชั่วโมง ผลต่างของนาที และผลต่างของวินาที แต่จะให้ค่าในรูปแบบของเวลา

รูปแบบการคำนวณ
เวลา-เวลา


ตัวอย่าง ต้องการทราบจำนวนเวลาที่เช่าเครื่องพิมพ์ดีด (rent time) จากเวลาเริ่มเช่า (begin time)  และเวลาเลิกเช่า (end time)

Rent time : [end time]-[begin time]

 

5. การคำนวณเวลาหน่วยเป็นชั่วโมง

       

จะให้ค่าเป็นผลต่างเวลาเป็นจำนวนชั่วโมงในรูปตัวเลข

รูปแบบการคำนวณ

hour(เวลา)-hour(เวลา)


ตัวอย่าง ต้องการทราบจำนวนชั่วโมงที่เช่าเครื่องพิมพ์ดีด (rent_hour) จากเวลาเริ่มเช่า (begin_time)และเวลาเลิกเช่า(end_time)

Rent_hour :hour([end_time])-hour([begin_time])


6.การคำนวณจากตารางความสัมพันธ์

       ในการประมวลผลที่ต้องอาศัยข้อมูลจากตารางความสัมพันธ์ จะต้องสร้างเส้นเชื่อมความสัมพันธ์ ซึ่งสามารถ สร้างได้ 2 ทาง คือ การสร้างความสัมพันธ์ด้วยคำสั่ง Relationship (ในเมนู Tools) หรือสร้างบนตาราง Query

ตัวอย่าง ต้องการสร้างตารางเพื่อบันทึกการจำหน่ายสินค้าประจำวัน โดยมีรายละเอียดดังต่อไปนี้
ตาราง Sale_goods ประกอบด้วย


NO

ชื่อเขตข้อมูล

ชนิดข้อมูล

ป้ายคำอธิบาย

1

Daysale

Date/Timr

วันที่ขาย

2

G_code

Text

รหัสสินค้า

3

Num

Number

จำนวน


ตาราง goods ประกอบด้วย


No

ชื่อเขตข้อมูล

ชนิดข้อมูล

ป้ายคำอธิบาย

1

G_code(คีย์หลัก)

Text

รหัสสินค้า

2

G_type

Text

ประเภทสินค้า

3

G_name

Text

ชื่อสินค้า

4

Price

Currency

ราคา/หน่วย

5

Stock

Number

สินค้าคงเหลือ


ป้อนข้อมูลตัวอย่างในตาราง Goods จำนวน 10 รายการ

รูปที่ 5.14 แสดงข้อมูลในตารางชื่อ goods
สร้างความสัมพันธ์ระหว่างตาราง โดยกำหนดเขตข้อมูล g_code เป็นตัวเชื่อม

รูปที่ 5.15 แสดงการออกแบบความสัมพันธ์ระหว่างตาราง good และ Sale_goods

ออกแบบตารางแบบสอบถาม เพื่อคำนวณราคารวม (tot) ภาษีมูลค่าเพิ่ม (vat) รวมสุทธิ (net)  จากตาราง GOODS และ ตาราง Sale_goods

ราคารวม : ราคา/หน่วย x จำนวน
ภาษีมูลค่าเพิ่ม : ราคารวม x 0.07
รวมสุทธิ : ราคารวม + ภาษีมูลค่าเพิ่ม

รูปที่ 5.16 แสดงการออกแบบเขตข้อมูลใหม่ที่เกิดจากสูตรการคำนวณที่ความสัมพันธ์