Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    39

    Unanswered: Cumulative Column

    I have a query/table with 2 columns:
    WeekNr and Invoiced
    What I need is to create another query/table with 3 columns:
    WeekNr, Invoiced and CumulativeInvoiced.
    The first 2 columns are the same from the original table and the 3rd will have the Invoiced values but cumulated.
    For examle, if the 1st table has data like:
    WeekNr/Invoiced
    32/1200
    33/900
    34/3500

    The 2nd table must have:
    WeekNr/Invoiced/CumulativeInvoiced
    32/1200/1200
    33/900/2100
    34/3500/5600

    Thanks for your help!

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    How abt this

    Code:
    SELECT tblInvoiced.weeknr, tblInvoiced.Invoiced, DSum("[Invoiced]","tblInvoiced","[weeknr] <=" & [weeknr]) AS CumulativeInvoiced
    FROM tblInvoiced
    GROUP BY tblInvoiced.weeknr, tblInvoiced.Invoiced;
    i think this does what you want i have named my table tblInvoiced

    HTH
    Dave

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What application do you have for this data? If it's for reference only, this would be much better (and easier) to do in a report.
    oh yeah... documentation... I have heard of that.

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

  4. #4
    Join Date
    Nov 2004
    Posts
    39
    Actually, it is a query not a table that I'm using.
    Here is the code adapted as you said:

    SELECT
    reqWeekSalesByMonthByCountry.weeknr,
    reqWeekSalesByMonthByCountry.Invoiced,
    DSum("[Invoiced]","reqWeekSalesByMonthByCountry","[weeknr] <=" & [weeknr]) AS CumulativeInvoiced
    FROM reqWeekSalesByMonthByCountry
    GROUP BY
    reqWeekSalesByMonthByCountry.weeknr,
    reqWeekSalesByMonthByCountry.Invoiced;

    I have this code in another query but when I run it I have an error tag for all fields for the CumulativeInvoiced column.
    I cannot figer out what is happening.
    Also, on reqWeekSalesByMonthByCountry query I'm using 2 parameters: pMonth and pCountry.

  5. #5
    Join Date
    Nov 2004
    Posts
    39
    Teddy, you are right, I need this data only in a report.
    Supposing that I already have reqWeekSalesByMonthByCountry with data I told you, how can I add the cumulative invoiced directly into the report?

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Use a textbox with runningsum = yes


    I believe there are some good examples of running sum in the help files.
    oh yeah... documentation... I have heard of that.

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

  7. #7
    Join Date
    Nov 2004
    Posts
    39
    it works perfectly
    thx Teddy

  8. #8
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    This post is just for my curiosity as i know my query works
    as ive tested it a few different ways the only way i can reproduce your behaviour is to set the weeknr to text is this the case?

    if so this should work
    Code:
    SELECT [reqWeekSalesByMonthByCountry].[weeknr], [reqWeekSalesByMonthByCountry].[Invoiced], DSum("[Invoiced]","reqWeekSalesByMonthByCountry","CINT([weeknr]) <=" &CINT( [weeknr])) AS CumulativeInvoiced
    FROM reqWeekSalesByMonthByCountry
    GROUP BY [reqWeekSalesByMonthByCountry].[weeknr], [reqWeekSalesByMonthByCountry].[Invoiced];
    but again if it is a report then teddy's method is best
    but if you need to reproduce this behaviour for a form use the method above

Posting Permissions

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