Re: ADO DBGrid Query Problem

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Newbie (noidea_at_nospam.com)
Date: 07/07/04 

Date: Wed, 7 Jul 2004 11:42:01 +0100

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
  "manf442" <manf442@bellsouth.net> wrote in message news:e6uaKh5YEHA.1048@tk2msftngp13.phx.gbl...
  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.

Relevant Pages

  • Re: Duplicate the record in form and subform
    ... Have checked the names in the SQL statement are correct, ... any change in the SQL statement if the PK value is string? ... Duplicate the main form record and related records in the ... 'Make sure there is a record to duplicate. ...
    (microsoft.public.access.formscoding)
  • Re: Duplicate Records That Contain Composite PK
    ... DBEngine.Execute strSql, dbFailOnError ... Here is another example of one that I have working (This is another subform ... Take a close look at the Insert sql statement. ... 'Make sure there is a record to duplicate. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Duplicate the record in form and subform
    ... Quotation marks within quotes ... Have checked the names in the SQL statement are correct, ... any change in the SQL statement if the PK value is string? ... 'Make sure there is a record to duplicate. ...
    (microsoft.public.access.formscoding)
  • Re: Duplicate the record in form and subform
    ... Quotation marks within quotes ... Allen Browne - Microsoft MVP. ... any change in the SQL statement if the PK value is string? ... 'Make sure there is a record to duplicate. ...
    (microsoft.public.access.formscoding)
  • Re: Duplicate Records That Contain Composite PK
    ... Take a close look at the Insert sql statement. ... Duplicate the main form record and related records in the ... Dim lngID As Long 'Primary key value of the new record. ... 'Make sure there is a record to duplicate. ...
    (microsoft.public.access.modulesdaovba)