Help with ADO Query

P

Pieter Wijnen

that's because it is what your asking for ;-)
try
select * from REmployee
WHERE EmployeeSSNumber
IN
(SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 )

HTH

Pieter
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.
 
G

GVaught

can you show what you have in VB 6 and then we can see where your statement is incorrectly syntaxed.

--
G Vaught

Can you give me the exact format for VB 6. I keep getting errors.

that's because it is what your asking for ;-)
try
select * from REmployee
WHERE EmployeeSSNumber
IN
(SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 )

HTH

Pieter
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.
 
M

manf442

Can you give me the exact format for VB 6. I keep getting errors.

that's because it is what your asking for ;-)
try
select * from REmployee
WHERE EmployeeSSNumber
IN
(SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 )

HTH

Pieter
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.
 
M

Microsoft Newsgroups

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.
 
Top