Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query (Access 97)

    Hi

    A simple problem I hope. The end result is a query, which gives the total count compared to a selected total count for a field grouped by another field eg two fields A (organisation) and B (flag), grouped on A. Field B can either be null or #. The selected count is where field B = #. So the results should as follows:

    A TotalCount SelectedCount
    CompanyA 5 0
    CompanyB 3 1
    CompanyC 4 4

    Thanks
    WTH

  2. #2
    Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (Access 97)

    When you use the Count (or DCount) function by default it counts only those rows with non-null fields. To get a count regardless of the contents of a field, use an asterisk (*) in place of the field name. So your query could be something like:

    SELECT A, Count(*), Count([img]/forums/images/smilies/cool.gif[/img] FROM tablename GROUP BY A

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (Access 97)

    Thanks, but unfortunately I have given incorrect advice. The blanks in field B are not in fact null fields (ie they are zero length strings), so I get totals equalling each others by using the method suggested by you. Sorry about that.

    Regards
    WTH

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (Access 97)

    Okay. Try this instead:

    SELECT A, Count(*), Abs(Sum(B<>"")) FROM tablename GROUP BY A

    In this case, when B is not equal to a zero-length string True (-1) is returned. Summing them returns the total number of True's, but as a negative number. The Abs function returns the absolute (non-negative) value of the sum.

    Sorry it took me so long to respond. I've been really busy.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (Access 97)

    Hi

    Many thanks, it worked perfectly.

    Regards
    WTH

Posting Permissions

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