none
Complex Pivot graphic programming.

    Question

  • Hello,

    I'm trying to build up a stacked column chart that shows how my store value has evolved in years.

    In order to understand what I am trying to do (failing right now) I'll explain quickly how my db works:

    -I have a table where all my data is filed. This table (MainTable) is processed by two queries that divide the table into Items that are in store(StoreItem) and Items that are sold (lets' call it SoldItem). 

    -The StoreItem query takes every record off MainTable with the exception of the ones that have a null or 0 value on the record Soldprice. On the other side, every item in the SoldItem has in the record Soldprice a value that is > 0.

    -Every record has some primary value like ID, Category, Cost, Soldprice, AcquiredDate, SoldDate. 

    Now lets get onto the problem:

    I want to see how my store value has evolved in years. For Example today my store value is 300. I want to see how it was in April 2010 (assume it was 280). In order to do so I'd have to look for every record that accomplies the following:

    -It has a Cost value in it's record. The sum of the Cost value in a selected period will in the end give me the Store Value.

    -The record item is not sold yet or wasn't sold in the period I wanted to investigate. In this case the SoldPrice has to be 0 or it's SoldDate must be posterior to the period I'm investigating (ex. Im looking for april 2010, and the item was sold later in May). 

    How could I build a graphic that shows me how my store value evolved each month? I assume I'd probably have to build a query to elaborate the data, but at this time, everything I thought didnt work or made access crash. 

    The graphic should look like:

    Data area: The total of each month StoreValue (storevalue is everyitem with soldprice=0 or that wasnt sold before the end of that month SoldDate<Month im looking it).

    Series area: The "category"  field suits fine.

    the X axis shows every month.

    Thanks for the help.

    Best regards.

    Monday, January 28, 2013 4:06 PM

All replies

  • Hi Filippo,

    Welcome to the MSDN forum.

    I am not sure about the situation. In the example you provided, you mentioned about a specific month in the past, however, you asked for a result during a period about several months. So, what do you want? Do you want to specify a start month and end month and get the record during them? Or do you just to get one particular month?

    If it is the latter, please refer to the following query:

    PARAMETERS TargetPeriod DateTime;
    SELECT tbMain.ID, tbMain.Category, tbMain.Cost, tbMain.AcquiredDate, tbMain.SoldDate, tbMain.SoldPrice
    FROM tbMain
    WHERE (((tbMain.AcquiredDate)<[TargetPeriod]) AND ((tbMain.SoldPrice)=0)) OR (((tbMain.AcquiredDate)<[TargetPeriod]) AND ((tbMain.SoldPrice)>0) AND ((DateDiff('m',[SoldDate],[TargetPeriod]))<0));

    Have a nice day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, January 30, 2013 10:22 AM
  • Hello Yoyo,

    Yeah I've been a bit clumsy on the explanation. I'm not trying to achieve none of the two things you asked me. I'm not really looking at a particular period or specific month. What I'm trying to make is a graphic chart that shows how my stock value has increased.

    On the Y axis I'd have the stock value in €, on the x the months since the db was opened. 

    The problem is to find a formula or to create a code to create the stock value per month. As a simple Sum of the cost in each month will end just giving me as result the sum of what I have spent that month. 

    Thursday, January 31, 2013 8:39 AM
  • Thanks for your reply.

    You said the stock value on the Y axis. Do you mean the sum of values of the all the stocks in the each month? 

    If this is a case, something occurs to me. Please see if the following way is a feasible solution for you:

    In Access 2010 and later, we can create a data macro in the table. So, besides the MainTable, we can just create a tbSum table to record the changes as time goes by.  No matter of the insert action, update action, or delete action( delete action depends on the situation we define), we just save the changes into this tbSum table with the time stamp as a new record. 

    Is it OK for you?

    Thanks.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, January 31, 2013 9:33 AM
  • Not really. Let's see if I had your explanation. You basically suggest me to add a macro that calculates on a new table the sum of the store value for each month? If that's right, yeah it could work. This way building the graphic isn't going to be a problem. 

    But still, as a newbie programmer, I don't know how I could make a macro that calculates automatically every month store total and auto-compilate the "tbSum". 

    Thanks for the time btw :)

    Friday, February 01, 2013 9:48 AM
  • >> You basically suggest me to add a macro that calculates on a new table the sum of the store value for each month?

    Yes.

    >> But still, as a newbie programmer, I don't know how I could make a macro that calculates automatically every month store total and auto-compilate the "tbSum". 

    So I assume that you got Access 2010 or later. I will try to write some data macros for this problem.

    I will let you know if there is any progress.

    Thanks.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, February 01, 2013 10:00 AM
  • Hi Filippo

    Please refer to the following steps:

    1. Create a table for stock information:

    <tfoot></tfoot>
    tbMain
    ID  Category Cost  SoldPrice  AcquiredDate  SoldDate
    1  1 10 0 1/1/2011
    2  2 20 10 1/3/2011 1/5/2011
    3  3 30 0 2/1/2011

    2. Create a table to track sum information:

    <tfoot></tfoot>
    tbSum
    ID MyTime MySum
    1 1/1/2011 10
    2 1/3/2011 30
    3 2/1/2011 60
    4 1/5/2011 50

    3. Create a named data macro for the tbSum to get the latest sum information:


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 04, 2013 10:52 AM
  • 4. In the tbMain's After Insert event:

    5. In the tbMain's After Update event:

    Please refer to the demo database: http://sdrv.ms/XiDz4l

    Tips: we can use Ctrl+C and Ctrl+V to copy and paste the data macro directly.

    Hope it helps.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 04, 2013 10:55 AM
  • Thanks for your time Yoyo.

    I'm actually trying to implement it on my database. Since it's quite bulky and complex it'll take me a couple of days. I'll give you an update here asap.

    Tuesday, February 05, 2013 6:03 PM
  • OK. Please feel free to let us know if you need any help on this issue.

    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 06, 2013 9:06 AM