CODEWITHSUNDEEP
sqlms-access
harryg
harryg

Reputation: 24077

MS-Access - How to add records up when performing a query

OK so this is quite complicated but I'm very new to Access so maybe this isn't so hard.

I have 3 tables in my database:

Clients - this contains a list of all the clients for the fund. Some of thse clients have more than one account in the fund. Structure:

ID | CliName
------------
1  | Joe Blogs Holdings
2  | John Doe Investments
etc...

ClientMap - this basically has a list of all the accounts and another field contains which client it belongs to (this ID of the client who owns the account).

ID |     AccountName     |      ClientName      | ClientID (refs to ID in Clients)
------------
1  | Joe Blogs Safe Fund | Joe Blogs Holdings   | 1
2  | Joe Blogs Risk Fund | Joe Blogs Holdings   | 1
3  | John Doe Fund       | John Doe Investments | 2
etc...

Valuations - this is a list of the values of each account at any given date.

ID | ValuationDate |   Valuation  | AccName
------------
1  |  30/09/2012   |   1,469,524  | Joe Blogs Safe Fund
2  |  30/09/2012   |   1,767,134  | Joe Blogs Risk Fund
3  |  30/09/2012   |   239,561    | John Doe Fund
4  |  30/06/2012   |   1,367,167  | Joe Blogs Safe Fund
5  |  30/06/2012   |   1,637,121  | Joe Blogs Risk Fund
6  |  30/06/2012   |   219,241    | John Doe Fund

OK, so I want to run a query that list a chosen client from the clients table and returns the sum of the valuations for all their accounts. E.g. John Doe only has one accounts so the query result would look like this:

ClientName           |  ValuationDate | Valuation
----
John Doe Investments |   30/09/2012   | 239,561
John Doe Investments |   30/06/2012   | 219,241

BUT, the query should return something like this for Joe Blogs:

ClientName           |  ValuationDate | Valuation
----
Joe Blogs Holdings   |   30/09/2012   | 3,236,658
Joe Blogs Holdings   |   30/06/2012   | 3,004,288

So for each date it adds finds the total of all Joe Blogs' accounts to give an overall valuation. How do I do this?

Hope this is clear and those access experts out there are able to help me out. Thanks

I have this so far but it does not sum the accounts for each date, it just lists them individually:

SELECT ClientMap.AccName, Clients.ClientName, Valuations.ValuationDate, Valuations.Valuation
FROM (Clients INNER JOIN ClientMap ON Clients.ID = ClientMap.ClientID) INNER JOIN Valuations ON ClientMap.AccName = Valuations.AccName
WHERE (((Clients.ClientName) Like [Which Client?] & "*"));

Upvotes: 0

Views: 136

Answers (1)

Germann Arlington
Germann Arlington

Reputation: 3353

You should start by reading http://www.w3schools.com/sql/
When you are done you will know where and how to use aggregate functions [like sum() in this case]
Good luck in your learning process

Upvotes: 1

Related Questions