Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    27

    Unanswered: why doesn't this SQL work?

    I am accessing our sales activity database and trying to get a list of all clients that a rep sees and the activity they have done against these targets.

    I have a table called Top50Targets which is a list of all the targets by rep. When I run the following SQL it does not list all targets in the Top50Targets table, only those that have had activity against them. Help!!!



    SELECT [top50 targets].TER_ID, [top50 targets].ACC_ID, [top50 targets].EU_ID, dbo_account.ACC_NAME, dbo_account.ACC_FIRSTNAME, Count(dbo_ACTIVITY.ACT_ID) AS Activity
    FROM ([top50 targets] LEFT JOIN dbo_ACTIVITY ON ([top50 targets].EU_ID = dbo_ACTIVITY.EU_ID) AND ([top50 targets].ACC_ID = dbo_ACTIVITY.ACC_ID)) LEFT JOIN dbo_account ON [top50 targets].ACC_ID = dbo_account.ACC_ID
    WHERE (((dbo_ACTIVITY.ACT_ACCOMPLISH)=1) AND ((dbo_ACTIVITY.CALL_TYPE)="1"))
    GROUP BY [top50 targets].TER_ID, [top50 targets].ACC_ID, [top50 targets].EU_ID, dbo_account.ACC_NAME, dbo_account.ACC_FIRSTNAME
    HAVING ((([top50 targets].TER_ID)="a209"));

  2. #2
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84

    Re: why doesn't this SQL work?

    Originally posted by bentley
    I am accessing our sales activity database and trying to get a list of all clients that a rep sees and the activity they have done against these targets.

    I have a table called Top50Targets which is a list of all the targets by rep. When I run the following SQL it does not list all targets in the Top50Targets table, only those that have had activity against them. Help!!!



    SELECT [top50 targets].TER_ID, [top50 targets].ACC_ID, [top50 targets].EU_ID, dbo_account.ACC_NAME, dbo_account.ACC_FIRSTNAME, Count(dbo_ACTIVITY.ACT_ID) AS Activity
    FROM ([top50 targets] LEFT JOIN dbo_ACTIVITY ON ([top50 targets].EU_ID = dbo_ACTIVITY.EU_ID) AND ([top50 targets].ACC_ID = dbo_ACTIVITY.ACC_ID)) LEFT JOIN dbo_account ON [top50 targets].ACC_ID = dbo_account.ACC_ID
    WHERE (((dbo_ACTIVITY.ACT_ACCOMPLISH)=1) AND ((dbo_ACTIVITY.CALL_TYPE)="1"))
    GROUP BY [top50 targets].TER_ID, [top50 targets].ACC_ID, [top50 targets].EU_ID, dbo_account.ACC_NAME, dbo_account.ACC_FIRSTNAME
    HAVING ((([top50 targets].TER_ID)="a209"));
    I'm assuming the table dbo_ACTIVITY has the activity records for targets. If there are no records for the target in your activity table, then even if you use the left join to the table your criteria will not allow the record to appear. You may want to try the NZ function on your activity table criteria fields so that NULL values (those targets that have no activities) are returned as something other than NULL and a value that doesn't match up with anything your are going ever use in that field. That way you can include it as criteria in your query using the OR operator.
    Regards,
    Terry

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Try changing the 'having' statement to a 'where' statement, i've had some trouble with 'group by' and 'having' in the same sql
    Last edited by m.timoney; 11-21-02 at 08:12.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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