Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    DFW
    Posts
    2

    Angry Unanswered: Newbie Has Query Quandry

    This may be a simple one, but I'm not having any luck. Here is my scenario:

    two tables: tblCap, tblBU(just a lookup table with a list of business units)

    tblCap
    capID(primary key)
    tk_buIDPri
    tk_buIDSec
    tk_buIDTer
    Customer
    etc...

    tblBU
    buID(primary key)
    busUnit

    Essentially, the tk_buID's relate to the buID in tblBU. tblCap contains customer accounts and tblBU is a list of business units assigned responsibility to the accounts. What I'm trying to do, for reporting purposes, is get the total number of accounts each business unit has assigned to it, unfortunately for me, up to three bu's can share responsibility. I can get the figures for the tk_buIDs individually, but I can't seem to figure out how grab the figures for all three?

    Any help would be much appreciated!

    Cheers,
    David

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select buID, busUnit, count(*) as Assigned_Accounts
    from tblBU
    inner join tblCap on tblBU.buID = tblCap.tk_buIDPri or tblBU.buID = tblCap.tk_buIDSec or tblBU.buID = tblCap.tk_buIDTer
    group by buID, busUnit

    blindman

  3. #3
    Join Date
    Sep 2003
    Location
    DFW
    Posts
    2
    Originally posted by blindman
    select buID, busUnit, count(*) as Assigned_Accounts
    from tblBU
    inner join tblCap on tblBU.buID = tblCap.tk_buIDPri or tblBU.buID = tblCap.tk_buIDSec or tblBU.buID = tblCap.tk_buIDTer
    group by buID, busUnit

    blindman
    Blindman,

    Thank you very much! I'm appalled with myself over how easy it ended up being. Someone had led me to believe that I would need some sort of pivot table to accomplish what I was trying to do, so I got lost in that scenario. Thanks again!

    David

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'll warn you that it is not a very efficient query!

    And lot of people don't seem to realize that you can join tables on multiple clauses.

    blindman

Posting Permissions

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