Previous record -MS Access

Question

Appreciate help if anybody let me know how to acheive "PreviousValue" cloumn as shown below in MSAccess

I have records stored in table for various categories other than "Overall" and based on ID/Name/Office/product/Category combination I want to acheive previous value column thorugh MS Access function/efficient query

ID       Name        Office Product Overall    Month         Value  Previous
228      Woodlands  london  Europe  Sales      201106          6    0
228      Woodlands  london  Europe  Sales      201202          3    6
228      Woodlands  london  Europe  Sales      201207          4    3
228      Woodlands  london  Europe  Overall    201106          6    0
228      Woodlands  london  Europe  Overall    201202          3    6
228      Woodlands  london  Europe  Overall    201207          4    3
228      Woodlands  london  Europe  Exetion    201202          6    0
228      Woodlands  london  UK      Exetion    201202          6    1
228      Woodlands  london  UK      Exetion    201106          1    0
228      Woodlands  london  Europe  Exetion    201207          6    6
228      Woodlands  london  Europe  Content    201106          6    0
228      Woodlands  london  UK      Content    201106          1    0
228      Woodlands  london  Europe  Content    201202          3    6
228      Woodlands  london  UK      Content    201202          1    1
228      Woodlands  london  UK      Content    201207          1    1
228      Woodlands  london  Europe  Content    201207          4    3

Thanks in Advance !!

Result of query--Which is not as expected because of Top1 function i guess, the correct output is as shown in image below

 ID  Name      Office   Product Category    Date       Month    ValuePrevious
228  Investors  london  Europe  Content   01/06/2011    201106  6   0
228  Investors  london  Europe  Content   23/02/2012    201202  3   6
228 Investors   london  Europe  Content   01/07/2012    201207  4   6

enter image description here

Please do not post pictures. Even very badly formatted data is better than a picture. Post the data and I can certainly answer. — Fionnuala
– Fionnuala, Commented Aug 17, 2012 at 7:59
Your tags say MS Access. Open the table, cut and paste a few lines into your post. If the worst comes to the worst, type them. — Fionnuala
– Fionnuala, Commented Aug 17, 2012 at 8:18
Your sample data does not match your new post. Do you have the correct sample data? — Fionnuala
– Fionnuala, Commented Aug 17, 2012 at 10:01

Fionnuala · Accepted Answer · 2012-08-17 10:15:16Z

0

You can use a subquery to get the previous value.

SELECT tx.id,
       tx.name,
       tx.office,
       tx.product,
       tx.overall,
       tx.month,
       tx.VALUE,
       tx.previous,
       Nz((SELECT TOP 1 [value]
           FROM   tablename t
           WHERE  t.id = tx.id
                  AND t.name = tx.name
                  AND t.office = tx.office
                  AND t.product = tx.product
                  AND t.overall = tx.overall
                  AND t.month < tx.month
           ORDER  BY t.month, rnd(t.id) DESC), 0) AS Prev
FROM   tablename tx
ORDER  BY tx.month;

New query based on changed, very sparse, sample data

SELECT tz.id, 
       tz.name, 
       tz.office, 
       tz.product, 
       tz.date, 
       tz.month, 
       tz.VALUE, 
       Nz((SELECT TOP 1 [value] 
           FROM   tablename t 
           WHERE  t.id = tz.id 
                  AND t.name = tz.name 
                  AND t.office = tz.office 
                  AND t.product = tz.product 
                  AND t.date < tz.date
           ORDER  BY t.date DESC), 0) AS Prev 
FROM   tablename tz 
ORDER  BY tz.date;

edited Aug 17, 2012 at 10:15

answered Aug 17, 2012 at 9:00

Fionnuala

91.5k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user1268559 Over a year ago

Hi Remou thanks for your quick help..But i am getting error as "At most one record can be returned by this subquery"..i have pasted query in my original post..actually query is giving 400-500 records but after that giving this error..

Fionnuala Over a year ago

In that case you have more duplicate records that I would have thought possible. Have you got a unique ID on the table?

user1268559 Over a year ago

no unique ID..it's sharepoint maintained table..can i debug somehow which are duplicates? will have corrected them

Fionnuala Over a year ago

Okay, add rnd(t.id) to the ORDER BY as illustrated above.

Fionnuala Over a year ago

The error indicates that you have more than one record with exactly the same name, office,product, overall and month. The addition to ORDER BY will pick one of these records randomly. This may not be what you need.

|

Collectives™ on Stack Overflow

Previous record -MS Access

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related