Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2014
    Posts
    2

    Unanswered: Adding up all columns linked to Foreign Key

    I have two tables.

    One table is called CaseDetails. The Other is Called Debts. They have a one to many relationship, CaseDetails can have more than one Debt linked to it.

    CaseDetails has a Primary Key of CaseID.
    Debts has a primary key of DebtID and a foreign key of CaseID.

    So for example I have CaseID 19 linked to DebtID 18, 19 and 20.

    The Debts table has a Column called DebtAmount. I want to do a sum of this column but only for each CaseID.

    I have the following Expression Written in the Expression Builder for a text box =Sum([Debts.CaseID]![DebtAmount])

    This is giving the grand total of DebtAmount but for every single CaseID not one at a time.

    Can someone please help?

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use a query or domain function

    eg =DSUM("DebtAmount", "Debts", "CaseID = " & mycaseid)
    if CaseID weas a string/text column
    =DSUM("DebtAmount", "Debts", "CaseID = '" & mycaseid & "'")
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2014
    Posts
    2
    Thanks

    I wrote some SQL in a Query.

    SELECT Sum(Debts.DebtAmount) AS SumOfDebtAmount
    FROM Debts
    WHERE Debts.CaseID=20;

    How could I implement this is a form so that the Debts.CaseID will equal the current case I'm scrolling through?

    Cheers

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could try:
    Code:
    Private Sub Form_Current()
    
        Const c_SQL As String = "SELECT Sum(Debts.DebtAmount) FROM Debts WHERE Debts.CaseID=@I;"
                                
        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        strSQL = Replace(c_SQL, "@I", Me.Text_CaseID.Value)
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        Me.Text_Sum.Value = rst.Fields(0)
        rst.Close
        Set rst = Nothing
    
        
    End Sub
    Where Text_Sum is the name of the control that displays the total and Text_CaseID the control that contains the primary key.
    Have a nice day!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use a domain function such as dsum, see post #2
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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