Time-Dependent Mapping in Access

[magemaester]

Hi all,

I'm working on a large database and I'm encountering a problem that seems so logically simple but I cannot get it to work no matter what in Access. It is time-dependent mapping, and I've simplified it to an example to show you what I mean.

Suppose I have the following data:

Table 1:


Year Pet
2015 Cat
2016 Dog
2017 Cat

Table 2:
Pet FavoriteFood
Cat Apple
Dog Pear

If I do a Table 1 LEFT JOIN Table 2, I get

Year Pet FavoriteFood
2015 Cat Apple
2016 Dog Pear
2017 Cat Apple


Now, suppose that the FavoriteFood for Cat changes to Bananas from 2016 onwards, I have a mapping like this

Table 3:
Year Pet NewFavoriteFood
2016 Cat Bananas

How do I write the SQL code / structure my Table 3 mapping such that I can produce a table that looks like this?

Year Pet FavoriteFood
2015 Cat Apple
2016 Dog Pear
2017 Cat Bananas


In my head the logic is: I only want Cat -> Apple to change to Cat -> Bananas after the year 2016, so I have to use this statement in my query: "WHERE Table1.Year > Table3.Year"
But this doesn't work because it ends up omitting all the other data in my final view.


Would super appreciate it if someone could offer a solution!

[June7]

Table3 doesn't really make sense.

Save the food in YearPet. So essentially the YearPetFood query would actually be a table.

Now, what is your REAL data?