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

    Unanswered: SQL that doesn't work - HELP!

    I have a table called top50targets which has a list of all target customers for each of our sales people. I want to run a query that joins this table to the activity tables to give me a full list of targets and the activity against them. Only problem is that when I try to use left joins in Access it gets the brackets wrong and doesn't list customers that have had no activity. When I fix up the brackets in SQL I get a "error in join" message. Code is below, any ideas why it doesn't work?

    I am using Access2000

    thanks in advance
    bentley

    ***********************************************

    SELECT [top50 targets].TER_ID, dbo_account.ACC_NAME, dbo_account.ACC_FIRSTNAME, dbo_TBV_CALLTYPE.TBV_LABEL_GB, Count(dbo_TBV_CALLTYPE.TBV_LABEL_GB) AS CountOfTBV_LABEL_GB

    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_TBV_CALLTYPE ON (dbo_ACTIVITY.CALL_TYPE = dbo_TBV_CALLTYPE.TBV_CODE)

    LEFT JOIN dbo_account ON (dbo_ACTIVITY.ACC_ID = dbo_account.ACC_ID))

    WHERE ((([top50 targets].TER_ID)="A207") AND ((dbo_ACTIVITY.ACT_DATE)>=#7/1/2002# And (dbo_ACTIVITY.ACT_DATE)<=#9/30/2002#))

    GROUP BY [top50 targets].TER_ID, [top50 targets].ACC_ID, dbo_account.ACC_NAME, dbo_account.ACC_FIRSTNAME, dbo_TBV_CALLTYPE.TBV_LABEL_GB

    ORDER BY dbo_account.ACC_NAME, dbo_account.ACC_FIRSTNAME;

  2. #2
    Join Date
    Dec 2002
    Posts
    18
    Your where statement doesn't allow for no activities. Try this one:

    WHERE ((([top50 targets].TER_ID)="A207") AND ((dbo_ACTIVITY.ACT_DATE)>=#7/1/2002# And (dbo_ACTIVITY.ACT_DATE)<=#9/30/2002#) OR dbo_ACTIVITY.ACT_DATE Is Null )

    If you designed the query in Access and just copied the SQL here, add a new line or criteria and allow the activity.act_date to be Null

  3. #3
    Join Date
    Jul 2002
    Posts
    27

    using a different method now - with "views"

    Although Access and Views doens't work to well...

    I am going to create a table of the activities with the details of each customer first and then left join that new query to the top50list. Therefore only 1 left join instead of a left join on a left join.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is no problem with left joins upon left joins

    you may not need to define a "view" (query to query)

    what access requires is that you parenthesize the entire FROM clause joins, not just the ON parts...

    here is your original query, with the proper nesting parentheses --
    Code:
    select [top50 targets].TER_ID
         , dbo_account.ACC_NAME
         , dbo_account.ACC_FIRSTNAME
         , dbo_TBV_CALLTYPE.TBV_LABEL_GB
         , Count(dbo_TBV_CALLTYPE.TBV_LABEL_GB) 
            AS CountOfTBV_LABEL_GB
      from (( [top50 targets] 
    left outer
      join dbo_ACTIVITY 
        on [top50 targets].EU_ID 
         = dbo_ACTIVITY.EU_ID
       and [top50 targets].ACC_ID 
         = dbo_ACTIVITY.ACC_ID )
    left outer
      join dbo_TBV_CALLTYPE 
        on dbo_ACTIVITY.CALL_TYPE
         = dbo_TBV_CALLTYPE.TBV_CODE )
    left outer
      join dbo_account 
        on dbo_ACTIVITY.ACC_ID 
         = dbo_account.ACC_ID
     where [top50 targets].TER_ID) = 'A207'
       and dbo_ACTIVITY.ACT_DATE >= #7/1/2002# 
       and dbo_ACTIVITY.ACT_DATE <= #9/30/2002#
    group
        by [top50 targets].TER_ID
         , [top50 targets].ACC_ID
         , dbo_account.ACC_NAME
         , dbo_account.ACC_FIRSTNAME
         , dbo_TBV_CALLTYPE.TBV_LABEL_GB
    order
        by dbo_account.ACC_NAME
         , dbo_account.ACC_FIRSTNAME

    rudy
    http://rudy.ca/

Posting Permissions

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