Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    1

    Unanswered: Cumulating fields of a request

    Hi all,

    I'm building an Access database...

    I'made a request to obtain, in a culumn the date (sorted ascendingly) and in a second the amount spend in this day (the sum of recepies of the date).

    I'd like to maque a request on this to have all the date since the 1st of january and to cumulate the amount spent...

    Ex:

    My first request
    Date - Amount
    01/03/2004 - 20
    01/04/2004 - 10
    01/06/2004 - 50
    01/09/2004 - 40
    01/25/2004 - 15

    What I want to obtain
    Date - Amount
    01/01/2004 - 0
    01/02/2004 - 0
    01/03/2004 - 20
    01/04/2004 - 30
    01/05/2004 - 30
    01/06/2004 - 80
    01/07/2004 - 80
    01/08/2004 - 80
    01/09/2004 - 120
    01/10/2004 - 120
    01/11/2004 - 120
    01/12/2004 - 120
    01/13/2004 - 120
    01/14/2004 - 120
    01/15/2004 - 120
    01/16/2004 - 120
    01/16/2004 - 120
    01/18/2004 - 120
    01/19/2004 - 120
    01/20/2004 - 120
    01/21/2004 - 120
    01/22/2004 - 120
    01/23/2004 - 120
    01/24/2004 - 120
    01/25/2004 - 135


    Ideas???

    Many thanks!

    Bye

  2. #2
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Here is a snippit of code from an ASP page I have that shows a concept that works for me (for generating a calendar which needs to show every day of the month even though some days may not have any data). And it uses a table called tblNumbersForCalendar which just contains something like the numbers 1 through 366.

    ' Set sql - AA (subquery).
    strSQLAASelect = "SELECT tblNumbersForCalendar.CalendarNum, DateAdd('D', [CalendarNum] - 1, #" & CalendarDateMinFromDlg & "#) AS CalendarDate"
    strSQLAAFrom = "FROM tblNumbersForCalendar"
    strSQLAAWhere = "WHERE (1=1)"
    If CalendarDateMinFromDlg <> "" Then
    strSQLAAWhere = strSQLAAWhere & " AND (DateAdd('D', [CalendarNum] - 1, #" & CalendarDateMinFromDlg & "#)>=#" & jpsvbFixSQL(CalendarDateMinFromDlg) & "#)"
    End If
    If CalendarDateMaxFromDlg <> "" Then
    strSQLAAWhere = strSQLAAWhere & " AND (DateAdd('D', [CalendarNum] - 1, #" & CalendarDateMinFromDlg & "#)<=#" & jpsvbFixSQL(CalendarDateMaxFromDlg) & "#)"
    End If
    strSQLAA = strSQLAASelect & " " & strSQLAAFrom & " " & strSQLAAWhere
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    First off you need to create a table containing all the dates you wish to list in your report, that's actually going to be the bigger challenge. The guy above me has something to say about that.

    As far as the cumulative total is concerned, simply set the "running sum" property for the textbox to True on your report.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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