การค้นหาและลบข้อมูลที่ซ้ำกันใน Access: คู่มือฉบับสมบูรณ์

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

ไม่มีวิธีการเดียวที่ใช้ได้กับทุกกรณี: ขึ้นอยู่กับว่าคุณกำลังมองหาข้อมูลที่ซ้ำกันอย่างแม่นยำ จำเป็นต้องยอมรับข้อผิดพลาด (ชื่อหายไปหรือมีความแตกต่างเล็กน้อย) หรือต้องการล้างตารางอย่างปลอดภัยAccess มีตัวช่วยค้นหาข้อมูลที่ซ้ำกัน คำสั่ง SQL เช่น DISTINCT และ GROUP BY และความสามารถในการลบข้อมูลจำนวนมากด้วยคำสั่งลบสำหรับกรณีที่ซับซ้อนกว่านั้น มีเครื่องมือลบข้อมูลที่ซ้ำกันโดยเฉพาะพร้อมการค้นหาแบบคลุมเครือ (fuzzy search)

ค้นหารายการที่ซ้ำกันด้วยตัวช่วยการเข้าถึง

วิธีที่ตรงที่สุดสำหรับผู้ใช้ Access คือตัวช่วยสร้างการค้นหาข้อมูลซ้ำ (Duplicate Query Wizard) ตัวช่วยสร้างนี้จะสร้างคำสั่งเลือกข้อมูล (select query) ให้คุณโดยอัตโนมัติ ซึ่งจะระบุระเบียนที่มีค่าตรงกันในฟิลด์ที่คุณเลือกเพื่อให้คุณสามารถตรวจสอบผลลัพธ์ก่อนตัดสินใจใดๆ ได้

วิธีการเริ่มต้นใช้งานและตั้งค่าทีละขั้นตอน โดยไม่ต้องใช้ศัพท์เทคนิคที่ไม่จำเป็น: เปิดแท็บ สร้าง แล้วไปที่ ตัวช่วยสร้างแบบสอบถามในกล่องโต้ตอบ เลือก ตัวช่วยสร้างแบบสอบถาม เพื่อค้นหารายการที่ซ้ำกัน และยืนยันเพื่อดำเนินการต่อ

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

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

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

SQL ใน Access: แสดง ซ่อน หรือล้างข้อมูลซ้ำ

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

แสดงค่าที่ไม่ซ้ำกันแต่ละค่าเพียงครั้งเดียวด้วยคำสั่ง DISTINCT มีประโยชน์สำหรับรายงานที่คุณไม่ต้องการเห็นค่าที่ซ้ำกันเช่น รายการสินค้าที่ไม่ซ้ำกันซึ่งสั่งซื้อโดยลูกค้าแต่ละราย

เลือกรหัสลูกค้า, หมายเลขบทความ ที่แตกต่างกัน จากบทความของลูกค้า สั่งตามรหัสลูกค้า, หมายเลขบทความ

หากคุณต้องการสร้างตารางที่สะอาดปราศจากข้อมูลซ้ำซ้อนให้ใช้ร่วมกับคำสั่ง INTO เพื่อสร้างตารางใหม่จากผลลัพธ์:

เลือก DISTINCT customer_id, article_no ลงใน table_new จาก customer_articles ORDER BY customer_id, article_no;

สรุปและ "ซ่อน" ข้อมูลที่ซ้ำกันด้วย GROUP BY เมื่อเป้าหมายของคุณคือการจัดกลุ่มข้อมูลและรับค่าเมตริก (จำนวนครั้งที่ปรากฏซ้ำ ผลรวม ฯลฯ และฟิลด์คำนวณใน Access )ให้ใช้ GROUP BY ตัวอย่างเช่น จำนวนครั้งที่สินค้าแต่ละรายการถูกขายและรายได้รวมที่เกี่ยวข้อง:

เลือก article_no, COUNT(*) AS times, SUM(revenue) AS revenue จาก invoice_articles GROUP BY article_no ORDER BY times DESC, article_no;

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

เลือก t1.id, t1.name, t2.id เป็น duplicate_id, t2.name เป็น duplicate_name จาก tablename เป็น t1, tablename เป็น t2 โดยที่ t1.name = t2.name และ t1.id <> t2.id และ t1.id = ( เลือก MAX(id) จาก tablename เป็น tx โดยที่ tx.name = t1.name );

โปรดสังเกตสองรายละเอียด: จำเป็นต้องมีคีย์ (id) เพื่อระบุแต่ละแถวอย่างไม่ซ้ำกันและ MAX(id) ใช้เพื่อกำหนดว่าระเบียนใดเป็นระเบียน "หลัก" ของแต่ละกลุ่ม ส่วนระเบียนอื่นๆ จะถือเป็นตัวเลือกที่จะถูกลบ

ลบรายการที่ซ้ำกันออก โดยใช้รูปแบบข้างต้นคุณสามารถรวมรายการ ID ที่เหลืออยู่ไว้ในคำสั่ง DELETEเพื่อทำการล้างข้อมูล โดยเหลือเพียงแถวเดียวต่อกลุ่ม:

ลบจากชื่อตารางที่ id IN (เลือก t2.id จากชื่อตารางเป็น t1, ชื่อตารางเป็น t2 โดยที่ t1.name = t2.name และ t1.id <> t2.id และ t1.id = (เลือก MAX(id) จากชื่อตารางเป็น tx โดยที่ tx.name = t1.name));

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

มีข้อมูลซ้ำกันในหลายตารางหรือไม่? หากข้อมูลของคุณกระจัดกระจายอยู่ในหลายตารางหรือมาจาก Excel การนำเข้าข้อมูลจาก Excel ไปยัง Accessและสร้างแบบสอบถามการเชื่อมต่อ (join query) ก่อนเพื่อจัดเรียงแถวที่ "เข้ากันได้" ถือเป็นวิธีปฏิบัติที่ดี จากนั้นคุณสามารถใช้ DISTINCT, GROUP BY หรือการเชื่อมต่ออัตโนมัติ (auto-join) กับแบบสอบถามการเชื่อมต่อที่ได้เพื่อค้นหาข้อมูลที่ซ้ำกัน

การทำซ้ำที่ยาก: การกำจัดข้อมูลซ้ำซ้อนแบบ "อัจฉริยะ" พร้อมความทนทานต่อข้อผิดพลาด

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

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

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

ประเด็นสำคัญคือการระบุว่าคอลัมน์ใดบ้างที่จะถูกเปรียบเทียบและเปรียบเทียบอย่างไรตัวอย่างเช่น ในรายการที่อยู่ คุณสามารถกำหนดฟิลด์ถนนเป็น "ถนน" และตั้งค่าเกณฑ์ไว้ที่ 50% เพื่อให้เฉพาะแถวที่มีความคล้ายคลึงกันของชื่อถนนเกินกว่าเกณฑ์นั้นเท่านั้นที่จะถูกพิจารณาว่าเป็นข้อมูลซ้ำกัน นอกจากนี้ยังเป็นเรื่องปกติที่จะจัดกลุ่มหลายคอลัมน์ (เช่น "ชื่อ" + "นามสกุล" + "บริษัท") เพื่อให้สามารถเปรียบเทียบร่วมกันก่อนที่จะตัดสินใจว่าบันทึกสองรายการนั้นเป็นของบุคคลหรือหน่วยงานเดียวกันหรือไม่

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

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

การกำจัดอย่างปลอดภัย: รายการตรวจสอบก่อนการกำจัด การปรึกษาหารือก่อนการกำจัด และกรณีศึกษาเชิงปฏิบัติ

ก่อนที่จะลบอะไรก็ตาม ควรเตรียมตัวให้พร้อมก่อนการลบไม่สามารถย้อนกลับได้และหากมีความสัมพันธ์ด้านความสมบูรณ์ของข้อมูลอ้างอิง คุณอาจลบข้อมูลมากกว่าที่ตั้งใจไว้ ตรวจสอบรายการตรวจสอบอย่างรวดเร็วนี้

– ตรวจสอบสิ่งนั้น ไฟล์ .accdb ไม่ใช่แบบอ่านอย่างเดียว.
– หากมีการแชร์ ให้ขอให้ผู้ใช้รายอื่น ปิดวัตถุ ที่คุณจะร่วมงานด้วยเพื่อหลีกเลี่ยงการติดขัดและความขัดแย้ง
– หากคุณได้รับอนุญาต เปิดฐานในโหมดพิเศษ (ไฟล์ > เปิด > ลูกศรถัดจาก เปิด > เปิดในโหมดพิเศษ)
- ทำ การสำรองข้อมูลเป็นวิธีเดียวที่จะกู้คืนแถวได้จริงหากมีบางอย่างผิดพลาด

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

ขั้นตอนแนะนำ: บนแท็บ สร้าง ไปที่ ออกแบบแบบสอบถาม และเพิ่มตารางที่เกี่ยวข้องดับเบิ้ลคลิกเครื่องหมายดอกจัน (*) หากคุณต้องการดูฟิลด์ทั้งหมด และเพิ่มฟิลด์ที่คุณจะใช้ในการกรองลงในตาราง (ตัวอย่างเช่น CustomerID และ OrderDate เพื่อค้นหาคำสั่งซื้อที่รอดดำเนินการจากลูกค้าที่ไม่ได้ใช้บริการกับเราแล้ว)

ป้อนเกณฑ์ที่เกี่ยวข้อง (รหัสลูกค้า วันที่หลังจากวันที่กำหนด ฯลฯ) และยกเลิกการเลือก "แสดง" สำหรับช่องเกณฑ์เหล่านั้นเพื่อให้มุมมองไม่รก คลิก "เรียกใช้" เพื่อตรวจสอบว่าแสดงเฉพาะระเบียนที่คุณต้องการลบเท่านั้น

เมื่อคุณพอใจกับผลลัพธ์แล้ว ให้กลับไปที่มุมมองการออกแบบ (Design View) และในแท็บการออกแบบ (Design) ให้คลิก ลบ (Delete) Access จะแปลงคิวรีเป็น "คิวรีลบ" (delete query)โดยจะซ่อนแถวแสดง (Show row) และเพิ่มแถวลบ (Delete row) ซึ่งคุณจะเห็น "จาก" (From) อยู่ใต้คอลัมน์ที่มีเครื่องหมายดอกจัน (*) และ "ที่ไหน" (Where) อยู่ใต้คอลัมน์ที่มีเกณฑ์ เรียกใช้คิวรีอีกครั้งและยืนยันผลลัพธ์

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

ตัวอย่างการใช้งานจริง: “ฉันต้องการเฉพาะค่าที่ไม่ซ้ำกันจากฟิลด์เดียวเท่านั้น” ปัญหานี้มักเกิดขึ้นในเธรดทั่วไป: ผู้ใช้เขียน`SELECT DISTINCT IdOperador FROM TRegO WHERE IdOperador IS NOT NULL`และได้ผลลัพธ์ 105 แถวที่ไม่ซ้ำกัน แต่เมื่อเพิ่มฟิลด์อื่นๆ (CIF, Nombre) ผลลัพธ์จะเพิ่มขึ้น (107, 116…) เพราะDISTINCT ถูกนำไปใช้กับทุกคอลัมน์ในคำสั่ง SELECTคำอธิบายนั้นง่ายมาก: หากคุณเพิ่มคอลัมน์มากขึ้น การรวมกันทั้งหมดจะต้องไม่ซ้ำกันดังนั้นแถวที่แตกต่างกันใน Nombre (ด้วยเครื่องหมายจุลภาค ช่องว่างเพิ่มเติม ฯลฯ) จะไม่ถูกจัดกลุ่มเป็นรายการซ้ำอีกต่อไป

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

-- 1) เลือกแถวที่เสถียรตามกลุ่ม (ตัวอย่างเช่น แถวที่มี Id สูงที่สุด) SELECT t.* FROM TRegO AS t INNER JOIN ( SELECT IdOperador, MAX(Id) AS IdMax FROM TRegO WHERE IdOperador IS NOT NULL GROUP BY IdOperador ) AS g ON t.IdOperador = g.IdOperador AND t.Id = g.IdMax; -- 2) หรือคืนค่ารวมตามกลุ่ม (ค่าตัวอักษรตัวแรกหรือตัวล่างสุด) SELECT IdOperador, MIN(Nombre) AS NombreExample, MIN(CIF) AS CIFExample FROM TRegO WHERE IdOperador IS NOT NULL GROUP BY IdOperador;

ตัวเลือกแรกจะส่งคืนแถว "มาตรฐาน" ที่สมบูรณ์สำหรับแต่ละรหัสผู้ปฏิบัติงาน (รหัสที่มีหมายเลขสูงสุด) ตัวเลือกที่สองจะให้ค่าที่รวบรวมตามกลุ่ม (เช่น เรียงตามลำดับตัวอักษรที่ต่ำที่สุด) หลีกเลี่ยงการใช้ DISTINCT ร่วมกับคอลัมน์เพิ่มเติมหากคุณต้องการเพียงแถวเดียวต่อคีย์การเลือกใช้ GROUP BY หรือการเลือกย่อยด้วย MAX/MINจะช่วยให้คุณควบคุมได้ตามต้องการ

หากขั้นตอนสุดท้ายคือการกำจัดข้อมูลซ้ำซ้อนให้เหลือเพียงแถวเดียวต่อ OperatorId ให้รวมรูปแบบก่อนหน้านี้เข้ากับคำสั่งลบ : ขั้นแรกให้ระบุว่าคุณต้องการเก็บ Id ใดจากแต่ละกลุ่ม (MAX(Id) หรือ MIN(Id)) จากนั้นลบส่วนที่เหลือด้วยคำสั่ง DELETE…WHERE Id NOT IN (… kept …) โดยต้องทำการสำรองข้อมูลไว้ก่อนเสมอ

เมื่อจัดการกับข้อมูลซ้ำใน Access สิ่งสำคัญคือต้องมีเป้าหมายที่ชัดเจน: "การไม่แสดงข้อมูลซ้ำ" ไม่เหมือนกับ "การลบระเบียน" หรือ "การค้นหาความคล้ายคลึงโดยประมาณ " ตัวช่วยสร้างจะช่วยเร่งการตรวจจับเบื้องต้น SQL ให้ความแม่นยำสูง (DISTINCT, GROUP BY, autojoins และ DELETE) และสำหรับกรณีที่ซับซ้อนซึ่งเกี่ยวข้องกับที่อยู่และชื่อ แนะนำให้ใช้การลบข้อมูลซ้ำแบบคลุมเครือโดยใช้เครื่องมือเฉพาะทาง ด้วยการสำรองข้อมูล การทดสอบก่อนหน้า และการออกแบบแบบสอบถามอย่างรอบคอบ การทำความสะอาดข้อมูลจึงกลายเป็นกระบวนการที่เชื่อถือได้และทำซ้ำได้

บทความที่เกี่ยวข้อง:

วิธีการนำเข้าข้อมูลจาก Excel ไปยัง Access อย่างรวดเร็วและง่ายดาย

ไอแซก

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