Coverting Excel sheet using vlookup tue wit Access DB

[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	???

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

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 probably using a SQL query wit a conditional dsrdata.dob >= fy.periodstart AND dsrdata.dob <= fy.periodend 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.

[CJ_London]

try

SELECT *, Dlookup("FP","FPs",SaleDate & "Between PeriodStart AND PeriodEnd") AS FiscalPeriod
FROM DSRData

Or

SELECT *, (SELECT TOP 1 FP FROM FPs AS T WHERE DSRData.SaleDate Between PeriodStart AND PeriodEnd) AS FiscalPeriod
FROM DSRData

Note that Date is a reserved word, so change it to something else otherwise you will experience problems

Also you have a table with a field the same name (FP) which will also cause problems. The normal convention is to use plurals for table names and the singular for field names - my code above reflects these changes

[skydiver]

SO do I save this as a Query then create a new column in the DSRData table that refers to the Query?

[skydiver]

Also is the Between statement inclusive?

P01 start = 1/2/2014
P01 end = 1/29/2014

Does:
lookup date 1/2/2014 = P01?
lookup date 1/3/2014 = P01?
lookup date 1/29/2014 = P01?

[skydiver]

I used the second query and it appears that this query looks up the entire DSRData table and adds the lookup column as part of the select query. As I said in the original post, I have 9 other tables where I am doing a similar lookup that need to be additional columns in the DSRData table Like Fiscal Week, Fiscal Year, PayPeriod, Etc.

So I don't think that this is what I am looking for.

[June7]

Cannot do DLookup (or any domain aggregate function) calcs in table. Can use IIf()

Can also do calc for the fiscal period in a query then use that query in other queries.

What are these 9 other tables? Want to post diagram of data structure?

BETWEEN AND is inclusive.

[CJ_London]

SO do I save this as a Query then create a new column in the DSRData table that refers to the Query?

save as a query, there is no need to add a new column into the DSRDate table, the query pulls the information through from that table. Access is a database, not a glorified Excel and treats data in a completely different way - suggest you research the principles of 'normalisation'

As I said in the original post, I have 9 other tables where I am doing a similar lookup

So create similar queries - again, suggest you research the principles of 'normalisation'