Results 1 to 14 of 14
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919

    Thumbs up Trying to get Jet to get the right summation

    The following query design:
    Click image for larger version. 

Name:	000.jpg 
Views:	22 
Size:	81.1 KB 
ID:	25977

    Gets me this:


    Click image for larger version. 

Name:	001.jpg 
Views:	22 
Size:	40.4 KB 
ID:	25978

    What I need is this:
    Building Fund 2200
    Employee Benefit 1132.84
    Pastor Salary 20
    Utilities 1300
    VM 10% Donatior 180

    Is there a simple change to the design that's needed or an entirely different approach?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I wouldn't think you need the DSum(). Have you tried a totals query, grouping on the other fields and summing the amount field?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Have you tried a totals query?

    Something like
    Code:
    SELECT tblCategories.CategoryName, Sum(tblRegister.Debit) AS SumOfDebit
    FROM tblCategories INNER JOIN tblRegister ON tblCategories.CatID = tblRegister.CatID
    GROUP BY tblCategories.CategoryName, tblRegister.CatID
    HAVING (((tblRegister.CatID)>0));

  4. #4
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    The SQL Select works great until I try to add some "date bracketing" of the tblRegister. If I can get that squared away, I'll build the SQL in the reports OnLoad where I can capture the user specifications for a periods beginning and ending dates, then set the RecordSource accordingly.

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Paul,
    I didn't pursue your exact thought in that ssanfu's SQL took care of the summing. I can't do any more on this until Saturday.
    Thanks for your thoughts,
    Bill

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe something like this? (this is very simplistic )
    Attached Files Attached Files

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Wow Daniel, it seems to me that you went to a lot of effort to solve this issue so thank you. I will integrate your query into my app with my existing names of the user's date prompting form and post back when that's accomplished.
    Bill

  8. #8
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Probably more than you wanted to know, but below is how your query was integrated into the current app. The period date bracketing form is common to several reports of which the first two are under development. The beginning and ending dates of the report periods are captured and saved in global variables, as opposed to accessing them from the form's controls as you did in your post, as they are used a few times elsewhere in the app when the form has been closed.

    Here is what the user sees:

    Click image for larger version. 

Name:	000.jpg 
Views:	16 
Size:	60.3 KB 
ID:	25994

    Here's the code behind the period bracketing form: (See "Sub Categories")
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Form_Open(Cancel As Integer)
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    ' Set the defaults for the beginning and ending of the report period.
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    
    Me.tbBegDate = DMin("tdate", "tblregister")        'Get the oldest date in the register as default starting
    curRptPerBeg = Me.tbBegDate
    
    Me.tbEndDate = Date                                'Set ending date to today
    curRptPerEnd = Me.tbEndDate
    
    End Sub
    
    Private Sub cmdOK_Click()
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    ' Setting period dates for which report?
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    
    Select Case Me.OpenArgs
        Case "Activity"
            Call Activity
        Case "Categories"
            Call Categories
    End Select
    
    End Sub
    
    Private Sub Activity()
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    ' Report of receipts and disbursements for the specified period.  Both are displayed as sub-reports, so we
    ' modify the RecordSource queries per the specified period.
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    Dim strQReceipts As String
    Dim strQExpences As String
    Dim strQLastReconcile As String
    
    strQReceipts = "SELECT TDate, Description, Credit FROM tblRegister WHERE ((([TDate])>=#" & curRptPerBeg & "# And ([TDate])<= #" & curRptPerEnd & _
                   "#) AND (([Credit])>0) AND (([TTypeID])<>3)) ORDER BY TDate;"
    Call ModifyQuery("QReceipts", strQReceipts)
    
    strQExpences = "SELECT TDate, Description, Debit FROM tblRegister WHERE (((TDate) >= #" & curRptPerBeg & "#)" & _
                    " And ((Debit) > 0) And (IsNumeric(TType)) ) ORDER BY TDate;"
    Call ModifyQuery("QExpences", strQExpences)
                   
    strQLastReconcile = "SELECT TDate, Description, Balance FROM tblRegister WHERE (TRecID=" & GetTranID & ");"
    Call ModifyQuery("QLastReconcile", strQLastReconcile)
    
    DoCmd.OpenReport "rptTresReport", acViewPreview
    
    End Sub
    
    Private Sub Categories()
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    ' Set the category report RecordSourc according to a beginning and ending date.  curRptPerBeg and curRptPerEnd
    ' are global, as they are used in report headers and also needed and saved elsewhere.
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    Dim strQCategories As String
    
    strQCategories = "SELECT CategoryName, Sum(Debit) AS SumOfDebit FROM tblCategories " & _
                     "INNER JOIN tblRegister ON tblCategories.CatID = tblRegister.CatID " & _
                     "WHERE (((TDate) >= #" & curRptPerBeg & "# And (TDate) <= #" & curRptPerEnd & "#)) GROUP BY CategoryName, tblRegister.CatID " & _
                     "HAVING (((tblRegister.CatID)>0));"
                                      
    Call ModifyQuery("QCatTotals", strQCategories)
    
    DoCmd.OpenReport "rptByCategory", acViewPreview
    
    End Sub
    Private Sub tbBegDate_Click()
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    ' To launch Allen Browne's pop-up calendar adjacent to the date control that's located
    ' in the footer section, we take the overall height minus the height of the footer
    ' minus half the height of the calendar as our "top of calendar" coordinate.
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    
    intCalTop = Me.Form.WindowTop + Me.cmdOk.top
    intCalLeft = Me.Form.WindowLeft + Me.cmdOk.left
    
    dummy = CalendarFor([tbBegDate], "Report Starting Date")
    
        If Not IsNull(Me.tbBegDate) Then
            Me.cmdOk.Visible = True
            Me.cmdOk.SetFocus
            curRptPerBeg = tbBegDate
        End If
        
    End Sub
    
    Private Sub tbPerEnd_Click()
    intCalTop = Me.Form.WindowTop + Me.cmdOk.top
    intCalLeft = Me.Form.WindowLeft + Me.cmdOk.left
    
    dummy = CalendarFor([tbPerEnd], "Report Period End Date")
    
        If Not IsNull(Mee) Then
            curRptPerEnd = tbPerEnd
        End If
    
    End Sub
    
    Public Sub ModifyQuery(strName As String, strNewSQL As String)
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    ' Modify the specified (strName) query that's being adjusted in accordance with the specified period.
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    
        Set dbs = CurrentDb
    
        'Modify the QueryDef's properties
        dbs.QueryDefs(strName).SQL = strNewSQL
    
        Set dbs = Nothing
    End Sub
    (You might notice I use Allen's calendar. That's because the front-end mdb/mde still needs updating in the A2003 environment.)

    And finally what the user sees in the report (under development):
    Click image for larger version. 

Name:	001.jpg 
Views:	17 
Size:	43.1 KB 
ID:	25995

    BTW, I seem to remember in years past that Steven Lebans wrote some code to prefix and append leader dots to text box controls strings. Was that facility or property ever advanced in subsequent releases of Access?

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Glad you were able to adapt the query to your needs.

    Quote Originally Posted by GraeagleBill View Post
    BTW, I seem to remember in years past that Steven Lebans wrote some code to prefix and append leader dots to text box controls strings. Was that facility or property ever advanced in subsequent releases of Access?
    Did you try it? Seems to work in A2010...
    The mdb converted to accdb and the reports had the leader dots.



    BTW, Who is Daniel??

  10. #10
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    I've just recently upgraded from A2003 to A2013 so don't even know how leader dots were implemented in A2010. I'll pursue the issue with our good friend Mr. Google.

    Daniel Bowles is the author of the database you sent me that had the summation query. I assumed ssanfu was Daniel in incognito.

    Thanks again. Your help made all the difference.
    Bill

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've just recently upgraded from A2003 to A2013 so don't even know how leader dots were implemented in A2010
    I just looked at Steven Lebans code for leader dots in reports.


    Daniel Bowles is the author of the database you sent me that had the summation query. I assumed ssanfu was Daniel in incognito.
    Nope, it was me. I've looked (apparently almost) everywhere in the demo and I cannot find the name "Daniel" anywhere. Where did you find the name??

  12. #12
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    I think his name appeared in the DB properties table. I'll look in the AM and post a screenshot when I find it.

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Whoops!! I found it.
    I've downloaded problem databases over the years and have set up databases the way I like. (I've never checked dB properties.) :O
    Then I use those databases to create test/demo databases.

    Thanks for pointing this out. I will be more vigilant in checking author names in DB properties..........

  14. #14
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Your method of offering help on this issue certainly helped me. SQL syntax has always given me fits when complex expressions come into play.
    Thanks again,
    Bill

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 12-23-2013, 02:30 PM
  2. summation with empty fields
    By danieltaylor in forum Access
    Replies: 3
    Last Post: 11-05-2012, 01:35 AM
  3. Summation of N Number of fields
    By deepanadhi in forum Queries
    Replies: 5
    Last Post: 06-07-2012, 10:48 PM
  4. Conditional Summation
    By megabrown in forum Queries
    Replies: 15
    Last Post: 12-08-2010, 06:19 PM
  5. Incorrect summation in report footer
    By wizard_chef in forum Reports
    Replies: 2
    Last Post: 12-07-2009, 06:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums