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

Newbie Error

Hi guys,
Your help please.
First off, I have searched the Forum for answers using the keywords and tried those clues first but with no luck. This one for example: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q282254

I had a copy of MSDE installed on my machine about 12 month ago but never used it so I uninstalled it. Then I downloaded and installed SQL Server 120-day Evaluation edition a few days ago. When I tried to run SQL Server I get
An error 1069 – (The service did not start due to logon failure)…

I'm using Windows XP.

How can I find out what the password is or how can I reset it so the SQL Server logon works?

Any ideas?

Thanks Guys

FBIt's a windows service, so you'll need to go to your "Administrative Tools".|||what exactly do I need to do to get it to work?
I have gone to Control Panel > Administative Tools > Services > right click, select properties then moved to the Log On tab. Then what? ...|||Oh yeah, OK I've done it. On the Log On tab, in Log on as: I've selected Local System account, rather than This account.
Fanstastic! 8-)