Adding data in one column together

newbieneedshelp


I'm working on my first database application and I am stuck on how to add all the data in one column and display that total with a label. I've searched the forum with no luck. The data in the column is currency.

Thanks for all the help

PEng1

You can use a simple querry with an aggregate function Like this

SELECT Sum(AmountsToBeToataled) as Total FROM AmountsTable

This will return a single amount equal to the sum of all values in the AmountsToBeTotaled column. Hope this helps.

newbieneedshelp

Thanks for your help in pointing in the right direction but I still have a few questions. Heres the code I have so far.

SELECT Sum(Winnings) as Total FROM AmountsTable
End Select

Questions
1. Is AmountsTable my table name or my tableadapter
2. Error, SUM and WINNINGS are not declared, how should I declare those
3. Error, end of statement expected.

Thanks again

anubisascends

The sql query, needs to be string value, basically, you need it to read something like this:

sQuery = "select sum(winnings) as total from AmountsTable"

If you don't assign it to a string variable or a command, then it will read the query as executeable code. Since the method Sum() has not been created and the variable winnings has not been created, you get errors.

Can you post a larger part of your code for us to look at

newbieneedshelp

Here is the code I have so far not included irrelevant stuff. Basically started back from the beginning. I've been reading tutorials and help files for 4 days straight and have tried countless times to get the sum of just 1 field in just 1 table:)

Table = "WagerAccess"
Field = "Winnings"
Red Code below = where I would like the query to take place



Public Class Form1
Private Sub WagerAccessBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WagerAccessBindingNavigatorSaveItem.Click
Me.Validate()
Me.WagerAccessBindingSource.EndEdit()
Me.WagerAccessTableAdapter.Update(Me.Database1_beDataSet1.WagerAccess)
End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'Database1_beDataSet1.WagerAccess' table. You can move, or remove it, as needed.
Me.WagerAccessTableAdapter.Fill(Me.Database1_beDataSet1.WagerAccess)

sQuery = "select sum(winnings) as total from AmountsTable"

End Sub
End Class

PS. I would like the sum of that to be displayed in LBL1.TEXT.
PSS. Any help or sugggestions are greatly appreciated

PEng1

From your code I take that you are using the DataSet Designer, if this is the case you need to do this:

1) In the DataSet Designer, right-click the AmountsTableTableAdapter and choose Add Query.
This will open the TableAdapter Query Configuration Wizard

2) Click Next

3) Choose Select which returns a single value

4) Inside the multiline Textbox type this

SELECT sum(winnings) as Total from AmountsTable

Click Next

5) Uncheck the Fill a Data Table and Check Return a DataTable

Type the Name of the Query e.g. GetWinnings

6) Click Finish

Where your red code is type this

lbl1.text = AmmountsTableTableAdapter1.GetWinnings()

This should do what you want if not it should give you a real good start

newbieneedshelp

Thanks for walkthrough!! Exactly what needed!!

PEng1

Not a problem, glad I could help.