Results 1 to 7 of 7
  1. #1
    Shap is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    4

    Aggregated Sum

    Hi all!

    Basically trying to create a chart through a Query.

    Table is as follow:

    Applebrand Date Volume per week Category
    ------------ ------ -------------------- -----------

    Im trying to display the aggregated volume in a chart. The volumes can change depending on the demand from the buyers and its always the latest volume for the specific applebrand that is in play.

    Example

    Apple A 20150101 10 Retail
    Apple B 20150202 100 Restaurants
    Apple B 20150303 200 Retail
    Apple A 20150404 50 Retail

    The chart i would like to see is a aggregated volume for category "Retail" displaying a line from 10 to 210 to 250.

    Instead my chart displays 10 to 200 to 50

    Is there a way a solve this or should i use a different approach?



    Grateful for any help!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,339
    Why 210 and 250?

    You say 'aggregate' - do you really mean a running sum?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Shap is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    4
    Hi June7!
    Reason for 210 and 250 is that it can be any Apple kind, eg. Apple A,B,C. But it's always the latest volume for it per category.

    So for category Retail :

    Apple A is first registered at 10, then Apple B is registered for 200, then a new sale for Apple A comes in at 50 that replaces the 10. That's what I mean with the latest registered value. I hope that clarifies. Maybe that is called running sum. I'm not sure to be honest.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,339
    Sorry, still don't understand why combining A and B.

    Don't understand what you want to analyse.

    If you want to show changes over time, why is B 100 ignored? If you want only the latest then why would A 10 be retained?

    Not understanding rules.

    Running sum in query or form is not simple (it is in report because textbox has RunningSum property) - review https://support.microsoft.com/en-us/kb/290136
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Shap is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    4
    Sorry maybe I'm unclear.
    My goal is to create a line chart called 'Sales Development' for category "Retail" and for "Restaurants". So they both got individual lines. The x-axis is date.

    Within these categories they can purchase any kind of Apple hence A,B and C. Since B 100 belongs to another category (Restaurants) it's not included in my example. I just put it there to display that there r other categories as well in my table. So therefor my line for 'Retail' should go from 10 to 210 to 250.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,339
    Okay, sorry, missed the 'category' requirement.

    Still, comment about running sum holds. And your data calc is not a simple running sum either because the A 10 gets dropped. I am not seeing a query solution. I might do a VBA procedure using a 'temp' table. Temp table temporarily stores data for duration of a process.

    If you want to provide db for analysis and testing, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Shap is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    4
    Hi! I was afraid that it might require some VBA for it. I have attached the table for you, so if u have the time to figure out a solution that would be mighty nice of you.


    Database Shap for June7.zip


    Quote Originally Posted by June7 View Post
    Okay, sorry, missed the 'category' requirement.

    Still, comment about running sum holds. And your data calc is not a simple running sum either because the A 10 gets dropped. I am not seeing a query solution. I might do a VBA procedure using a 'temp' table. Temp table temporarily stores data for duration of a process.

    If you want to provide db for analysis and testing, follow instructions at bottom of my post.

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

Similar Threads

  1. Replies: 0
    Last Post: 04-14-2012, 07:36 PM

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