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,
Davidselect 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|||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|||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