Click here to Skip to main content
14,453,230 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have an Employee table Access Database with columns 'Employee_ID','Basic Salary', 'HRA', 'TA&DA' and I want to sum these three columns "Basic Salary', 'HRA', 'TA&DA" and get the Total on a textbox.
The query I wrote only appends the values of these three Columns but doesn't sum it.
Please, is there something I didn't do right?
I'm a beginner in programming please.
Thanks.

What I have tried:

Private Sub CalculateGross()

        Dim gross As Double
        'Dim net As Double
        'Dim tax As Double

        If String.IsNullOrEmpty(txtSalaryID.Text) Then MsgBox("Sorry, cannot perform Query. String is Null.") : txtSalaryID.Focus() : Exit Sub

        EmpMain.AddParam("@SalID", txtSalaryID.Text)

        EmpMain.ExecQuery("SELECT SUM([Basic Salary]+ HRA + [TA&DA]) AS Total FROM Employee WHERE Employee_ID = @SalID ")

        If NoErrors(True) = False OrElse EmpMain.RecordCount < 1 Then txtSalaryID.Clear() : txtSalaryID.Focus() : Exit Sub

        EmpMain.DBcon.Open()
        EmpMain.Reader = EmpMain.DBcmd.ExecuteReader

        If EmpMain.Reader.Read() Then
            If EmpMain.Reader("Total") Then
                gross = EmpMain.Reader("Total")
            End If
        End If
        txtGrossPay.Text = gross

        EmpMain.DBcon.Close()

    End Sub
Posted
Updated 26-Sep-19 11:08am
v2
Comments
ZurdoDev 26-Sep-19 14:53pm
   
SUM(Field1) + SUM(Field2) ... no?

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

I believe you need to use this query,
-- Assuming
-- | EmployeeId | Name | JoinDate | ... | [Basic Salary] | HRA | [TA&DA] | ...
SELECT ([Basic Salary] + HRA + [TA&DA]) AS 'Total' 
FROM Employee 
WHERE Employee_ID = @SalID;
Application of an aggregate function (SUM() in this case) makes little sense, why would you spread the Basic Salary of an employee all over the table, and then group it? If you wanted to get a grouping of the values, then maybe SUM() part makes sense, but WHERE query is inappropriate for a group as you might want to apply something like, WHERE Basic Salary > 1000 etc.

Secondly, please name the tables in a better way—TA_DA would make more sense. Similar for Basic_Salary, avoid spacing, and special characters/names. You might know what you did, but the other DBA(s) would always be up for a challenge to break things. :-)

Check out this thread[^] to learn about other ways in which you can get the sum of columns for a record.
   
v4
Comments
Member 14587396 26-Sep-19 16:52pm
   
@Afzaal, thanks for the contribution but it doesn't still do what I want.
It still appends values for each column instead of summing them up.
This is what I mean;
Basic Salary =10, HRA=5,TA DA=5.
The sum should be 20 but it outputs 1055.
Why is it so?
Maciej Los 26-Sep-19 16:57pm
   
This means that you're concatenating strings. You need to convert strings into numbers.
Afzaal Ahmad Zeeshan 26-Sep-19 19:30pm
   
Change the data type of your column to a number type instead of a character type.
Maciej Los 26-Sep-19 16:58pm
   
5ed!
Afzaal Ahmad Zeeshan 26-Sep-19 19:30pm
   
Thank you, Maciej!
Member 14587396 27-Sep-19 4:48am
   
Yeah, y'all are correct. My query would've worked except for that silly mistake.
The datatype of my columns were text instead of numbers.
Thank you @Afzaal and @Maciej for the help.

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100