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
[TABLE="width: 500"]
<tbody>[TR]
[TD]Item
[/TD]
[TD]Amount
[/TD]
[TD]Date
[/TD]
[TD]FiscalPeriod
[/TD]
[/TR]
[TR]
[TD]Sales
[/TD]
[TD]10000.00
[/TD]
[TD]2/19/2014
[/TD]
[TD]???
[/TD]
[/TR]
[TR]
[TD]Sales
[/TD]
[TD]850.00
[/TD]
[TD]1/14/2014
[/TD]
[TD]???
[/TD]
[/TR]
</tbody>[/TABLE]

Table: FP
[TABLE="width: 500"]
<tbody>[TR]
[TD]PeriodStart
[/TD]
[TD]PeriodEnd
[/TD]
[TD]FP
[/TD]
[/TR]
[TR]
[TD]1/2/2014
[/TD]
[TD]1/29/2014
[/TD]
[TD]P01
[/TD]
[/TR]
[TR]
[TD]1/30/2014
[/TD]
[TD]2/26/2014
[/TD]
[TD]P02
[/TD]
[/TR]
[TR]
[TD]2/27/2014
[/TD]
[TD]4/2/2014
[/TD]
[TD]P03
[/TD]
[/TR]
</tbody>[/TABLE]

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