-
Jan 30th, 2021, 07:50 PM
#1
Thread Starter
Fanatic Member
Sum data from two tables
Hello Vbforums
I need to sum data from two tables.
This is the illustration
Table MTbl
ID...............Tarif
1 ................200
Table Act_tbl
ID....................Recette
1.......................300
I'm using sqlite3 and RC5
This is my attempt:
Code:
StrSql = "select tarif, recette from MTbl " & _
" inner join Act_tbl on MTbl.Id = Act_tbl.ID"
Set Rs = Cnn.OpenRecordset(StrSql)
Do While Not Rs.EOF
Debug.Print val(Rs!Recette) + val(Rs!tarif)
Rs.MoveNext
Loop
The output
200
200
500
Another attempt:
Code:
StrSql = "select tarif, recette from MTbl " & _
" inner join Act_tbl on MTbl.Id = Act_tbl.ID group by Act_tbl.ID"
Set Rs = Cnn.OpenRecordset(StrSql)
Do While Not Rs.EOF
Debug.Print val(Rs!Recette) + val(Rs!tarif)
Rs.MoveNext
Loop
The output
200
I want the output to be 500
Any help is much appreciated
-
Jan 30th, 2021, 09:09 PM
#2
Re: Sum data from two tables
Does it not support the sum() method?
-
Jan 30th, 2021, 09:23 PM
#3
Re: Sum data from two tables
This works for me in Access. You'll have to adjust table and field names to fit your own.
Code:
SELECT (Table3.Cost + Table4.Tax) As Total FROM Table3
Inner Join Table4 On Table3.Id = Table4.Id
Good luck.
-
Jan 31st, 2021, 03:46 AM
#4
Thread Starter
Fanatic Member
Re: Sum data from two tables
Originally Posted by OptionBase1
This works for me in Access. You'll have to adjust table and field names to fit your own.
Code:
SELECT (Table3.Cost + Table4.Tax) As Total FROM Table3
Inner Join Table4 On Table3.Id = Table4.Id
Good luck.
Thank you
this works.
the output is 500 as expected.
But regarding the following situation, the output is not as expected.
Table MTbl
ID...............Tarif
1 ................200
1.................50
Table Act_tbl
ID....................Recette
1.......................300
Using the code above, the output is as follows:
500
250
instead of
550
Any other idea please?
Last edited by Mustaphi; Jan 31st, 2021 at 03:49 AM.
-
Jan 31st, 2021, 03:51 AM
#5
Thread Starter
Fanatic Member
Re: Sum data from two tables
Originally Posted by DataMiser
Does it not support the sum() method?
Yes the sum() method is supported.
But select Sum(tarif, recette) is sending error
-
Jan 31st, 2021, 05:28 AM
#6
Re: Sum data from two tables
The sum works on a single column
-
Jan 31st, 2021, 05:38 AM
#7
Thread Starter
Fanatic Member
Re: Sum data from two tables
Originally Posted by Arnoutdv
The sum works on a single column
Thanks But do you think there is solution to my issue?
-
Jan 31st, 2021, 08:09 AM
#8
Addicted Member
Re: Sum data from two tables
I think this will work:
Code:
SELECT MTbl.Id, SUM(MTbl.Tarif) + SUM(Act_tbl.Recette) As Total
FROM MTbl INNER JOIN Act_tbl ON MTbl.Id = Act_tbl.Id
GROUP BY MTbl.Id
-
Jan 31st, 2021, 09:32 AM
#9
Thread Starter
Fanatic Member
Re: Sum data from two tables
Originally Posted by Erwin69
I think this will work:
Code:
SELECT MTbl.Id, SUM(MTbl.Tarif) + SUM(Act_tbl.Recette) As Total
FROM MTbl INNER JOIN Act_tbl ON MTbl.Id = Act_tbl.Id
GROUP BY MTbl.Id
Thank you
I tried this but the outcome is 750 instead of 550
-
Jan 31st, 2021, 09:51 AM
#10
Addicted Member
Re: Sum data from two tables
OK, the inner join messes things up since the same ID exists multiple times in MTbl. What you need to do, is to calculate the totals per ID per table, then do the join, and then calculate the grand total. The SQL statement will look like this:
Code:
SELECT T1.ID, MTblTotal + Act_tblTotal AS GrandTotal
FROM (
SELECT ID, SUM(Recette) As Act_tblTotal
FROM Act_tbl
GROUP BY ID) AS T1 INNER JOIN (
SELECT ID, SUM(Tarif) As MTblTotal
FROM MTbl
GROUP BY ID) AS T2 ON T1.ID = T2.ID
Edited to format the SQL statement better
-
Jan 31st, 2021, 11:12 AM
#11
Re: Sum data from two tables
Originally Posted by Mustaphi
Thank you
this works.
the output is 500 as expected.
But regarding the following situation, the output is not as expected.
Table MTbl
ID...............Tarif
1 ................200
1.................50
Table Act_tbl
ID....................Recette
1.......................300
Using the code above, the output is as follows:
500
250
instead of
550
Any other idea please?
That's why you should post the actual scenario in your initial post, and not some sort of simplified version of it that distorts your actual needs. Your initial post said nothing about values with the same ID in the same table.
Good luck.
-
Jan 31st, 2021, 11:33 AM
#12
Thread Starter
Fanatic Member
Re: Sum data from two tables
Code:
StrSql = "SELECT T1.ID, MTblTotal + Act_tblTotal AS GrandTotal " & _
" FROM ( SELECT SUM(Recette) As Act_tblTotal " & _
" From MTbl GROUP BY ID) AS T1 " & _
" INNER JOIN ( SELECT SUM(Tarif) As MTblTotal From Act_tbl " & _
" GROUP BY ID) AS T2 ON T1.ID = T2.ID"
Set Rs = Cnn.OpenRecordset(StrSql)
Thank you but I have the following error:
No such field T1.ID
-
Jan 31st, 2021, 11:38 AM
#13
Thread Starter
Fanatic Member
Re: Sum data from two tables
Originally Posted by OptionBase1
That's why you should post the actual scenario in your initial post, and not some sort of simplified version of it that distorts your actual needs. Your initial post said nothing about values with the same ID in the same table.
Good luck.
thank you for your interest
But this senario appeared later.
At the time I wrote my initial post, I had only one value in each table but later I figured out that there is a possibility for adding new values.
So you think there is no way to solve the issue?
-
Jan 31st, 2021, 03:06 PM
#14
Addicted Member
Re: Sum data from two tables
Originally Posted by Mustaphi
Code:
StrSql = "SELECT T1.ID, MTblTotal + Act_tblTotal AS GrandTotal " & _
" FROM ( SELECT SUM(Recette) As Act_tblTotal " & _
" From MTbl GROUP BY ID) AS T1 " & _
" INNER JOIN ( SELECT SUM(Tarif) As MTblTotal From Act_tbl " & _
" GROUP BY ID) AS T2 ON T1.ID = T2.ID"
Set Rs = Cnn.OpenRecordset(StrSql)
Thank you but I have the following error:
No such field T1.ID
You should use the full SQL statement as in the code I posted. Now you have left out parts of it, and as a result it no longer works...
-
Jan 31st, 2021, 03:36 PM
#15
Thread Starter
Fanatic Member
Re: Sum data from two tables
Originally Posted by Erwin69
You should use the full SQL statement as in the code I posted. Now you have left out parts of it, and as a result it no longer works...
I have copied your code and pasted it on the form.
I have just formatted it.
-
Jan 31st, 2021, 03:45 PM
#16
Re: Sum data from two tables
Originally Posted by Mustaphi
I have copied your code and pasted it on the form.
I have just formatted it.
No, you changed it. Erwin's subqueries include the ID field in the select statements, you removed those.
-
Jan 31st, 2021, 03:47 PM
#17
Addicted Member
Re: Sum data from two tables
Sorry, but your SQL statement is clearly different from the one that I posted:
SELECT T1.ID, MTblTotal + Act_tblTotal AS GrandTotal
FROM (
SELECT ID, SUM(Recette) As Act_tblTotal
FROM Act_tbl
GROUP BY ID) AS T1 INNER JOIN (
SELECT ID, SUM(Tarif) As MTblTotal
FROM MTbl
GROUP BY ID) AS T2 ON T1.ID = T2.ID
-
Jan 31st, 2021, 04:07 PM
#18
Thread Starter
Fanatic Member
Re: Sum data from two tables
I'm sorry sir
Now there is no error but the output is not correct
Perhaps I'm doing an error
Debug.print Act_tblTotal + MTblTotal
it is giving 1
thank you
Edit:
Debug.print Act_tblTotal + MTblTotal is giving 0
Last edited by Mustaphi; Jan 31st, 2021 at 04:15 PM.
-
Jan 31st, 2021, 04:15 PM
#19
Addicted Member
Re: Sum data from two tables
Mustaphi,
If you don't include code, we can't help you to find out what the problem is.
To make sure the info I gave you was correct, I went as far as setting up the two tables with the data as you descriped. See the below picture with three queries and the results. The SQL code I gave you does work fine.
Attachment 180059
Regards,
Erwin
-
Jan 31st, 2021, 04:20 PM
#20
Thread Starter
Fanatic Member
Re: Sum data from two tables
sorry sir I get invalid attachment
-
Jan 31st, 2021, 04:32 PM
#21
Thread Starter
Fanatic Member
Re: Sum data from two tables
This is my code
Instead of printing the output, I'm sending it to Textbox.
Code:
StrSql = "SELECT T1.ID, MTblTotal + Act_tblTotal AS GrandTotal " & _
" FROM ( SELECT ID, SUM(Recette) As Act_tblTotal " & _
" From MTbl GROUP BY ID) AS T1 " & _
" INNER JOIN ( SELECT ID, SUM(Tarif) As MTblTotal From Act_tbl " & _
" GROUP BY ID) AS T2 ON T1.ID = T2.ID"
Set Rs = Cnn.OpenRecordset(StrSql)
If Not Rs.EOF Or Not Rs.BOF Then
Debug.print Act_tblTotal + MTblTotal
end if
the output is 0
-
Jan 31st, 2021, 04:33 PM
#22
Addicted Member
Re: Sum data from two tables
No idea why sometimes it is displayed as an in-line picture, and sometimes as an attachment. When I click the attachment link, it displays the image.
Either way, the image shows that the query works. And since the first query with a sum worked, I can only guess that there is something wrong in your code. But without code, it is impossible to help.
-
Jan 31st, 2021, 04:36 PM
#23
Thread Starter
Fanatic Member
Re: Sum data from two tables
I even made a loop
Code:
StrSql = "SELECT T1.ID, MTblTotal + Act_tblTotal AS GrandTotal " & _
" FROM ( SELECT ID, SUM(Recette) As Act_tblTotal " & _
" From MTbl GROUP BY ID) AS T1 " & _
" INNER JOIN ( SELECT ID, SUM(Tarif) As MTblTotal From Act_tbl " & _
" GROUP BY ID) AS T2 ON T1.ID = T2.ID"
Set Rs = Cnn.OpenRecordset(StrSql)
If Not Rs.EOF Or Not Rs.BOF Then
Do While Not Rs.EOF
Text1.Text = Act_tblTotal + MTblTotal
Rs.MoveNext
Loop
End If
The output is always 0
-
Jan 31st, 2021, 04:38 PM
#24
Thread Starter
Fanatic Member
Re: Sum data from two tables
This is what I get when I click on the attachment
Invalid Attachment specified. If you followed a valid link, please notify the administrator
-
Jan 31st, 2021, 04:39 PM
#25
Thread Starter
Fanatic Member
Re: Sum data from two tables
Sometimes I need to attach a pictre twice to get it displayed properly
-
Jan 31st, 2021, 04:39 PM
#26
Addicted Member
Re: Sum data from two tables
Originally Posted by Mustaphi
This is my code
Instead of printing the output, I'm sending it to Textbox.
Code:
StrSql = "SELECT T1.ID, MTblTotal + Act_tblTotal AS GrandTotal " & _
" FROM ( SELECT ID, SUM(Recette) As Act_tblTotal " & _
" From MTbl GROUP BY ID) AS T1 " & _
" INNER JOIN ( SELECT ID, SUM(Tarif) As MTblTotal From Act_tbl " & _
" GROUP BY ID) AS T2 ON T1.ID = T2.ID"
Set Rs = Cnn.OpenRecordset(StrSql)
If Not Rs.EOF Or Not Rs.BOF Then
Debug.print Act_tblTotal + MTblTotal
end if
the output is 0
Sorry, but this is getting frustrating! I'm spending my free time trying to help you. It should not be too much to ask for you to pay attention, and properly copy and past the code samples I provide. Again the SQL statement is incorrect. And in your Debug,print you should refer to Act_tblTotal and MTblTotal as fields in the recordset, and not as variables.
-
Jan 31st, 2021, 04:40 PM
#27
Re: Sum data from two tables
Why would you think that those column names in the query are suddenly accessibly by name as VB variables?
You seemed capable of accessing values inside of a recordset in earlier code, and now you forgot how?
-
Jan 31st, 2021, 05:25 PM
#28
Thread Starter
Fanatic Member
Re: Sum data from two tables
I'm really sorry for my poor concentration
Finally It is working as I wished.
I thank you very much
And I apologize again
I will keep the post open until I try to sum the two columns regardless the ID.
So far I dropped this part from the query but it did not work.
I'll come back if I fail
thank you sir
-
Jan 31st, 2021, 05:30 PM
#29
Addicted Member
Re: Sum data from two tables
In all fairness, this thread should be in SQL, not VB6.
-
Jan 31st, 2021, 05:34 PM
#30
Thread Starter
Fanatic Member
Re: Sum data from two tables
I think I got my happiness.
I also dropped "Group by"
It is working but I need your confirmation to close the post.
thank you
-
Jan 31st, 2021, 05:42 PM
#31
Addicted Member
Re: Sum data from two tables
If you drop the ID fields in the selections, and the group by clauses, the query should calculate the total for each table. Not sure what you need my confirmation for. If you feel that your question has been answered, mark the post as resolved.
-
Jan 31st, 2021, 05:51 PM
#32
Thread Starter
Fanatic Member
Re: Sum data from two tables
Originally Posted by Erwin69
If you drop the ID fields in the selections, and the group by clauses, the query should calculate the total for each table. Not sure what you need my confirmation for. If you feel that your question has been answered, mark the post as resolved.
the query should calculate the total for both tables.
-
Jan 31st, 2021, 05:59 PM
#33
Thread Starter
Fanatic Member
Re: Sum data from two tables
Code:
StrSql = "SELECT T1.ID, MTblTotal + Act_tblTotal AS GrandTotal " & _
" FROM ( " & _
" SELECT ID, SUM(tarif) As Act_tblTotal " & _
" From Act_tbl )" & _
" AS T1 INNER JOIN ( " & _
" SELECT ID, SUM(Recette) As MTblTotal " & _
" From MTbl) AS T2 "
Set Rs = Cnn.OpenRecordset(StrSql)
Table1
ID
1......... 100
2.......... 200
Table2
ID
1 .......... 100
1...........300
3...........100
The result of the query is 800
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|