0

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.

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

The other excel file has (let's suppose):

Asset-----Sold_Date
1. X------1-Dec-2013
2. Z------12-Mar-2014

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.

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)

Thanks

user3125707
  • 399
  • 2
  • 5
  • 20

1 Answers1

0

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.

A very similar problem is described in How to use another table fields as a criteria for MS Access

Community
  • 1
  • 1
Don George
  • 1,328
  • 1
  • 11
  • 18