Picking up values from previous row.

[malaydash]

I have an table in MS Access in which I have got date wise sales figure for different product in three different columns (one column for date, one for the product code & the third for sales figure). I want the program to find the last "sales figure" for a particular product in the sales column (by going backward) which was less than the last day's figure (or for any specified date) and then stop looking further & provide the data in the fourth column. Then the program should repeat the step & provide the figure less than the figure found in the previous step in the fifth column.

Here with I am attaching the file for reference. The file contains two table. The first one is the original table named "SALES" & the second one is "SALES_REQD" which is a sample table in which I have manually entered the required data for one particular product for a very small period. I want the same result in the "SALES" table for multiple product & for a long period.

Please suggest a method by using query, macro, VBA code or any other method you can think of. Please help as I am stuck with this problem & really need to get out of it.

Thanks & regards

 

[rodmc]

Theres a few ways to do this, the easiest way I have found is creating a query to find the last record, then I place a control holding that value in a sub form that is hidden, then I just refer to that control when I need to pull through the record

 

[malaydash]

Thanks rodmc for your suggestion.

But will you please elaborate the steps Or else can you please do the same in the attached file & post it here for reference?

Your kind help really will be appreciated.

 

[jdraw]

see Get the value in another record at
http://allenbrowne.com/subquery-01.html

 

[malaydash]

Thanks jdraw for the useful link. The content in the link provided is very good & I think this can solve my problem. I tried the solution given there but didn't get the result. Actually I have never written SQL statements & therefore struggling with the syntax error. Also the examples given in the link is not so clear to me as the actual database is not given in the example.

So, if I am not asking too much, will you please use my attached database & prepare the query & post it here so that I can understand it better. Actually I am interested in getting the second figure less than the current sales figure(which I have described in my problem)

Then the program should repeat the step & provide the figure less than the figure found in the previous step in the fifth column.

This will really help me in great way.

So please do it once for me if you have some time.

Thanks & regards.

 

[jdraw]

Please post in mdb format (Acc2003) -- not everyone has Acc2007 or 2010.

 

[malaydash]

Please see the file in .mdb format.

 

[jdraw]

Comments based on quick look at your mdb:

Many would advise NEVER store calculated values in a Table.
Your SALES_REQD that you keyed in to show values is inconsistent. The values do not match from one column to the next.

Is there a business rationale for such a request. It seems suited to a Report, and although I'm not an Excel person, better suited to Excel -- copy and paste.

To get the data, as a query, for a given Product,
you can use several queries on this template for product 500114.

If you have 30 records in your list, then replace X with 29

SELECT SALES.DATE , SALES.PRODUCT_CODE , SALES.SALES_IN_TON
FROM (SELECT TOP X SALES.DATE, SALES.PRODUCT_CODE, SALES.SALES_IN_TON
FROM SALES
WHERE (((SALES.PRODUCT_CODE)="500114"))
ORDER BY SALES.DATE DESC) <-- get the top values in desc sequence
ORDER BY DATE; <--- display the values in ascending sequence

Since your data is sorted in ascending order by Date, your result is to select the Top
(initial count - 1) from the template shown. In effect in each of your proposed columns, you are just looking at the values in rows not including the first row, next iteration is to look at rows not including the first two rows.

 

[malaydash]

Dear Jdraw
Thanks for the help & sorry for the delay. I think I can solve my problem by the hint you have given. I will try the same & get back to you if I face any problem.

Anyway lot of thanks to you & all the helpful members of the forum