Results 1 to 8 of 8
  1. #1
    varadaradj is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2018
    Posts
    11

    Question Cumulative Total in query

    Dear Friends,
    I want to generate a query from the following type of database
    Bill No. Bill Date Customer I.D. Bill Amount
    1 01/09/2018 3092 2042
    2 01/09/2018 3392 125
    3 01/09/2018 CashBill 4458
    4 03/09/2018 211 949
    5 03/09/2018 932 447
    6 03/09/2018 442 412
    7 04/09/2018 301 885
    8 04/09/2018 130 44
    9 05/09/2018 CashBill 1258
    10 03/10/2018 33 199
    11 03/10/2018 99 482
    12 07/10/2018 399 124
    13 09/11/2018 76 447

    My requirements is Generating Monthly Total of Sales Details
    (i.e.)
    Month Monthly Sales Cumulative Sales
    September 2018 10620 10620
    October 2018 805 11425
    November 2018 447 11872



    I can generate a query for Monthly Sales figure with Month & Year, but I don't know how to write a query for Cumulative Total? Is it possible to generate a cumulative total as I shown above in a single query? Or is there any way to generate the result set as shown above?

    My requirement is not ended by just with this. After that, if I select any month I need Date-wise Sales Total with Cumulative Total for each Date also.

    Can anybody help me please.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you can do it in a report, set a field box property: RUNNING SUM = TRUE.
    but doing it in a query has bad issues.
    You CAN make a 'report' table, append the values, then update a running total by looping thru the table.

  3. #3
    varadaradj is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2018
    Posts
    11
    Sir,

    I want this query to display in VB6 Forms and not to show in a report.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    assuming your basic query is (called say Q1)

    Code:
    SELECT format(billdate,"yyyymm") as datecode, format(billdate,"mmmm") as billmonth, Year(billdate) as billyear, sum(billamount) as billamount
    FROM myTable
    GROUP BY  format(billdate,"yyyymm"), Month(billdate), Year(billdate)
    do another query based on Q1

    Code:
    SELECT Q1.billmonth, Q1.billyear, Q1.billamount, sum(Q1A.billamount) as CumSales
    FROM Q1 INNER JOIN Q1 AS Q1A ON Q1A.datecode<=Q1.datecode
    GROUP BY Q1.billmonth, Q1.billyear, Q1.billamount

  5. #5
    varadaradj is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2018
    Posts
    11
    Quote Originally Posted by Ajax View Post
    assuming your basic query is (called say Q1)

    Code:
    SELECT format(billdate,"yyyymm") as datecode, format(billdate,"mmmm") as billmonth, Year(billdate) as billyear, sum(billamount) as billamount
    FROM myTable
    GROUP BY  format(billdate,"yyyymm"), Month(billdate), Year(billdate)
    do another query based on Q1

    Code:
    SELECT Q1.billmonth, Q1.billyear, Q1.billamount, sum(Q1A.billamount) as CumSales
    FROM Q1 INNER JOIN Q1 AS Q1A ON Q1A.datecode<=Q1.datecode
    GROUP BY Q1.billmonth, Q1.billyear, Q1.billamount

    Sir,

    I want to use this query in a project where VB6 is front endand access 2007 as backend. How can I use the result of (say Q1) assource (Is it possible? Can we use result recordset as source for anotherquery? I never tried in this manner till date. If I can, can youplease tell me how can I do this?) for second query (Q1A).


  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you only need to replace the field and table names in my example with the ones you are using. VB6, although similar, is not the same as VBA but I don't see the relevance in your requirement, the solution is in SQL which will be the same with either language

    How can I use the result of (say Q1) assource
    I've used Q1 as a source to the second query.

  7. #7
    varadaradj is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2018
    Posts
    11
    Yes I Agree, I have to replace with respective field and table names.

    But, I want to avoid to convert the result set of Q1 to a new table, because I'm trying to run this query only to display purpose only. I didn't want to save the result table permanently. Instead I can save the result recordset to query till displaying in form after which I cant delete/drop the table. The only one drawback in this case is, even after dropping / deleting the table from my database, the space occupied is not freed immediately. To free the space, I've to Compact/Repair the database after which only I can free the space occupied by deleted table/records etc.

    I want to avoid this process because Access 2007 supports a maximum database size of 2 GB(Am I correct). As I'm in the process of converting the existing (DOS) foxpro application to access, I have to import 20 years data, which initially occupies near 700 to 800 MB. If I add more and more temporary tables (I explained only one requirement here, but I've to create more than 5 to 6 temporary database for atleast 3 to 5 forms, which are utilized more than twice / day) in database and dropping them again after usage, the database will become more bulky, forcing me to compact / repair database more often.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    But, I want to avoid to convert the result set of Q1 to a new table, because I'm trying to run this query only to display purpose only
    the queries I provided do not save to a table. Strongly suggest you try them before making assumptions.

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

Similar Threads

  1. CrossTab Query to Achieve a Cumulative Total
    By Alex_622 in forum Access
    Replies: 1
    Last Post: 10-07-2016, 07:35 AM
  2. Cumulative total error in query
    By maxmaggot in forum Queries
    Replies: 13
    Last Post: 06-22-2013, 07:51 PM
  3. Running Total (Cumulative)
    By jamesborne in forum Queries
    Replies: 3
    Last Post: 12-26-2011, 09:30 PM
  4. cumulative total
    By afshin in forum Queries
    Replies: 14
    Last Post: 08-11-2011, 01:42 AM
  5. Cumulative total in query
    By MikeWaring in forum Queries
    Replies: 2
    Last Post: 12-18-2010, 01:40 PM

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
  •  
Other Forums: Microsoft Office Forums