CODEWITHSUNDEEP
sqlms-access
iKindOfCode
iKindOfCode

Reputation: 29

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!

Upvotes: 0

Views: 61

Answers (1)

Ronak
Ronak

Reputation: 190

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.

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

I hope this will solve your issue.

Upvotes: 1

Related Questions