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

By biztpine ·
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.

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
Thread display: Collapse - | Expand +

All Answers

Collapse -

Stop using MS Access

by Spitfire_Sysop In reply to How do I get detail from ...

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?

Collapse -

Are you using that damned UI thingy?

by Tony Hopkinson In reply to How do I get detail from ...

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.
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.

Back to Web Development Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums