ADO DBGrid Query Problem

  • Thread starter Thread starter manf442
  • Start date Start date
M

manf442

I have a database with an employee table. There are duplicate ss#'s in this table

I want to create a recorset that shows the duplicate records.

In Access 2000 I created a SQL statement that did display what I wanted.

Below is SQL from access:

In (SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 )


Then in VB 6 I created an ADO control with a DB Grid bound to it.

Below is the SQL statement for the Recordsource property:

(SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 )

What happens is that the records are displyed in the DB grid, but it does not show all the records. Just the first of each duplicate.
 
My understanding is if the query is grouped by EmployeeSSNumber then the number will only appear once. In order for the EmployeeSSNumber to appear more than once then something else needs to be included in the query that can be grouped on to make the records unique.

The result you are getting is what I would expect
I have a database with an employee table. There are duplicate ss#'s in this table

I want to create a recorset that shows the duplicate records.

In Access 2000 I created a SQL statement that did display what I wanted.

Below is SQL from access:

In (SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 )


Then in VB 6 I created an ADO control with a DB Grid bound to it.

Below is the SQL statement for the Recordsource property:

(SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 )

What happens is that the records are displyed in the DB grid, but it does not show all the records. Just the first of each duplicate.
 
That is because you left off the first part of the SQL Try"

SELECT [EmployeeSSNumber]
FROM [REmployee]
WHERE [EmployeeSSNumber]
IN (SELECT [EmployeeSSNumber]
FROM [REmployee] As Tmp
GROUP BY [EmployeeSSNumber]
HAVING Count(*)>1)

HTH
Dale
I have a database with an employee table. There are duplicate ss#'s in this table

I want to create a recorset that shows the duplicate records.

In Access 2000 I created a SQL statement that did display what I wanted.

Below is SQL from access:

In (SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 )


Then in VB 6 I created an ADO control with a DB Grid bound to it.

Below is the SQL statement for the Recordsource property:

(SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 )

What happens is that the records are displyed in the DB grid, but it does not show all the records. Just the first of each duplicate.
 
Back
Top