[RESOLVED] How add a running total in a database

[cfd33]

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:

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.

[leinad31]

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.

[dee-u]

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

SELECT SUM(HoursWorked) FROM EVENT

[cfd33]

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 =

[leinad31]

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.

[cfd33]

leinad31 ,

again, I apologize for the lack of information.

When I get back tomorrow, then I will upload data.

[dee-u]

Have you noticed my post?

[cfd33]

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.

[cfd33]

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

[cfd33]

Anyone out there?

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

Thanks!

[techgnome]

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

[cfd33]

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.

[techgnome]

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

-tg

[cfd33]

Yes, this is what I am using:

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:

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

[dee-u]

Your sql should be something like this.

strSQL = "SELECT FDID, LastName, FirstName, MiddleInitial, Assignment, KellyDay, SUM(TotalHoursWorked) FROM table GROUP BY FDID, LastName, FirstName, MiddleInitial, Assignment, KellyDay"

[cfd33]

Thank you dee-u,

I will try that when I get back to work tomorrow.

[cfd33]

I have changed my sql statement to the following because I am drawing data from 2 different tables.

Here is the 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?

[techgnome]

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

rsWorked)," _

Should be

rsWorked)" _

-tg

[cfd33]

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:

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:

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

[techgnome]

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

[cfd33]

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.

[techgnome]

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

[cfd33]

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