[RESOLVED] SQL totals to a textbox

i have this peice of code to populate an MSHFlexGird with the data in a table that matches combo1 AND combo2.

VB Code:

adoRecordset.Open "SELECT clientid,mtype,amount,datereceived, SUM(amount) AS totals FROM history GROUP BY clientid,mtype,amount,datereceived HAVING mtype = '" & Combo2.Text & "' AND datereceived = '" & Combo1.Text & "'", adoConnection
 
Set MSHFlexGrid1.DataSource = adoRecordset

This code works perfectly ... it only returns rows that match BOTH mtype and datereceived;
My next goal is to take the total of all the amounts found in this search and put it into Text1.text. I am a bit confused as to how to tell VB that i want SUM(amount) to paste to the text box ... any suggestions ?

Maybe im going about this the wrong way:

I have been reading other posts to this forum about how to get the sumation of a column to a textbox but i cant get any of these solution to work for me.

here is whats going on (hopefully this picture will post correctly)

[IMG]C:\untitled.bmp[/IMG]

I have this table

i am showing the results to a query to an MSHFlexGrid, but would like to total a couple of things to text boxes.

so for example: if i search for ALL transacations on 9/5/2006 i will get a list of 1000 records. i want to take each different "Type" and total them into the correct textbox. this way i can still see ALL the records for that day, but i also get the totals for each "Type"

the code that im using to fill the flexgrid is as follows:

VB Code:

adoRecordset.Open "SELECT ID,type,amount,date, SUM(amount) AS totals" &_
"FROM history GROUP BY date,type,amount,id" &_
"HAVING date = '" & Combo1.Text & "'" &_
"ORDER BY date", adoConnection, adOpenKeyset, adLockReadOnly
Set MSHFlexGrid1.DataSource = adoRecordset

So what i need to end up with is 4 Textboxes (or labels, or whatever) that reflect the totals of each of the 4 different "Types" for that day; And then a 5th that shows the totals of the totals (or the daily total)

Im sure i can probably get this to work by running 4 or 5 different queries for each text box to fill, but id rather just run one query and end it !!

maybe some one could just push me into the right direction ??

Ok so the table didnt post ... what i have is:

ID DATE TYPE AMOUNT DESCRIPTION
1 6/5/2006 0:00 ACH* 111 Payment
2 6/5/2006 0:00 ACH* -187 Refund
2 7/5/2006 0:00 Cash -222 Refund
3 7/5/2006 0:00 Money Order 333 Payment
1 8/4/2006 0:00 ACH* 444 Payment
4 8/4/2006 0:00 Check* 555 Payment
4 9/5/2006 0:00 ACH* -111 Refund
7 9/5/2006 0:00 Check* -222 Refund
9 10/4/2006 0:00 ACH* 123 Payment
4 11/6/2006 0:00 ACH* 312 Payment


I have also tried to post the totals of a single TPYE to a text box using that SUM in the query as:

Text1.Text = adoRecordset!totals

but all i get is the first entry of that type ... no total at all ?

One thing that will cause issues is that you have a field called Date.

Date is a reserved word in just about everylanguage I've ever been exposed to. Either change that field name (the best solution) or put brackets around in it in your query.

Mr. Hack: sorry i just made it DATE so that i didnt have to type in the actual column name ... lol. its actually in the tables as datereceived.

I think you are going to have a look a little deeper into what you're trying to do.

For example these records will return two separate totals

ID DATE TYPE AMOUNT DESCRIPTION
1 6/5/2006 0:00 ACH* 111 Payment
2 6/5/2006 0:00 ACH* -187 Refund

However once you sort things out so that you only have one instance of each type in your recordset then one strategy to populate your text boxes could be this

VB Code:

Do 
   if Recordset("TYPE") = "ACH*" then
        Tex1.Text =  Recordset("totals")
   elseif Recordset("TYPE") = "cash" then
        Text2.Text =  Recordset("totals")
   elseif Recordset("TYPE") = "money order" then
        Text3.Text =  Recordset("totals")
   elseif Recordset("TYPE") = "check*" then
        Text4.Text =  Recordset("totals")
   else
       msgbox "Some unkown and bizarre crap just happened",0,"??????"
   end if
   Recordset.MoveNext
loop until RecordSet.EOF = True
 
Recordset.close
 
text5.text = ' add up contents of the 4 text boxes

Im confused as to the ElseIF statements. in almost any situation there will be at least one of every type. so if i have 10 total payments for one day broken up as follows:

ACH 1000
ACH -500
Check 500
Cash 100
Cash 400
ACH 100
Money 500
ACH -1000
ACH 200
Check 300

.. then i would want the text boxes to show as follows:

ACH -200
Check 800
Cash 500
Money... 500

Total 1600

Hmmm, that is why I suggested looking a little deeper.

If you want to summarize the four different types then you will have change your query or run a separate query. The query should look like this.

VB Code:

"SELECT type, SUM(amount) AS totals FROM history GROUP BY date, type HAVING date = '" & Combo1.Text & "' ORDER BY date"

This query will return the totals for each type.

The if ... elseif just allows you to capture each total to the correct text box.

If you don't wish to change the query then an alternate solution might be to loop through the recordset incrementing counters for each type. Something along these lines

VB Code:

ACH_count = 0
 ' initialize remaining counts
 
Do 
   if Recordset("TYPE") = "ACH*" then
        ACH_count =  ACH_count  + Recordset("totals")
   elseif Recordset("TYPE") = "cash" then
      Cash_count =  Cash_count  + Recordset("totals")
   elseif Recordset("TYPE") = "money order" then
        MoneyOrder_count =  MoneyOrder_count  + Recordset("totals")
   elseif Recordset("TYPE") = "check*" then
        Check_count =  Check_count  + Recordset("totals")
   else
       msgbox "Some unkown and bizarre crap just happened",0,"??????"
   end if
   Recordset.MoveNext
loop until RecordSet.EOF = True
 
Recordset.close
text1.text = format( ACH_count)
Text2.text = ' and so on
 
text5.text = ' add up contents of the 4 text boxes

this is the temporary code i put in its own form just to test ...

VB Code:

Private Sub Command1_Click()
Dim adoConnection As New ADODB.Connection
Dim adoRecordset As New ADODB.Recordset
Dim strSQLach As String
Dim strSQLcheck As String
Dim strSQLmo As String
Dim strSQLcash As String
 
strSQLach = "SELECT mtype, SUM(amount) AS totals FROM history GROUP BY datereceived, mtype" &_ 
"HAVING datereceived >= '" & Combo1.Text & "' AND datereceived <= '" & Combo3.Text & "' AND mtype = 'ACH' ORDER BY datereceived"
strSQLcheck = "SELECT mtype, SUM(amount) AS totals FROM history GROUP BY datereceived, mtype" &_  
"HAVING datereceived >= '" & Combo1.Text & "' AND datereceived <= '" & Combo3.Text & "' AND mtype = 'Check' ORDER BY datereceived"
strSQLmo = "SELECT mtype, SUM(amount) AS totals FROM history GROUP BY datereceived, mtype" &_  
"HAVING datereceived >= '" & Combo1.Text & "' AND datereceived <= '" & Combo3.Text & "' AND mtype = 'Money Order' ORDER BY datereceived"
strSQLcash = "SELECT mtype, SUM(amount) AS totals FROM history GROUP BY datereceived, mtype" &_  
"HAVING datereceived >= '" & Combo1.Text & "' AND datereceived <= '" & Combo3.Text & "' AND mtype = 'Cash' ORDER BY datereceived"
 
 
adoConnection.Open "Provider=SQLOLEDB; Data Source=192.168.0.200, 1433;" & _
    " Initial Catalog=some_db; User Id=sa; Password=some_pass"
 
adoRecordset.Open strSQLach, adoConnection, adOpenKeyset, adLockReadOnly
Label12.Caption = adoRecordset!totals
adoRecordset.Close
 
adoRecordset.Open strSQLcheck, adoConnection, adOpenKeyset, adLockReadOnly
Label13.Caption = adoRecordset!totals
adoRecordset.Close
 
adoRecordset.Open strSQLmo, adoConnection, adOpenKeyset, adLockReadOnly
Label14.Caption = adoRecordset!totals
adoRecordset.Close
 
adoRecordset.Open strSQLcash, adoConnection, adOpenKeyset, adLockReadOnly
Label15.Caption = adoRecordset!totals
adoRecordset.Close

when i do it this way the results are the same. the Label comes up with the first "total" in the ACH list. Example :

ACH 400
ACH 1000
ACH 123123
ACH 9578

ACH TOTAL = 400

Your approach to finding the totals is the way I would do it. My suggestion was based on the assumption that you only wanted to use one query.

I'm a bit confused though. Are you expecting the result to be ACH TOTAL = 400 or

ACH 400
ACH 1000
ACH 123123
ACH 9578

ACH TOTAL = 400 + 1000 + 123123 + 9578 = 134101

my GOAL is to have ACH = to the total of ALL ACH data for that time period.
so in your example:

ACH TOTAL = 400 + 1000 + 123123 + 9578 = 134101

and then once i can get that to work properly it will be

ACH 134101
Check 2043
MO 4435
Ect.

but for some reason when i run the code that i posted it only gives me the first cell in column "totals"

I suspect that reason it's doing that is because you are actually returning totals for each date in the query but only displaying the first record.

I will do some more digging and see what I can come up with.

It would be handy if you could a sample set of data that we can both work from. It seems that your samples are changing a bit with each post.

DATERECEIVED MTYPE AMOUNT
2006-01-05 00:00:00.000 Check* -190.00
2006-01-05 00:00:00.000 ACH* 870.33
2006-01-05 00:00:00.000 ACH* 416.00
2006-01-05 00:00:00.000 Check* -416.00
2006-01-05 00:00:00.000 ACH* 620.00


This is a peice from the DB that im doing the query on. I hope it posts ok ...

In this example there are only 2 different "mtype"s so lets just base it off of that. my final goal is to have a form with 2 textboxes (or labels) with
ACH TOTAL = 1906.33
CHECK TOTAL = -606.00

but the way that i am doing it, im getting:
ACH TOTAL = 870.33
CHECK TOTAL = -190.00


PS: i dont know why it keeps posting with the " * " after the Mtype, but its not really that way in my table.

I think I got it now.

Using your table from a previous post.....

ID DateReceived mType Amount Description
4 11/06/06 ACH 213 PAYMENT
9 10/04/06 ACH 123 PAYMENT
4 08/04/06 ACH -111 REFUND
2 06/05/06 ACH 187 REFUND
1 06/05/06 ACH 111 PAYMENT
2 07/05/06 cash -222 REFUND
7 09/05/06 CHECK -222 REFUND
1 08/04/06 CHECK 555 PAYMENT
3 07/05/06 money order 333 PAYMENT


Your ACH query will sum each common date ie 06/05/06

mType DateReceived Totals
ACH 06/05/06 298
ACH 08/04/06 -111
ACH 10/04/06 123
ACH 11/06/06 213

and Label12.Caption = adoRecordset!totals will show the first record.

So unless I'm way off course you can either go back and look at my posts for a solution or you could use your query to create a temporary table with the information as shown above and then query that table for a final sum.

SELECT mType, DateReceived, Sum(Amount) AS Totals INTO Temp FROM History GROUP BY mType, DateReceived HAVING mType="ACH" AND DateReceived >= '6/5/2006' And DateReceived <= '11/6/2006';

Followed by

SELECT Sum(Totals) AS FinalTotal FROM Temp;

Oops got ahead of myself, I will use your final table.

linXG, you are the man !!! thank you for walking me through this !!

you example worked perfectly ... the first time ... when i run it again i get an error about Temp already existing ... how would i go about getting rid of Temp after its run once ? that way the INTO statemtent will work if i run it twice ?!

Read two posts above cause I will be repeating myself here.

Using just the ACH data...

2006-01-05 00:00:00.000 ACH* 870.33
2006-01-05 00:00:00.000 ACH* 416.00
2006-01-05 00:00:00.000 ACH* 620.00

Your query will return a total of 1906.33 because your dates are all the same. If I change one date....

2006-01-05 00:00:00.000 ACH* 870.33
2006-01-05 00:00:00.000 ACH* 416.00
2006-01-06 00:00:00.000 ACH* 620.00

your query will return 1286.33 and 620.00 and your label will display the first value.

I hope this helps a bit.

LinXG, you are the man !!! thank you for walking me through this !!

you example worked perfectly ... the first time ... when i run it again i get an error about Temp already existing ... how would i go about getting rid of Temp after its run once ? that way the INTO statemtent will work if i run it twice ?!

The easiest way is to use DROP TABLE .....

DROP TABLE Temp;

You might want to create some test code to see what happens when you try to drop a non-existent Temp table and add the appropriate error handling code in case this situation arises.

LinXG .. again thanks so much for helping me out here !!