Replace VLookup in Access Database

[skydiver]

I am new to Access so please bear with my lack of knowledge..

I have an Access database I am setting up to replace an Excel workbook. The Access database is comprised of multiple tables but the one in question where I am stumped is as follows:

Table: DSRData

Item	Amount	Date	FiscalPeriod
Sales	10000.00	2/19/2014	???
Sales	850.00	1/14/2014	???

<tbody>
</tbody>

Table: FP

PeriodStart	PeriodEnd	FP
1/2/2014	1/29/2014	P01
1/30/2014	2/26/2014	P02
2/27/2014	4/2/2014	P03

<tbody>
</tbody>

In Excel I could use a Vlookup *(true) to find the correct fiscal period to assign the data to from the FP table but i am at a bit of a loss as to how to do this in Access. I know I will need to do create a table join in access proably using a SQL query but I haven't figured out how to dynamically assign the correct FP value to each row of data in the DSRData table.

I have approximately 9 columns of data that will need this type of lookup. My goal is to create a replication of the Excel worksheet in Access that I can then save to our SharePoint site with Pivot charts of the full data set as a SharePoint Webpart.

 

[Darren Bartrup]

You'll need an SQL query for that. The FiscalPeriod field won't appear in the DSRData table - it's stored in the FP table and will only appear when retrieved by the query. I've had to rename Date to dDate as Date is a reserved word.

SELECT  Item,
        Amount,
        dDate,
        FP
FROM    DSRData LEFT JOIN FP ON DSRData.dDate>=FP.PeriodStart AND
                                DSRData.dDate<=FP.PeriodEnd