Query Problem

[HedgeKAM]

Hi,

I have a Table that looks like this:
Transactions
-------------
SalesID
ClientID
FundID

Basically a client will do a transaction. The transaction has a salesperson and the transaction is in a certain fund.

What I want is for a given salesperson (SalesID) a list of that salespersons clients (ClientID) and then for each Client, a list of the funds the client has transactions in.

A client can have many transactions in a given fund, but I only want each fund listed once.

Any help is greatly appreciated!

 

[Loolaa]

Write a query based on your table grouping the three fields, I am assuming that there is a transaction id field in your table, just make sure that you miss this out of your query. If you want the actual description of the salesperson, client and fund then link into the query the tables that contain this information.

Hope this helps

 

[HedgeKAM]

thanks, ill give it a shot

 

[HedgeKAM]

Yepp, I got it to work using grouping

SELECT  ClientID, FundID, Sum(Shares) AS SumCount
FROM Transactions
WHERE SalesID = 44
GROUP BY ClientID, FundID;

I wanted to try to also include a JOIN in that SQL, but I cant get it to work.

SELECT  Transactions.ClientID, Funds.FundName, Sum(Shares) AS SumCount
FROM Transactions
JOIN Funds
ON Transactions.FundID = Funds.FundID
WHERE SalesID = 44
GROUP BY ClientID, FundID;

But I get an error saying "Funds.FundName is not apart of the aggregate"

Any Ideas?