Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    Crediton, Devon, England
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Correlated, aggregate subquery?? (2k)

    I'd appreciate any pointers on how to achieve to following (Access 2000 using ADO from Active Server Pages).

    Given a table X containing two fields (Person = Text, Amount = Integer number), where People is not unique, and Amount may be zero or a positive integer, I want to produce an output record set where each row has three fields

    Person
    Count(rows in X where amount for that person was zero)
    Count(rows in X where amount for this person was > zero)

    The amount of data to be processed is small, so less than elegant solutions are probably acceptable.

    Clearly creating any one of these pieces of information alone is relatively easy, but I don't see (yet) how or if the complete result can be obtained from an Access 2k query.

    Thanks for any ideas. - Mike -

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Correlated, aggregate subquery?? (2k)

    Have you worked with hierarchical recordsets using the SHAPE keyword? That sounds like what you're looking for. Here's an A2k demo that shows some of the things you can do with data and shaped recordsets. It was based on an article someone else wrote, but I've applied the principles in my own applications as well and it will at least give you an idea of the power of SHAPEd recordsets. It does require a reference to the ADO Multi-dimensional library.

    There was a thread on this recently in the VB/VBA forum, but I've lost track of the link and without the search function, it would take forever to find it again. I hope this demo will help instead.
    Attached Files Attached Files
    Charlotte

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

    Re: Correlated, aggregate subquery?? (2k)

    The only recent thread mentioning shaped recordsets I can recall is <post#=243090>post 243090</post#> in the Access forum...

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Correlated, aggregate subquery?? (2k)

    That's the one, Hans. I don't know why I remembered it as being in VB/VBA. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    Charlotte

  5. #5
    New Lounger
    Join Date
    Nov 2001
    Location
    Crediton, Devon, England
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Correlated, aggregate subquery?? (2k)

    Many thanks Charlotte, hansV. In the end I created a simple recordset and bashed it myself with code, rather than struggle with elegance/complexity in SQL. Anyway, problem resolved. Cheers - Mike -

Posting Permissions

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