[RESOLVED] How add a running total in a database

Printable View

I hope the title is not too confusing. I did not know exactly how to word what I need.

In my program, when a person receives overtime, the number of hours is recorded and saved into the database. This part is fine.

So, for example, when John Doe works 6 hours of OT, then it is added to the database.

Now, when the person in charge of OT starts the program again, then John Doe should have 6 hours added to his total hours and it is displayed in the listview.

My problem is, is that I dont know how to do the second part.

Here is the code for adding the person info receiving OT hours:

vb Code:

Private Sub cmdSubmit_Click()
    Dim strSQL As String
    Dim blnTransOpen As Boolean
    Dim ConnectionLiving As Boolean
    
    
            'On Error GoTo ErrHandler
    
            blnTransOpen = False
            ConnectionLiving = False
    
            Call openConn
    
            ConnectionLiving = True
            dbconn.BeginTrans
            blnTransOpen = True
    
            dbconn.Execute "INSERT INTO Event(FDID,Event_Name,Event_Hours,Time_Of_Event,Date_Of_Event,Time_Called, " _
            & "Date_Called,HoursWorked,Left_Message) Values('" & txtFDID.Text & "', " _
            & " '" & txtVenue.Text & "','" & txtHoursNeeded.Text & "','" & txtVenueTime.Text & "','" & txtVenueDate.Text & "', " _
            & " '" & txtTimeCalled.Text & "','" & txtDate.Text & "','" & txtHoursWorked.Text & "','" & txtLeftMessage.Text & "')"
            
 
            
            dbconn.CommitTrans
            blnTransOpen = False
            'DBCon.Close
            ConnectionLiving = False
            
            
'            Call ClearTB
End Sub

For example: lets say John Doe currently has worked 25 hours of OT and now he has worked 6 more hours.

So, now his total should be 31 hours. So, how is this entered into the database?

I hope I am making sense.

Apr 12th, 2010, 12:32 AM
leinad31

Re: How add a running total in a database

As we are not familiar with your data architecture and we are not mind readers I'm afraid you will have to make your posts more informative.
Apr 12th, 2010, 02:21 AM
dee-u

Re: How add a running total in a database

If you want to sum of all the hours worked then it should be something like this.

Code:

SELECT SUM(HoursWorked) FROM EVENT
Apr 12th, 2010, 04:24 PM
cfd33

Re: How add a running total in a database

Quote:

As we are not familiar with your data architecture and we are not mind readers I'm afraid you will have to make your posts more informative.

Sorry about that.

Ok, I hope I can explain this, I have a form, that the person in charge of overtime uses, to keep track of personnels overtime.

When a person receives overtime, then the person in charge will enter the amount of overtime received into the form. Once the user clicks the "save" button, then I want the hours to be placed into 2 columns; 1 for the overtime hours the personnel just received and the other column for the running total of overtime hours.

The running total is needed to push the person with the most hours to the bottom of the overtime list.

I hope this helps to clear up what I am looking for.

The code in #1 post works fine and adds 99% of what I need, but I cannot figure out how add to the INSERT SQL above to total the hours and place it in the correct column.

Would I use something on the lines of : INSERT SUM(HoursWorked) FROM EVENT INTO ? WHERE FDID =
Apr 13th, 2010, 01:19 AM
leinad31

Re: How add a running total in a database

Quote:

Originally Posted by cfd33

Would I use something on the lines of : INSERT SUM(HoursWorked) FROM EVENT INTO ? WHERE FDID =

That was what I was referring to... we are not familiar with architecture (relevant tables and how they are related, primary keys, etc) nor the data involved (what are the values of FDID? you didn't provide sample data).

Corollary, the issue you cited in post #1 is listview data related but you didn't provide existing query used to fill listview. How are we to improve something we don't understand (again, we don't know the query, tables, etc involved as we can't see what you see on your monitor)?

dee-u's post is the most informative answer you'll get based on what you provided.
Apr 13th, 2010, 05:25 AM
cfd33

Re: How add a running total in a database

leinad31 ,

again, I apologize for the lack of information.

When I get back tomorrow, then I will upload data.
Apr 14th, 2010, 12:37 AM
dee-u

Re: How add a running total in a database

Have you noticed my post?
Apr 14th, 2010, 05:37 AM
cfd33

1 Attachment(s)

Re: How add a running total in a database

dee-u,

Yes, I have seen your post, but am not sure how to use it.

Would this be the way to use the code:

INSERT SUM(HoursWorked) FROM EVENT INTO TotalHoursWorked

I have now added the project.
Apr 14th, 2010, 10:19 AM
cfd33

Re: How add a running total in a database

I am hoping to accomplish the following things:

1. have a running total of hours column

2. a msgbox to show when a record was successfully enter
Apr 17th, 2010, 08:46 AM
cfd33

Re: How add a running total in a database

Anyone out there?

I still could use some help, if you have the time.

Thanks!
Apr 17th, 2010, 09:18 AM
techgnome

Re: How add a running total in a database

You shouldn't be storing a running total in the database. That's something that should be calculated on the go, as part of the query.

-tg
Apr 17th, 2010, 09:23 AM
cfd33

Re: How add a running total in a database

techgnome,

First, thanks for the response.

The reason I am trying to save the running count is so that when the program is opened, the listview will populate with the running total. So, that way the users can see who is up next for the overtime.

So, you are saying that I should use a sql query to populate the listview for the total overtime hours?

I am not sure how to do that.
Apr 17th, 2010, 10:06 AM
techgnome

Re: How add a running total in a database

Well, I'm assuming you have a query that you are currently using to populate the view, correct?

-tg

Re: How add a running total in a database

Yes, this is what I am using:

vb Code:

Set rs1 = New ADODB.Recordset
    rs1.CursorLocation = adUseClient
    strSQL = "SELECT * FROM 1Unit ORDER BY Hours ASC"
    rs1.Open strSQL, dbconn, adOpenDynamic, adLockOptimistic, adCmdText

So, should I use something like:

strSQL + strSQL = "SELECT SUM (TotalHoursWorked) FROM Event

and how do I get the results into my last column (Total Hours Worked) ? as the other columns data is being pulled from the database, where the total hours worked, will not?

Here is the code on load:

vb Code:

Set rs1 = New ADODB.Recordset
    rs1.CursorLocation = adUseClient
    strSQL = "SELECT * FROM 1Unit ORDER BY Hours ASC"
    rs1.Open strSQL, dbconn, adOpenDynamic, adLockOptimistic, adCmdText
    
   Screen.MousePointer = vbHourglass
    
    rs1.Requery
   
    If rs1.RecordCount = 0 Then
        msfg1U.Clear
        MsgBox "No record found"
        Screen.MousePointer = vbNormal
        Exit Sub
    
    ElseIf rs1.RecordCount >= 1 Then
 
        'populate flexgrid box
        With msfg1U
            .Clear
            .TextMatrix(0, 0) = "FDID"
            .TextMatrix(0, 1) = "LAST NAME"
            .TextMatrix(0, 2) = "FIRST NAME"
            .TextMatrix(0, 3) = "MIDDLE INTIAL"
            .TextMatrix(0, 4) = "ASSIGNMENT"
            .TextMatrix(0, 5) = "KELLY DAY"
            .TextMatrix(0, 6) = "TOTAL HOURS WORKED"
            .Rows = rs1.RecordCount + 1
            .Cols = rs1.Fields.Count - 1
            .Row = 1
            .Col = 0
            .RowSel = .Rows - 1
            .ColSel = .Cols - 1
            .Clip = UCase(rs1.GetString(adClipString, -1, Chr(9), Chr(13), vbNullString))
            .Row = 1
            .ColWidth(0) = 1200
            .ColWidth(1) = 2000
            .ColWidth(2) = 2000
            .ColWidth(3) = 2000
            .ColWidth(4) = 2000
            .ColWidth(5) = 2000
            .ColWidth(6) = 2300
            .ColAlignment(6) = flexAlignCenterCenter
            
         End With
    End If
    
    Screen.MousePointer = vbNormal

Apr 19th, 2010, 02:09 AM
dee-u

Re: How add a running total in a database

Your sql should be something like this.

Code:

strSQL = "SELECT FDID, LastName, FirstName, MiddleInitial, Assignment, KellyDay, SUM(TotalHoursWorked) FROM table GROUP BY FDID, LastName, FirstName, MiddleInitial, Assignment, KellyDay"
Apr 19th, 2010, 08:30 AM
cfd33

Re: How add a running total in a database

Thank you dee-u,

I will try that when I get back to work tomorrow.
Apr 20th, 2010, 10:35 AM
cfd33

Re: How add a running total in a database
I have changed my sql statement to the following because I am drawing data from 2 different tables.

Here is the code:
vb Code:
```
strSQL = "SELECT 1Unit.FDID,1Unit.Lname,1Unit.Fname,1Unit.M_initial,1Unit.Assignment,1Unit.Kday,SUM(Event.HoursWorked)," _
    & " AS SUMOFHoursWorked FROM 1Unit LEFT JOIN Event ON 1Unit.FDID = Event.FDID GROUP BY 1Unit.FDID,1Unit.Lname,1Unit.Fname,1Unit.M_initial," _
    & " 1Unit.Assignment,1Unit.Kday ORDER BY Hours ASC"
```
I am getting an error of : "Syntax error (missing operator) in query expression"

I think it has something to do with the part of the sql statement:

Event.Sum(HoursWorked)

Any ideas?
Apr 20th, 2010, 11:22 AM
techgnome

Re: How add a running total in a database

you have a comma after your SUM, but before you aliased the field....

Quote:

rsWorked)," _

Should be

Quote:

rsWorked)" _

-tg

Re: How add a running total in a database

Man, I should have caught that, I think you told me that one before (yes, I am a slow learner).

Anyways, now I have a "datatype mismatch error"

This is my sql statement now:

vb Code:

strSQL = "SELECT [1Unit].FDID,[1Unit].Lname,[1Unit].Fname,[1Unit].M_initial,[1Unit].Assignment,[1Unit].Kday,Sum(Event.HoursWorked) AS SumOfHoursWorked" _
    & " FROM 1Unit INNER JOIN Event ON [1Unit].FDID = Event.FDID" _
    & " GROUP BY [1Unit].FDID, [1Unit].Lname, [1Unit].Fname, [1Unit].M_initial, [1Unit].Assignment, [1Unit].Kday" _
    & " ORDER BY Sum(Event.HoursWorked)"

Ok, I found out why I was getting the mismatch error: one of the test data in my database did not have an entry in it.

But now I am getting an "Invalid Column Value" when the listview is trying to load.

What causes this?

This is the code for loading the listview:

vb Code:

Set rs1 = New ADODB.Recordset
    rs1.CursorLocation = adUseClient
    
    strSQL = "SELECT [1Unit].FDID,[1Unit].Lname,[1Unit].Fname,[1Unit].M_initial,[1Unit].Assignment,[1Unit].Kday,Sum(Event.HoursWorked) AS SumOfHoursWorked" _
    & " FROM 1Unit INNER JOIN Event ON [1Unit].FDID = Event.FDID" _
    & " GROUP BY [1Unit].FDID, [1Unit].Lname, [1Unit].Fname, [1Unit].M_initial, [1Unit].Assignment, [1Unit].Kday" _
    & " ORDER BY Sum(Event.HoursWorked)"
 
    
    Debug.Print strSQL
    
    rs1.Open strSQL, dbconn, adOpenDynamic, adLockOptimistic, adCmdText
    
   Screen.MousePointer = vbHourglass
    
    rs1.Requery
   
    If rs1.RecordCount = 0 Then
        msfg1U.Clear
        MsgBox "No record found"
        Screen.MousePointer = vbNormal
        Exit Sub
    
    ElseIf rs1.RecordCount >= 1 Then
 
        'populate flexgrid box
        With msfg1U
            .Clear
            .TextMatrix(0, 0) = "FDID"
            .TextMatrix(0, 1) = "LAST NAME"
            .TextMatrix(0, 2) = "FIRST NAME"
            .TextMatrix(0, 3) = "MIDDLE INTIAL"
            .TextMatrix(0, 4) = "ASSIGNMENT"
            .TextMatrix(0, 5) = "KELLY DAY"
            .TextMatrix(0, 6) = "TOTAL HOURS WORKED"
            .Rows = rs1.RecordCount + 1
            .Cols = rs1.Fields.Count - 1
            .Row = 1
            .Col = 0
            .RowSel = .Rows - 1
            .ColSel = .Cols - 1
            .Clip = UCase(rs1.GetString(adClipString, -1, Chr(9), Chr(13), vbNullString))
            .Row = 1
            .ColWidth(0) = 1200
            .ColWidth(1) = 2000
            .ColWidth(2) = 2000
            .ColWidth(3) = 2000
            .ColWidth(4) = 2000
            .ColWidth(5) = 2000
            '.ColWidth(6) = 2300
            '.ColAlignment(6) = flexAlignCenterCenter
            
         End With
    End If
    
    Screen.MousePointer = vbNormal

I get the error at the last column (which I have commented out). This is the column where the data from the Event table should be (the running total of hours).

Apr 20th, 2010, 12:27 PM
techgnome

Re: How add a running total in a database

I wouldn't worry about formatting the data or columns at this point.... just get the data loaded into the view first, make sure that that part is working.
Also... what IS "msfg1U"? Clearly a grid... but what kind of grid?

-tg
Apr 20th, 2010, 01:42 PM
cfd33

Re: How add a running total in a database

Quote:

what IS "msfg1U"?

That is what I use to abbrv. ms flexgrid.

Is there a different way to load a flexgrid than what I am using?

With the code above, I get everything from the first table (1Unit) to load into the flexgrid, but not the running total data from the second table (Event).

I am not sure how to get the running total data from the Events table into the last column of the flexgrid.
Apr 20th, 2010, 02:17 PM
techgnome

Re: How add a running total in a database

Dont need this:
rs1.Requery

After you open the recordset, try looping through the fields collection and print out the names of each field... make sure you are getting the fields you expect.

also break point at .Cols = rs1.Fields.Count - 1 and double check to make sure that rs1.fields.count does in fact equal 7.

-=tg
Apr 20th, 2010, 03:25 PM
cfd33

Re: How add a running total in a database

techgnome,

I appreciate your help. I am not a programmer, just a borrower of code. I try to find what applies to my little projects and then mod them to my needs.

I am not sure how to loop through the field collections and print out the names.

I got it!! I used Si's example and it works.

Thanks everyone!!!