Q:

How do i join the last record from one table where the date is older than other table?

This is my first post here, and the first problem i havent been able to find a solution to on my own. I have a MainTable that contains the fields: Date, MinutesActiveWork (And other not relevant fields). I have a second table that contains the fields: ID, id_Workarea, GoalOfActiveMinutes, GoalActiveFrom.
I want to make a query that returns all records from MainTable, and the active goal for the date. 
Exampel: 
Maintable (Date = dd/mm/yyyy)
ID      Date      ActvWrkMin    WrkAreaID
1    01-01-2019      45             1
2    02-01-2019      50             1
3    03-01-2019      48             1

GoalTable: 
ID     id_Workarea     Goal     GlActvFrm
1          1            45      01-01-2019
2          2            90      01-01-2019
3          1            50      03-01-2019

What i want from my query:
IDMain     Date     ActvWrkMin     Goal     WrkAreaID
1       01-01-2019      45          45          1
2       02-01-2019      50          45          1
3       03-01-2019      48          50          1

The query that i have now is really close to what i want. But the problem is that the query outputs all goals that is less than the date from MainTable (It makes sense why, but i dont know what criteria to type to fix it). Like so:
IDMain     Date     ActvWrkMin     Goal     WrkAreaID
1       01-01-2019      45          45          1
2       02-01-2019      50          45          1
3       03-01-2019      48          45          1 <-- Dont want this one
3       03-01-2019      48          50          1

My query
SELECT tblMain.Date, tblMain.ActiveWorkMins, tblGoal.Goal
FROM VtblSumpMain AS tblMain LEFT JOIN (
SELECT VtblGoalsForWorkareas.idWorkArea, VtblGoalsForWorkareas.Goal, VtblGoalsForWorkareas.GoalActiveFrom (THIS IS THE DATE FIELD)
FROM VtblGoalsForWorkareas
WHERE VtblGoalsForWorkareas.idWorkArea= 1) AS tblGoal ON tblMain.Date > tblGoal.GoalActiveFrom 
ORDER BY tblMain.Date

(I know i could do this pretty simple with Dlookup, but that is just not fast enough)
Thanks for any advice!

A:

For this, I think you have to use the nested query as I mention below.
select tblMain.id,tblMain.Date,tblMain.ActvWrkMin, tblMain.WrkAreaID,
(select top 1 Goal 
from GoalTable as gtbl 
where gtbl.id_workarea = 1 
and tblmain.[Date] >= gtbl.glActvFrm order by gtbl.glActvFrm desc) as Goal
from Maintable as tblMain

Check the below image for the result which is generated from this query.

I hope this will solve your issue.

