Sum data from two tables

[Mustaphi]

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:

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:

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

[DataMiser]

Does it not support the sum() method?

[OptionBase1]

This works for me in Access. You'll have to adjust table and field names to fit your own.

SELECT (Table3.Cost + Table4.Tax) As Total FROM Table3
Inner Join Table4 On Table3.Id = Table4.Id

Good luck.

[Mustaphi]

This works for me in Access. You'll have to adjust table and field names to fit your own.

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?

[Mustaphi]

Does it not support the sum() method?

Yes the sum() method is supported.
But select Sum(tarif, recette) is sending error

[Arnoutdv]

The sum works on a single column

[Mustaphi]

The sum works on a single column

Thanks But do you think there is solution to my issue?

[Erwin69]

I think this will work:

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

[Mustaphi]

I think this will work:

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

[Erwin69]

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:

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

[OptionBase1]

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.

[Mustaphi]

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

[Mustaphi]

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?

[Erwin69]

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

[Mustaphi]

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.

[OptionBase1]

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.

[Erwin69]

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

[Mustaphi]

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

[Erwin69]

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

[Mustaphi]

sorry sir I get invalid attachment

[Mustaphi]

This is my code
Instead of printing the output, I'm sending it to Textbox.

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

[Erwin69]

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.

[Mustaphi]

I even made a loop

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

[Mustaphi]

This is what I get when I click on the attachment

Invalid Attachment specified. If you followed a valid link, please notify the administrator

[Mustaphi]

Sometimes I need to attach a pictre twice to get it displayed properly

[Erwin69]

This is my code
Instead of printing the output, I'm sending it to Textbox.

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.

[OptionBase1]

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?

[Mustaphi]

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

AS T2 ON T1.ID = T2.ID

from the query but it did not work.
I'll come back if I fail
thank you sir

[Erwin69]

In all fairness, this thread should be in SQL, not VB6.

[Mustaphi]

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

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

[Mustaphi]

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.

[Mustaphi]

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