Question Locked

How do I get detail from one table with sum from another table in one query

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have two tables, a client table and a transactions table. All the different records on the transactions table have Amounts adding up to a balance total for the client table. I want to show the client table detail plus the sum of the transactions amounts (as the client balance). The ClientId is a foreign key on the transactions table.
My query needs to show a selected client's detail with the total.

I saw a solution for mySQL but cannot find one for ms access?

Please, I'm searching and searching and wasting time ....
Thank you.

3 replies

by spitfire_sysop

Eventually you will reach the point where Access simply doesn't work for you anymore. The sooner you develop an SQL solution the better.

That being said, Why would you need to do this in one query? Can't you gather the information in to an array using as many queries as you want? Then just output the array in to a tabular format?

by tony hopkinson

Create a query, put the clients and transactions tables on it. Stick the join on (line from clientid in one table to the other).
Make sure only amount is checked in the transactions table and set it to do the sum and perhaps alias the field name.

Don't ask me how to do any of that in access (whatever version you have), I've not a clue). I just know it's in there somewhere...

The sql should look something like
Select Clients.*, Sum(Transactions.Amount) as Total from Clients Inner join Transactions on Clients.ClientID = Transactions.ClientID Group By Clients.ClientID
It probably won't in access, square brackets everywere for starters.
If you want to learn sql, this place is a good start. http://www.w3schools.com/sql
but you'll never learn it with that GUI crap, it's for people who don't want to.
On the rare occasions I'm forced to access, (once my tantrum is over), I usually just go into SQL view and type the stuff in.
Not sure how much support there is in there in current access versions, you might be better off with SQL Server 2008 express as a learning tool, does syntax highlighting, type ahared and stuff.
If you switch from one to the other access will some some things that it won't (let you use form variables and such) and sql server does a heck of a lot that access can't even start on.
Hths and happy learning.

This discussion is closed to new replies.