MS访问最新日期 [英] MS access latest date issue
问题描述
我有2个excel文件正在工作，在这些文件中我维护资产费率和费率发布日期.另一个Excel文件包含资产列表和出售日期.
I have 2 excel files at work where I maintain the rates of assets and the dates when the rates were issued. Another excel file has the list of assets and the dates when they were sold.
因此，一个excel文件具有以下列:
So one excel file has the following columns:
Asset------Rate------Rate_Issued_On
1. X-------1500------21-Apr-2014
2. X-------2000------28-Aug-2013
3. Z-------2200------11-Jan-2014
4. X-------3000------1-Jan-2014
另一个excel文件具有(让我们假设):
The other excel file has (let's suppose):
Asset-----Sold_Date
1. X------1-Dec-2013
2. Z------12-Mar-2014
现在，由于资产X的出售日期介于2014年1月1日至2013年8月28日之间，因此应采用2000的汇率.例如，如果出售日期为2014年4月22日，则应采用以下汇率: 1500.如果出售日期为2013年8月27日，则应显示空白记录.因此，基本上销售日期应该大于最新的发行日期"，并且汇率应与该特定日期相对应.
Now since the sold date of Asset X lies between 1-Jan-2014 and 28-Aug-2013 it should take the rate of 2000. If for example the sold date was 22-Apr-2014 it should take the rate as 1500. If the sold date is 27-Aug-2013 it should display a blank record. So basically the sold date should be greater than the latest Issued date and rate will correspond to that particular date.
我可以轻松地在excel中运行此文件，但问题是excel文件现在变得很大，以至于运行非常缓慢.因此，我只希望将此内容合并到ms访问中.这可能吗? (我是ms access的新手，所以请您对我进行一些简化)
I can easily get this working in excel but the problem is that the excel file has now become so large that it runs very slow. So I just want this incorporated in ms access. Is this possible? (I am a novice in ms access so kindly requesting you to go a little easy on me)
谢谢
推荐答案
是的-一些简单的查询可以匹配所需的数据.如果您的两个表分别称为费率和销售额"，则可以使用两个查询来获取所需的结果.第一个查询将使用销售和费率"表查找小于Sale_date的最大Rate_date，第二个查询将其与"Rate"表匹配以获取该日期的费率.
Yes - a few simple queries can match up the data they way you want. If your two tables are called Rates and Sales, you could use two queries to get the results you need. The 1st query would use the Sales and Rates table to find the largest Rate_date that is less than the Sale_date, and the second query would match this back to the Rate table to get the rate on that date.
