Calculating difference of two values as a percentage

[Guest]

I am using Access 2000 and I need to know if there is a way to calculate the
difference between two numbers as a percentage.

My data base deals with price changes from one date to another. So if milk
was $2.00 on October 1st and then on October 16th it increased to $3.00 I
need to be able show the price increase or decrease in a percentage format.

It has been along time since I have done anything as far as building a data
base and I would appreciate any help I can get.

 

[Allen Browne]

Presumably you have a table that has fields like this to store the product
prices:
ProductID relates to tblProduct.ProductID
EffectiveDate Date/Time
PriceEach currency
And hopefully ProductID + EffectiveDate is your primary key, so there can't
be 2 prices for the same product on the same date.

If that's the idea, you can use a subquery to get the previous price for the
same product. You would type something like this into the Field row in query
design (all on one line):
(SELECT TOP 1 Dupe.PriceEach
FROM tblProductPrice AS Dupe
WHERE (Dupe.ProductID = tblProductPrice.ProductID)
AND (Dupe.EffectiveDate < tblProductPrice.EffectiveDate)
ORDER BY Dupe.EffectiveDate DESC, Dupe.ProductPriceID)

Once you have that working, you can subtract it from the current price to
get the difference.

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[Guest]

Thank you. I will try that. Thanks for including the link. I am sure I
will have to refresh my memory on subqueries.