get sale value for the previous day

[illusionek]

Hello

Can please someone point me in the right direction how to solve below problem?

I have below table in my database where I have different products, sale date and sale value

Product Name| Sale Date| Sale Value
Product 1| 10/05/16| 100
Product 2| 10/05/16| 150
Product 1| 11/05/16| 125
Product 2| 11/05/16| 135


I am trying now to convert above table into below where I have an additional column stating Previous Day Sale.

Any idea how to do it? I was playing with DLookUp but then I get value of first record in the entire table across regardless of date or product name. I also tried DSum but it did not work neither :banghead:

Product Name| Sale Date| Sale Value| Previous Day Sale
Product 1| 10/05/16| 100|
Product 2| 10/05/16| 150|
Product 1| 11/05/16| 125| 100
Product 2| 11/05/16| 135| 150

 

[pbaldy]

See if Allen's method works for you:

http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[jdraw]

How exactly will you use the info?

You could enter a SaleDate and a Product;
calculate he PreviousSaleDate as SaleDate-1;
then use that Date and your Product to see if a record(s) exists;
if it exists do the calculation
if it doesn't exists , you know there was no Sale of your Product on that Date.

Doesn't require storage in a new field. It is considered poor practice generally to store a calculation.
Do the query or queries when the info is required.
Good luck.

 

[illusionek]

See if Allen's method works for you:

http://allenbrowne.com/subquery-01.html#AnotherRecord

It appears to be what I need but for some reason, it does not work when I apply it to my query. I attached a screenshot of my query and in second row I would expect to see 5104 in the last column.

I use below query, would you be able to advise where I go wrong?

SELECT Query1.Product, Query1.WeekCommencing, Query1.Sale, (Select TOP 1 Query1.Sale from Query1 as q1v2 where q1v2.Product = Query1.Product and q1v2.WeekCommencing = Query1.WeekCommencing - 7 Order By q1v2.WeekCommencing, q1v2.Product) AS PreviousSale
FROM Query1;

 

[illusionek]

How exactly will you use the info?

You could enter a SaleDate and a Product;
calculate he PreviousSaleDate as SaleDate-1;
then use that Date and your Product to see if a record(s) exists;
if it exists do the calculation
if it doesn't exists , you know there was no Sale of your Product on that Date.

Doesn't require storage in a new field. It is considered poor practice generally to store a calculation.
Do the query or queries when the info is required.
Good luck.

Would you be able to share a link or point me in the right direction where I can read more about good practice when it comes to Access. My background is not in databases and the only rule I really apply is to use normalized tables but I do not really know whats the good practice when it comes to calculations, expressions, queries etc in Access.

 

[jdraw]

Do not store calculations.
As Paul said the Allen Browne link is one way to go

SELECT 
prodsale.productName,
prodsale.saleDate,
prodsale.saleValue,
   (SELECT TOP 1 X.SaleValue                 
   FROM ProdSale AS X                     
   WHERE X.Productname = Prodsale.productname 
     AND X.saleDate < Prodsale.saleDate   
   ORDER BY X.SaleDate DESC )          AS PreviousValue
FROM Prodsale;

Gives

productName	saleDate	saleValue	PreviousValue
Product 1	10/05/2016	100	
Product 2	10/05/2016	150	
Product 1	11/05/2016	125	100
Product 2	11/05/2016	135	150

 

[illusionek]

Do not store calculations.

Many thanks for your code it works. However I wonder what do you mean by above?

I do not store my calculations in tables, this is part of a query.

 

[jdraw]

Perhaps I misunderstood this

I am trying now to convert above table into below where I have an additional column stating Previous Day Sale.