如何在msaccess中获得最后的购买价格 [英] How to get last purchase price in msaccess

问题描述

亲爱的，



我的桌子叫 Trn_Purchase

字段是

 ItemCode 
 ItemDesc 
 PurchaseDates 
价格

我想要得到最后的购买价格取决于日期



表格如下数据

 ItemCode PurchaseDate价格
 A 01-Mar-2010 4 
 A 02-Mar-2011 6 
 B 01-Mar-2010 7 
 B 02-Mar-2011 2 

然后结果应该是

 itemCode PurchaseDate价格
 2011年3月2日6 
 B 02-Mar-2011 2 

怎么做？



谢谢

巴斯特。



我尝试了什么：



我在下面做了。

  SELECT  p。* 
  FROM  
 Trn_Purchase p，
（ SELECT  *  FROM  Trn_Purchase）m 
  WHERE  
 p.Itemcode = m.Itemcode 
 和 p。[购买日期] = m。[购买日期] 

解决方案

您需要做的就是通过 ItemCode 对数据进行分组以获取 MAX（PurchaseDate）然后加入价格：

  SELECT  a.ItemCode，a.PurchaseDate，a.Price 
  FROM  Trn_Purchase  AS  a  INNER   JOIN （
  SELECT  b.ItemCode，MAX（b.PurchaseDate） AS  PurchaseDate 
  FROM  Trn_Purchase  AS  b 
  GROUP   BY  b.ItemCode 
） AS  c  ON  a.ItemCode = c.ItemCode  AND  a.PurchaseDate = c.PurchaseDate 

如需了解更多信息，请参阅：

MS Access：最大功能 [ ^ ]

Dear,

I have table called Trn_Purchase
fields are

ItemCode
ItemDesc
PurchaseDates
Price

I want to get the last purchase price depend on date

table have e.g below data

ItemCode PurchaseDate	Price 
A	01-Mar-2010 4 
A	02-Mar-2011 6
B	01-Mar-2010 7
B	02-Mar-2011 2

then result should be

itemCode	PurchaseDate	Price
A	02-Mar-2011 6
B	02-Mar-2011 2

How to do this?

Thanks
Basit.

What I have tried:

I did below.

SELECT  p.*
FROM
 Trn_Purchase p,
 (SELECT * FROM Trn_Purchase) m
WHERE 
 p.Itemcode = m.Itemcode
 and p.[Purchase Date] = m.[Purchase Date]

解决方案

All you need to do is to group data by ItemCode to get MAX(PurchaseDate) then join Price:

SELECT a.ItemCode, a.PurchaseDate, a.Price
FROM Trn_Purchase AS a INNER JOIN (
    SELECT  b.ItemCode, MAX(b.PurchaseDate) AS PurchaseDate 
    FROM Trn_Purchase AS b
    GROUP BY b.ItemCode
) AS c ON a.ItemCode = c.ItemCode AND a.PurchaseDate = c.PurchaseDate

For further information, please see:
MS Access: Max Function[^]

这篇关于如何在msaccess中获得最后的购买价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！