以前的记录-MS Access [英] Previous record -MS Access

问题描述

如果有人让我知道如何实现"PreviousValue"克隆，请提供帮助，如下所示在MSAccess中

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

我在表中存储了除总体"以外的各种类别的记录，并且基于ID/名称/办公室/产品/类别组合，我希望通过MS Access功能/高效查询获得以前的值列

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

预先感谢！

查询结果-我猜是因为Top1函数的缘故，这与预期不符，正确的输出如下图所示

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

推荐答案

您可以使用子查询来获取先前的值.

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; 

这篇关于以前的记录-MS Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！