Comparing two from rows in the same field

[mor]

I have a table [VL] with four fields, [vl_id], [product], [vl_date], [valeur_liquidative].

The idea of the table is that you can input and update the value of each product on a given day.

I need to find the difference in days between successive dates (vl_date) each time that a product value (valeur_liquidative) is updated. Values aren't updated everyday as it is not updated during the weekends. I have had a go at this but have struggled..

A picture of what the table looks like is attached for your reference.

Many thanks!
MOR

 

[rzw0wr]

Your tables are not normalized it appears to me.

Your Product and valeur_liquidative should appear in a table only once.

Read the following to see if helps you .

http://symplebyte.com/microsoft_office/access/how_to_normalize_data_access.html

Dale

 

[mor]

Apologies, the valeur liquidative values are just example values, they are not necessarily the same.. The valeur liquidative does not describe the product as it changes with time.

Unless you are implying I have a different table for each product, such that each table will give the evolutive values of valeur liquidative in each case?

 

[rzw0wr]

What i s produit?
It appears more than once with a different ID each time.
Can you post a picture of your tables?

Dale

 

[mor]

Its french for product. It is the product id. This is just a table that logs the daily value of each product that the company sells.

 

[rzw0wr]

You have only 1 table?
You need at least 2.
One other one for dates.

Dale

 

[mor]

I'm sorry but I don't see that helps. I would just be replacing the date with a foreign key and having an extra table with two fields; date_id and date.

 

[JHB]

Try the below, it shows the result in the picture:

SELECT VL.Produit, VL.Vl_Date, (SELECT max(VL_1.Vl_Date) FROM VL as vl_1 WHERE VL_1.Vl_Date<[VL].[Vl_Date] AND VL_1.Produit= VL.Produit;) AS Vl_Date1, DateDiff("d",[Vl_Date1],[Vl_Date]) AS DaysBetween
FROM VL
ORDER BY VL.Produit, VL.Vl_Date;

 

[mor]

Thank you so much!

 

[JHB]

You're welcome - luck with your project.