UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Price Increase Calculation On Same Field, Access 2016    
 
   
sging1
post Jan 1 2020, 04:46 PM
Post#1



Posts: 166
Joined: 29-June 05
From: Wales UK


Hi all

I have racked my brain over this one. I have a table with hundreds of items codes that in most cases have duplicates.

The item code has a unit price and a date when that item code was ordered. I am trying to show any item code that has increased inn price and by what amount or a percentage. I have got it to bring out any item that has had a price change but it shows me price reductions as well as increases.

Basically I cant work out how to just bring out just the item codes that has increased in price over the past two years years and then have a column to show me by how much so I can then set a criteria to then show anything over 10% for example between the orders in the past and now.

Its blown my mind this one so if anyone can help that would be great for the NHS as its to pinpoint price increases with suppliers.

Kind regards
Stephen Gingell
Go to the top of the page
 
theDBguy
post Jan 1 2020, 04:54 PM
Post#2


UA Moderator
Posts: 78,097
Joined: 19-June 07
From: SunnySandyEggo


Hi Stephen. I think in order to help you, we might need to see your data. Are you able to post a small copy of your db?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
sging1
post Jan 1 2020, 07:51 PM
Post#3



Posts: 166
Joined: 29-June 05
From: Wales UK


theDBguy, thank you very much for your help, I have added some data into a blank DB and uploaded.

Kind Regards
Stephen
Attached File(s)
Attached File  TestData.zip ( 192.04K )Number of downloads: 13
 
Go to the top of the page
 
Kaarlo Tuomi
post Jan 2 2020, 04:50 AM
Post#4



Posts: 62
Joined: 9-November 18



QUOTE
I have got it to bring out any item that has had a price change but it shows me price reductions as well as increases.


you could put a criteria on this field saying >0, that would show only price increases.

if you want to restrict your enquiry to the past two years, start with a criteria that says something like: year([date])>year(today())-2

and since you have already calculated a difference, to show that as a percentage the calculation is (difference/original price)*100



Kaarlo Tuomi



Go to the top of the page
 
Kaarlo Tuomi
post Jan 2 2020, 05:10 AM
Post#5



Posts: 62
Joined: 9-November 18



this is what I came up with:

SELECT data from the last two years...
CODE
SELECT MainData.Item, MainData.Date
FROM MainData
WHERE ((Year([date])>Year(Date())-2));


SELECT the oldest date in that period, and the most recent date in that period...
CODE
SELECT qry_change_00.Item, Min(qry_change_00.Date) AS olddate, Max(qry_change_00.Date) AS newdate
FROM qry_change_00
GROUP BY qry_change_00.Item
ORDER BY Min(qry_change_00.Date);


get the prices on those dates, and calculate the difference
CODE
SELECT qry_change_01.Item, MainData.[Unit Price] AS oldprice, MainData_1.[Unit Price] AS newprice, [newprice]-[oldprice] AS delta
FROM (qry_change_01 LEFT JOIN MainData ON (qry_change_01.olddate = MainData.Date) AND (qry_change_01.Item = MainData.Item)) LEFT JOIN MainData AS MainData_1 ON (qry_change_01.newdate = MainData_1.Date) AND (qry_change_01.Item = MainData_1.Item);


SELECT items where the price difference is greater than zero, and calculate that as a percentage of the oldest price.
CODE
SELECT qry_change_02.Item, qry_change_02.oldprice, qry_change_02.newprice, qry_change_02.delta, ([delta]/[oldprice])*100 AS variance
FROM qry_change_02
WHERE (((qry_change_02.delta)>0));




Kaarlo Tuomi
Go to the top of the page
 
sging1
post Jan 2 2020, 05:54 PM
Post#6



Posts: 166
Joined: 29-June 05
From: Wales UK


Thank you for your help and time, I will try this and come back to you. Much appreciated.

regards
Stephen
Go to the top of the page
 
sging1
post Jan 4 2020, 09:42 AM
Post#7



Posts: 166
Joined: 29-June 05
From: Wales UK


Hi Kaarlo Tuomi

Thank you very much again for all your hard work and its got me closer I have ever got myself. I have recreated the codes into my database but I am having only a slight 2 issues now and I have tried my best with the knowledge I have which is not the as good as your to make it right.

The first and second code seems to run like a dream as you intended. The third one and query qry_change_02 looks like it is not sorting all the prices as it should into the delta column. An example as you can see below item 00-2232-001-18 is showing a price difference in this two years but it not going across to the delta. I am getting 124 items in the qry_change_03_FINAL_RESULT but 00-2232-001-18 will not obviously show as we are excluding all >0 on the delta column which is strange as some are working and some are not. The only other issue is that some of the 124 are showing duplicate lines.

Item oldprice Newprice delta
00-2232-001-18 £198.00 £198.00 £0.00
00-2232-001-18 £68.40 £68.40 £0.00


If you don't mind helping once more I would really appreciate it, if it helps I have put all this together in an attached database and uploaded.

Other than its nearly perfect for what i am looking for.

Kind regards
Stephen Gingell


Attached File(s)
Attached File  IncreaseInCost.zip ( 946.2K )Number of downloads: 13
 
Go to the top of the page
 
Kaarlo Tuomi
post Jan 19 2020, 09:53 AM
Post#8



Posts: 62
Joined: 9-November 18



apologies for the delay in getting back to you.

QUOTE
An example as you can see below item 00-2232-001-18 is showing a price difference in this two years but it not going across to the delta.


your problem is that this item is NOT showing a price difference, and I'm not clear on why you think it is.

the item you posted, 00-2232-001-18 has only one entry in qry_change_00, with a date of 17 Oct 2019.

in qry_change_01 this item has one entry, in which olddate = newdate = 17/10/2019

in qry_change_02 this item has one entry, with oldprice = £68.40 and newprice = £68.40

in qry_change_03 this item has zero entries because the price difference is zero. as we would expect.

however, you posted,

QUOTE
Item oldprice Newprice delta
00-2232-001-18 £198.00 £198.00 £0.00
00-2232-001-18 £68.40 £68.40 £0.00


and this would appear to be a copy-paste error because the item with a newprice of £198.00 is 00-2232-004-18, a different item from 00-2232-001-18.

and, item 00-2232-004-18 clearly shows up in qry_change_03 as

Item oldprice newprice delta variance
00-2232-004-18 £68.40 £198.00 £129.60 189.47

so, I'm sorry, but I don't understand the problem you are having.



Kaarlo
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    26th May 2020 - 11:21 AM