Sum value from table with reference from other table

Question

0.00/5 (No votes)

See more:

Hi all,

I have 2 table. tblCurrTrx and tblTransaction.
tblCurrTrx store the transaction data. tblTransaction store the Transaction ID, Description.

On tblCurrTrx also have column TransID. How to write sql statement to select the total amount in tblCurrTrx.dblAmount where the tblCurrTrx.TransID = tblTransaction.TransID?

On tblTransaction, I have 2 value only, that's is S01 and S02. So in tblCurrTrx, there is 10 transaction that have 4 S01 and 6 S02. And I want to run a query to select the sum amount of S01. Second query will get the sum of S02. Can anyone explain? I have made some query here. But it's failed.

VB

strSQl="SELECT tblCurrTrx, SUM (tblCurrTrx.dblAmount) WHERE " & _
tblCurrTrx.TransID=tblTransaction.TransID AND " & _
tblCurrTrx.CollectionNo='" & strCollectNo & "' GROUP BY tblTransaction.TransID"

My pal said I should use INNER JOIN but I have no idea how to write it. Please help

Posted 7-Dec-12 23:45pm

Luiey Ichigo

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2012-12-08T05:55:00

This should get you started ..http://msdn.microsoft.com/en-us/library/office/bb243855(v=office.12).aspx[^]

I would suggest trying it out in SQL first until you get it to work, and then convert it into your code ("strSql = ...etc") - you'll get more meaningful error messages that should help you spot what you're doing wrong. For example you don't have FROM clause in the example above