Re: Second Recordset?

From: Tim (anonymous_at_discussions.microsoft.com)
Date: 05/20/04 

Date: Wed, 19 May 2004 22:01:35 -0700

Marshall,

My problem can only be resolved by two queries. The
second query select the record from the first query. I
tried the query you stated but the output was different
from my expected.

Thanks anyway.

Tim.
>-----Original Message-----
>Tim wrote:
>>The following list all the info:
>>
>>Table3:
>>
>>Test ID Test Value
>>99 aa
>>99 aa
>>98 ab
>>98 ba
>>97 cc
>>96 aa
>>96 aa
>>96 cc
>>95 cc
>>95 cc
>>95 cc
>>1 c
>>1 a
>>
>>
>>Output:
>>
>>f1
>>1
>>96
>>98
>>
>>Module:
>>
>>Public Sub test()
>>Dim dbs As Database
>>Dim rst1 As Recordset
>>Dim rst2 As Recordset
>>
>>Set dbs = CurrentDb
>>Set rst1 = dbs.OpenRecordset("SELECT DISTINCT [Test
ID],
>>[Test Value] FROM Table3;")
>>
>>Set rst2 = dbs.OpenRecordset("SELECT " & rst1.Fields
>>("[Test ID]").Value & " as f1 FROM " & rst1 & _
>> " GROUP BY " &
>>rst1.Fields("[Test ID]").Value & _
>> " HAVING Count(" &
>>rst1.Fields("[Test ID]").Value & ") > 1;")
>>
>>Debug.Print rst2.Fields("f1").Value
>
>
>I'm not sure I understand what you want here, but I
>**think** you only need one query:
>
>SELECT [Test ID]
>FROM table3
>GROUP BY [Test ID], [Test Value]
>HAVING Count(*) > 1
>--
>Marsh
>MVP [MS Access]
>.
>

Relevant Pages

  • Re: access 2003
    ... Dim ctl As Control ... Dim rs As Recordset ... This sets the query definitions for choosing data to create an invoice using ... Event on combo box: Private Sub ChooseCust_AfterUpdate ...
    (microsoft.public.access.conversion)
  • Re: Query Problem in Test.
    ... You've declared rs as an ADO Recordset, yet you're trying to use DAO to ... Dim paramMiles As ADODB.Parameter ... query works in the query designer. ... MsgBox "No Churches meet your criteria.", vbInformation, ...
    (microsoft.public.access.formscoding)
  • Re: Need help with Code Please!!!
    ... putting the query SQL itself in here maybe it will help you understand what I ... the actual string that gets built at the end of the SQ1 build process. ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ... Dim qd As QueryDef 'object ref to query item ...
    (microsoft.public.access.formscoding)
  • Re: ADO Recordset.Filter in Excel 2002
    ... Tim ... Dim RS As ADODB.Recordset ... Dim oRS As ADODB.Recordset ... 'Populate the Recordset object with a SQL query ...
    (microsoft.public.excel.programming)
  • Re: Exporting data from continuous form to Excel spreadsheet
    ... I have overcome it by creating a Make Table Query. ... You can also use excel's copy from recordset method with a recordset. ... Dim sqlStatement As String ...
    (microsoft.public.access.modulesdaovba)