Results 1 to 2 of 2

Thread: "Count" Problem

  1. #1
    Join Date
    Nov 2002
    Posts
    59

    Unanswered: "Count" Problem

    Hi folks...

    I have an Access database in such structure:

    SELECT VerticalMarket.VerticalName, CallRecord.CompanyVertID, Count(CallRecord.NextCallDate) AS CountOfNextCallDate
    FROM CallRecord INNER JOIN VerticalMarket ON CallRecord.CompanyVertID = VerticalMarket.VerticalID
    WHERE (((CallRecord.NextCallDate) Between [StartDate] And [EndDate]) AND ((CallRecord.Inhouse)=-1))
    GROUP BY VerticalMarket.VerticalName, CallRecord.CompanyVertID;

    When this one works as a query it's bringing me how many calls made between that "StartDate and EndDate" for each group of Vertical Market and the ones that Inhouse = -1


    VerticalName ----- CompanyVertID ------ CountOfNextCallDate
    --------------- ----------------- ---------------------
    AAAAAAAAA --------- AA --------- ------ 17
    BBBBBBBBBB --------- BB --------- ------ 25
    CCCCCCCCC --------- CC --------- ------ 13
    DDDDDDDDD --------- DD --------- ------ 15

    But I also wanna show other counts in same lines
    For example without considering what is inhouse whether -1 (Count2) or not and all datas belongs to particular VertID (Count3)

    so it'll be something like that
    VerticalName CompanyVertID CountOfNextCallDate Count2 Count3
    ------------- ----------------- -------------------- ---------- --------
    AAAAAAAAA AA 17 23 55
    BBBBBBBBBB BB 25 35 42
    CCCCCCCCC CC 13 17 29
    DDDDDDDDD DD 15 25 38

    And then I'll use it in my report as Record Source.

    So what basically that table says.
    - Total record number is 164 (Sum of Count3)
    - and 55 of them belongs to AA, 42 of them belongs to BB so on...
    - Count2 tells how many records AA has where inhouse = -1
    - CountofNextCallDate tells how many data in definite time as AA has 23 records between definite date.

    So 3 counts in 1 summary with different criterias. I hope it's clear enough.

    Thanks to everybody in advance.
    Last edited by cuneyt; 10-19-05 at 02:40.

  2. #2
    Join Date
    May 2005
    Posts
    119
    you could use =sum(iif([inhouse]=-1,1,0)) to get your inhouse counts for each grouping.
    Last edited by Krista327; 10-19-05 at 10:36.

Posting Permissions

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