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

 

Answer 1

"Pinacle1" wrote in message

news:[e-mail]...

> 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

Answer 2

"Dirk Goldgar" <[e-mail]> wrote in message

news:[e-mail]...

>

> 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

Answer 3

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.

 

Answer 4

"Pinacle1" wrote in message

news:[e-mail]...

> 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

Answer 5

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

Answer 6

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)

Answer 7

"Pinacle1" wrote in message

news:[e-mail]...

> 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

Answer 8

"Dirk Goldgar" <[e-mail]> wrote in message

news:[e-mail]...

>

> 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