Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sub Queries using VBA (2000 (9.0.4402 SR-1))

    Having a problem creating subqueries.

    I have a query which summaries Premiums by class of business and by unique policy reference and then I have a second query which extracts from the first the count of the number of unique polices by class of business along with the summary of the premiums. I do it this way, because the table the first run pulls from has multiple transactions for the same policy and to perform the count on the first run distorts the output. The second query

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Queries using VBA (2000 (9.0.4402 SR-1))

    See <post#=401925>post 401925</post#> for instructions on how to reduce the size of a database for posting.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Queries using VBA (2000 (9.0.4402 SR-1))

    Ah Ha!

    ZIpped db as attached.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Queries using VBA (2000 (9.0.4402 SR-1))

    It may be possible to do this with an inline subquery, but I didn't manage to make that work.

    I have attached a modified version that modifies a query in code, then runs the append SQL on this query. This is more efficient than using an intermediate table.
    The code uses DAO, I set a reference to the Microsoft DAO 3.6 Object Library in Tools | References...

  5. #5
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Queries using VBA (2000 (9.0.4402 SR-1))

    Many thanks, good stuff!

    Is there a way of using the ADO architecture to do this?

    Cheers,

    Niven <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Queries using VBA (2000 (9.0.4402 SR-1))

    In theory, it should be possible to use ADOX for this, but I when I tried it, I couldn't change the CommandText of the View object. Perhaps someone else knows how to do this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •