Re: Second Recordset?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Rob (emailme_at_somewhere_else.com)
Date: 05/20/04 

Date: Thu, 20 May 2004 19:35:04 +1000

Tim,

Have you created the two queries in design view? You can then use the
QueryDef object to create a recordset?

Cheers
Rob

"Tim" <anonymous@discussions.microsoft.com> wrote in message
news:f90401c43e27$86833950$a501280a@phx.gbl...
> 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]
> >.
> > 

---
This email has been cetified virus free
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004

Relevant Pages

  • Re: Data Changing Issue
    ... In the query, on the field line, I added the above expression. ... *if* you are in VBA. ... the data of your table from a recordset. ... I am running some queries using a form I created and for some ...
    (microsoft.public.access.queries)
  • Re: How do I verify a valid sql statement programatically ??
    ... recordset, ASP, and javascript. ... the ASP session to minimize generating the query from the DB with different ... parts of the system that generated record lists would use the same list ... Many places in our system generate dynamic queries based on various ...
    (microsoft.public.sqlserver.programming)
  • RE: Need help regarding compare the results from two queries
    ... Are the two queries based on the same table, ... that query would then show you the result you needed. ... > I need compare the results of two queries, ... > Or use other methods(SQL query, or recordset) to achieve the goal. ...
    (microsoft.public.access.modulesdaovba)
  • Aggregates, Joins, I am totally stuck on this issue
    ... queries rather than looping through the recordset using VBA code and doing ... I have two aggregate queries which are grouped on ... query and then create an append query which puts the results of this master ...
    (microsoft.public.access.queries)
  • Re: Create a recordset based on another recordset
    ... Take a look at Querydef in the Help file. ... one or both of your queries, one or both of your crosstab queries or the ... recordsource query for your report. ... > I can create a recordset of each inital query, but is there a way to ...
    (comp.databases.ms-access)