none
Subtract value from previous value

    Question

  • Hello,

     I have a database setup for purchasing. I need help calculating the revision values.Please see the below example. Revision 1 and 2 includes the price change of Revision 0 item. I would like to run a query that shows the change occured for each revision.Please advise.

    Thanks,

    Lima

     

    PO Revision Item Price Rev Total
    1 0 Item1 50   50
    1 1 Item1 65 Increased by 15 65-50=15 How do I calcualte this in MS Access
    1 2 Item1 60 Decreased by 5 60-65=-5  

     

    
    
    Tuesday, January 25, 2011 7:01 PM

Answers

  • "Dirk Goldgar" <dg@NOdataSPAMgnostics.com.invalid> wrote in message
    news:c15998f1-d556-49f7-acad-9c7c9497c88c@communitybridge.codeplex.com...
    >
    > So then, for each item in a revision, you want to know the price
    > difference
    > from the most recent previous revision (of the same PO) in which that item
    > appeared.  Is that right?  And if the item didn't appear before at all,
    > the
    > price difference is the same as the current price.
     
    >
     
    If the above is right, how about this query:
    -- start of SQL --
    SELECT
       POItems.PO,
       POItems.Revision,
       POItems.Item,
       POItems.Price,
       [POItems].[Price]-Nz(
           (
           SELECT P.Price FROM POItems P
           WHERE P.PO = POItems.PO
                 AND P.Item = POItems.Item
                 AND P.Revision =
                     (
                     SELECT MAX(Q.Revision) FROM POItems Q
                     WHERE Q.PO = POItems.PO
                           AND Q.Item = POItems.Item
                           AND Q.Revision < POItems.Revision
                     )
           ),
           0) AS Rev
    FROM POItems
    ORDER BY POItems.PO, POItems.Revision, POItems.Item;
    -- end of SQL --
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by Bessie Zhao Monday, February 07, 2011 10:16 AM
    Wednesday, January 26, 2011 10:43 PM

All replies

  • "Pinacle1" wrote in message
    news:83e13d8d-c0af-4e75-bb20-24daaf853fe5@communitybridge.codeplex.com...
    > Hello,
    >
    > I have a database setup for purchasing. I need help calculating the
    > revision values.Please see the below example. Revision 1 and 2 includes
    > the price change of Revision 0 item. I would like to run a query that
    > shows the change occured for each revision.Please advise.
    >
    > Thanks,
    >
    > Lima
    >
    >
    >
    >      PO Revision Item Price  Rev Total
    >      1 0 Item1 50   50
    >      1 1 Item1 65 Increased by 15 65-50=15 How do I calcualte this in MS
    > Access
    >      1 2 Item1 60 Decreased by 5 60-65=-5
    >
     Something along these lines:
        SELECT
           POItems.PO,
           POItems.Revision,
           POItems.Item,
           POItems.Price,
           [POItems].[Price]-Nz([PrevRev].[Price],0) AS Rev
       FROM
           POItems
       LEFT JOIN POItems AS PrevRev
           ON  (POItems.PO = PrevRev.PO)
           AND (POItems.Item = PrevRev.Item)
           AND (POItems.Revision = PrevRev.Revision + 1);
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Tuesday, January 25, 2011 7:44 PM
  • "Dirk Goldgar" <dg@NOdataSPAMgnostics.com.invalid> wrote in message
    news:459aaa48-deac-4ed9-a428-e64c329cf9a9@communitybridge.codeplex.com...
    >
    > Something along these lines:
    >    SELECT
    >       POItems.PO,
    >       POItems.Revision,
    >       POItems.Item,
    >       POItems.Price,
    >       [POItems].[Price]-Nz([PrevRev].[Price],0) AS Rev
    >   FROM
    >       POItems
    >   LEFT JOIN POItems AS PrevRev
    >       ON  (POItems.PO = PrevRev.PO)
    >       AND (POItems.Item = PrevRev.Item)
    >       AND (POItems.Revision = PrevRev.Revision + 1);
    >
     It occurs to me that the above query, while it tracks changes in price for
    items that are included in the PO, doesn't generate a record flagging a
    revision if an item was dropped from the PO.  For example, if Item2 was
    present in revision 1 and not in revision 2, then the query generates no
    record for revision 2 showing the subtraction of that item.  If you want to
    see such a record, the query will need to be modified.
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by Pinacle1 Tuesday, January 25, 2011 8:38 PM
    • Unmarked as answer by Pinacle1 Tuesday, January 25, 2011 9:32 PM
    Tuesday, January 25, 2011 7:51 PM
  • Dirk,

     The answer you suggested is working just fine.When I have ann item purchased in Rev0 and not revised until rev3 (i.e not appeared on Rev1 and Rev2), the formula is not calculating the item value...it is working if i have the items appeared in the consecutive revisions.Thanks for your help.

     

    Tuesday, January 25, 2011 9:34 PM
  • "Pinacle1" wrote in message
    news:1ef701fb-9440-4988-88ee-c5faa81e8f7e@communitybridge.codeplex.com...
    > Dirk,
    >
    > The answer you suggested is working just fine.When I have ann item
    > purchased in Rev0 and not revised until rev3 (i.e not appeared on Rev1 and
    > Rev2), the formula is not calculating the item value...it is working if i
    > have the items appeared in the consecutive revisions.Thanks for your help.
    >
     
    It sounds to me like it isn't quite working the way you want, but to revise
    it I'll need to know exactly what sort of data you expect to have in the
    table, what the table represents, and what you want the query to return.  I
    had thought that for each PO, each revision would contain a record for every
    item in the PO.  It sounds like that is not the case.  Can you explain the
    whole setup in more detail?
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Tuesday, January 25, 2011 10:20 PM
  • Background:

    I have 2 tables, one is POLOG(this contains the purchase order header information like total PO No,PO Rev,PO value,vendor name etc) and the second table is PODETAILS(this contains the details of each PO entered in POLOG like item#,Descriptio, price of that revision)

    Issue:

    Like you said an item may or may not appear in all revisions. For example I bought a Black Pen from XYZ company and they sold it to me for $5 in Rev1 and on my next purchase i.e Rev2 I have not purchsed Black Pen I purchased Black Pen again on Rev3 this time they charged me $4.

    I am trying to find the price increase or decrease of Black pen from Rev1 to Rev3.(Given Balck pen is not purchased under Rev2 i.e it will not appear on Rev2).

    Hope this helps. If not please ask me questions I will clarify.

    Appreciate your help

    Wednesday, January 26, 2011 5:46 PM
  • Pinacle1,

    I can't help but wonder why you need this information - what value does it represent to you?

    If you are really looking for the change in value, then in your example for the balck pen and the 3 revs of the PO, would you not actually need to show something like this:

    Rev 1: Black Pen  200 @ $5.00
    Rev 2: Black Pen   0 @ $0 (-100%)
    Rev 3: Black pen   200 @ $4.00 (100%)

    ...to be technically correct?

    ...I'm not suggesting any changes to you, I'm just trying to understand your requirements and what value this represents as I have not seen this type of data before and am wondering about its usefulness/accuracy.


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)
    Wednesday, January 26, 2011 6:25 PM
  • "Pinacle1" wrote in message
    news:5f652532-b139-4aeb-8414-d35ddb3a0468@communitybridge.codeplex.com...
    > Background:
    >
    > I have 2 tables, one is POLOG(this contains the purchase order header
    > information like total PO No,PO Rev,PO value,vendor name etc) and the
    > second table is PODETAILS(this contains the details of each PO entered in
    > POLOG like item#,Descriptio, price of that revision)
    >
    > Issue:
    >
    > Like you said an item may or may not appear in all revisions. For example
    > I bought a Black Pen from XYZ company and they sold it to me for $5 in
    > Rev1 and on my next purchase i.e Rev2 I have not purchsed Black Pen I
    > purchased Black Pen again on Rev3 this time they charged me $4.
    >
    > I am trying to find the price increase or decrease of Black pen from Rev1
    > to Rev3.(Given Balck pen is not purchased under Rev2 i.e it will not
    > appear on Rev2).
    >
    > Hope this helps. If not please ask me questions I will clarify.
    >
     
    So then, for each item in a revision, you want to know the price difference
    from the most recent previous revision (of the same PO) in which that item
    appeared.  Is that right?  And if the item didn't appear before at all, the
    price difference is the same as the current price.
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Wednesday, January 26, 2011 10:21 PM
  • "Dirk Goldgar" <dg@NOdataSPAMgnostics.com.invalid> wrote in message
    news:c15998f1-d556-49f7-acad-9c7c9497c88c@communitybridge.codeplex.com...
    >
    > So then, for each item in a revision, you want to know the price
    > difference
    > from the most recent previous revision (of the same PO) in which that item
    > appeared.  Is that right?  And if the item didn't appear before at all,
    > the
    > price difference is the same as the current price.
     
    >
     
    If the above is right, how about this query:
    -- start of SQL --
    SELECT
       POItems.PO,
       POItems.Revision,
       POItems.Item,
       POItems.Price,
       [POItems].[Price]-Nz(
           (
           SELECT P.Price FROM POItems P
           WHERE P.PO = POItems.PO
                 AND P.Item = POItems.Item
                 AND P.Revision =
                     (
                     SELECT MAX(Q.Revision) FROM POItems Q
                     WHERE Q.PO = POItems.PO
                           AND Q.Item = POItems.Item
                           AND Q.Revision < POItems.Revision
                     )
           ),
           0) AS Rev
    FROM POItems
    ORDER BY POItems.PO, POItems.Revision, POItems.Item;
    -- end of SQL --
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by Bessie Zhao Monday, February 07, 2011 10:16 AM
    Wednesday, January 26, 2011 10:43 PM