Often data is entered into tables by many different users. Sometimes data is repeated, or there is common information in several different records. This section explains how to find all rows in a table that, for a given column, share a value with another row. This is accomplished by performing two SQL Select statements.
The first SQL Select statement produces a query table with two columns. The first column is a list of all unique values in the data column and the second column lists the number of times that each unique value occurs. The second SQL statement compares each data column value with all rows in the Query table where the count is greater than one.
In the next example, there is a table EMPLOYEE that has two columns Id_Num and Name.
To find the duplicate values, perform the two SQL Selects, modifying them where indicated.
- On the TABLE tab, in the Selection group, click SQL Select and fill in the SQL Select dialog box.
- Select Columns:
ID_Num,
Count(*)
- from Tables: EMPLOYEE
- Group by Columns:
1
- Order by Columns:
2 desc
- into Table Named:
Selection
Substitute the name of your data column for ID_Num and the name of your table for EMPLOYEE. The number 1 in the Group By Columns box will group the row by ID_Num (the first column). The 2 Desc in the Order by Columns box will arrange the records in descending order based on the values in the count(*) field (the second column).
This SQL Select statement returns a query table with two columns. The first column contains every identification number possessed by at least one employee. The second column contains the number of employees that have that identification number. The rows are sorted by the number of employees that have each id number (for example, the count).
- Select Columns:
ID_Num,
- On the TABLE tab, click SQL Select and fill in the dialog box.
Also, change EMPLOYEE to the name of your table and ID_Num to the name of your data column.
In the example, the SQL statement returns a query table containing all of the rows from EMPLOYEE with duplicated data column values. The where condition selects all rows from EMPLOYEE that have an identification number that is the same as one of the ID numbers in the Count_By_ID query table. This sub-select finds all identification numbers that occur more than once.