I have 2 tables: in Table 1 are a list of projects and data by month surrounding those projects. In Table 2 it contains ratings of the projects reference in Table 1, but it is on a weekly basis.
I am attempting to combine the data from both tables, but I only want to include information from Table 2 for the latest date range/month.
Example:
Table 1
Project 1 June Data Project 1 July Data Project 2 June Data Project 2 July Data
Table 2
Project 1 6/13/16 Project 1 6/20/16 Project 1 7/4/16 Project 1 7/11/16 Project 2 6/20/16 Project 2 6/27/16 Project 2 7/11/16 Project 2 7/18/16
From the example above, I would want to combine the data from Table 2 to Table 1, but for Project 1 I need 6/20/16 and 7/11/16 data only. And for Project 2 I need 6/27/16 and 7/18/16 data only.
Projects can end in the middle of a month, so the projects rating may not always be the last week of the month.