Query pull data from another table

[tareyj8569]

Hey everyone,

What I have is 2 tables one with data, and one with fiscal year periods that go up to the year 2020.
and a query which I am trying to pull the data into.

[Data_table] is set up as

Test	Date	PorF
Test1	5/10/2015	P
Test2	8/6/2015	F

[Fiscal_Year_table]

Fiscal/PP	PPStart	PPEnd
15/01	5/1/2015	5/14/2015
15/02	8/1/2015	8/14/2015

Now I have a query off Data table which includes all 3 fields and a 4th field
With the 4th field I would like for it to look at the date then look at [Fiscal_Year_table] and if the date field falls between ppstart and ppend then show fiscal/pp for 4th field


Which if done right would look like below

[Query_combined]

Test	Date	PorF	Fiscal/PP
Test1	5/10/2015	P	15/01
Test2	8/6/2015	F	15/02

I am guessing it needs an iif statement? but I am not positive.

if it would make it easier I can combine the ppstart and ppend show it would should one field as 5/1/2015-5/14/2015 so it only looks at one fields rather then 2?

Thank you for your time and help.

[pbaldy]

Most efficient is probably joining the tables with a non-equi join. The design grid can't represent that so it has to be done in SQL view. It may also make the query read-only, but I'm not sure. You can also use a DLookup() in your query, comparing the date field against the 2 in the other table.

I'd leave the fields as is, not combine them into one.

[tareyj8569]

Could you by change give me an example of how it would look as a dlookup... I have been trying for awhile now and just striking out.
What I have tried was
FYPP: Dlookup("[Date]","Fiscal_Year_table","[Fiscal/PP] Between [PPStart] And [PPEnd]")

perhaps I have something in wrong order or just not even close. appreciate the help, thanks.

[tareyj8569]

still no luck, I'm calling it quits for the night. if any one can give me a dlookup example that does the above It would be much appreciated. thanks

[pbaldy]

Try

"[Fiscal/PP] >= [PPStart] And [Fiscal/PP] <= [PPEnd]"

[tareyj8569]

solved, I ended up going with a non-equi join like suggested earlier and worked out. Thanks for the help

[pbaldy]

Happy to help.