Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2009
    Posts
    29

    Unanswered: Total of Values from Filter - SQL

    Hi,

    I’ve hit a problem with my database that I am creating.

    At this current stage I have a table called Matters, each Matter has a MatterID which is being used to link the Matter table and the Time Record table together.

    I have two buttons on the Matter form which run SQL code. One opens a time record form and inserts the currently viewed MatterID and FileNumber which the user then can add the necessary information for recording time. It works but if possible I would like to put a filter on so that it only shows Time Records for the same MatterID. Here is my code I have now

    Code:
    Private Sub TimeRecord_Click()
    
        Dim theFileNumber As String
        Dim theMatterID As Long
        Dim strSQL As String
    
        theFileNumber = Me.mFileNumber
        theMatterID = Me.MatterID
    
        strSQL = "INSERT INTO tblTimeRecord"
        strSQL = strSQL & "(tMatterID, tFileNUmber)"
        strSQL = strSQL & "VALUES ('" & theMatterID & "', '" & theFileNumber & "');"
            
        CurrentDb.Execute strSQL, dbFailOnError
        
        DoCmd.OpenForm "frmTimeRecord", acNormal
        DoCmd.GoToRecord acDataForm, "frmTimeRecord", acLast
        
    End Sub
    At the moment when the Time Record form open it is going to be showing all Time Recorded records. Is it possible to just show the ones with the same MatterID as the currently viewed matter using theMatterID variable?

    My second problem is… I want to take all the Time Recorded records for the currently viewed matter and total them together. Such as total travel time, total attendance time etc. Again I will need to filter using the MatterID (from tblTimeRecord) but not totally sure how I am going to do this.

    I have this code at the moment but I’m not sure if I am going the right way with this. If someone could point me in the right direction that would be great.

    Code:
    Private Sub CurrentCosts_Click()
    
        Dim fMatterID As Long
        Dim SQLStr As String
        Dim cnn1 As ADODB.Connection
        Set cnn1 = CurrentProject.Connection
        Dim myRecordSet As New ADODB.Recordset
        myRecordSet.ActiveConnection = cnn1
    
        SQLStr = "SELECT mAttendanceTime, mTravelTime"
        SQLStr = SQLStr & " FROM tblTimeRecord"
        SQLStr = SQLStr & " WHERE ([tMatterID] = fMatterID)"
        
        myRecordSet.Open SQLStr
        
    End Sub
    I would like to open a form named frmCurrentCosts which will show the total of times for the Matter which I can then do the calulations so I get get the current costs. Would the best way to do it be, create a temporary table, insert all the times from the filtered matter, total the times together and do calculations then delete temporary table?

    Thanks
    Andy.
    Last edited by andycambo; 08-13-09 at 07:36.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Open a second form to the record

    I would base the second form on a totals query that summed the fields you wanted and grouped on the Matter, and of course restricted to the specific Matter (you can use the technique above for that as well).
    Paul

  3. #3
    Join Date
    Jul 2009
    Posts
    29
    Thanks for the link. I now have the first problem solved(ish)

    I've used DoCmd.OpenForm "frmTimeRecord", , , "tMatterID = theMatterID"

    where theMatterID = Me.MatterID

    but I keep getting asked to enter the Matter ID rather than it taking it automatuically from the variable.

    Any ideas?

    Thanks
    Andy.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You didn't follow the link. The variable should be outside the quotes, so it's value can be evaluated.
    Paul

  5. #5
    Join Date
    Jul 2009
    Posts
    29
    DoCmd.OpenForm "frmTimeRecord", , , "tMatterID = " & theMatterID

    Good Call, working now. Cheers.

    This method will not work for my second problem. This is because the button is on frmMatters (which uses tblMatters) but the information I need is on a table called tblTimeRecord. So although this method works if I open the Time Record Form and do my calculations for that Matter on there, if I open a different form which has no table (as all I want to do is take the values from the Time Record table, filtered by the MatterID, and calculate the totals which do not need to be saved) it does not work as there is no table to filter.... all the information is on tblTimeRecord.

    Does that make any sense?

    Thanks
    Andy.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Like I said, I would base that form on a totals query that grouped on MatterID and summed the relevant fields (based on tblTimeRecord). Then when you open it using that technique it will display the totals for that MatterID.
    Paul

  7. #7
    Join Date
    Jul 2009
    Posts
    29
    Hi,

    Thanks for your advice. I've had a look around the internet and found some examples. I've manged to create a query that calculates the totals for each field.

    Now I would like to do this same but in SQL so that I can give selected variables a value which I can then use to populate a form.

    At the moment I have this code:

    Code:
    Private Sub CurrentCosts_Click()
    Dim totalTravelTime As Integer
    
    totalTravelTime = ("SELECT Sum(tblTimeRecord.tTravelTime) FROM tblTimeRecord WHERE tblTimeRecord.tMatterID = Me.MatterID;")
    
    DoCmd.OpenForm "frmCurrentCosts"
        
    End Sub
    Here I am tying to get the total sum of tTravelTime where the Time Records Matter ID (the FK - tMatterID) is the same as the currently viewed Matter (Me.MatterID). I then want to use the variables to populate the form frmCurrentCosts.

    I am fairly new to SQL language and I still unsure of the correct syntax to use. At the moment I am getting a type mis-match error (run-time 13).

    Any help on the above select statement will be much appreciated.

    Thanks,
    Andy.

  8. #8
    Join Date
    Jul 2009
    Posts
    29
    I've finally cracked it.

    totalTravelTime = DSum("[tTravelTime]", "tblTimeRecord", "tMatterID =" & theFileNumber)

    Now to figure how to get the results onto the form.

    Any suggestions?

    Thanks
    Andy.

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I think you're doing it the hard way, but you'd do something like:

    Forms!FormName.ControlName = totalTravelTime

    What I would do is base that form on a totals query:

    SELECT tMatterID, Sum(tTravelTime) AS TotalTime
    FROM tblTimeRecord
    GROUP BY tMatterID

    And then use the same wherecondition method to open the form to the specified matter.
    Paul

  10. #10
    Join Date
    Jul 2009
    Posts
    29
    Thanks for your advice, I've managed to find a solution to suit my needs and everything seems to functioning fine so thank-you.

    I'm just trying to calculate something though using an AND Statement but I keep getting an error. I think it might be the syntax I am using,

    totalTravelTime = DSum("[tTravelTime]", "tblTimeRecord", ("tMatterID =" & theFileNumber) And ("tFundingMethod =" & "M" Or "LM"))

    Is this right?

    Thanks
    Andy.

  11. #11
    Join Date
    Jul 2009
    Posts
    29
    Code:
    totalTravelTime = DSum("[tTravelTime]", "tblTimeRecord", "tFundingMethod = '" & "M" & "' AND tMatterID=" & theFileNumber)
    Figured it out, it was easier to get the syntax right by putting the text condition before the integer condition.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •