Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using aggregate functions in a query (2000/SR-1)

    I'm confused (so what's new :-)!

    I have a primary table "Fred" in a one-to-many relationship with a related table "Alice." I need a query that shows fields F1 and F2 for each record in Fred regardless of whether there are related records in Alice. That's easy.

    I also need a column in that query that displays a number x, where x is the count of related records in Alice that meet certain criteria. For instance, let Alice contain fields boolFoo and boolBar. For each record in Fred I want the query to display the count of related records in Alice for which, say, (boolFoo And Not boolBar) is true.

    I'm stuck. How do I define the column that displays x?

    Thanks, as always, for your help!

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Using aggregate functions in a query (2000/SR-1)

    I got a little tangled up with Fred and Alice <img src=/S/grin.gif border=0 alt=grin width=15 height=15>, but I think what you want to do is to run a GroupBy query on Alice that among other things counts the number of records in Alice for each unique Fred-Pointer - to tet that turn on Totals under the Query menu after you start a new query on Alice. Then do the LeftJoin from Fred to that query rather than to the table Alice. Post back if this isn't clear - it doesn't feel that way.
    Wendell

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using aggregate functions in a query (2000/SR-1)

    Thank you!! Works like a champ; gives me exactly what I need.

Posting Permissions

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