I got help from one of the forums to write the statement in access and after a little modification it slowly works as follow:

SELECT some columns

FROM (Trial LEFT JOIN Culture ON Trial.CultureID = Culture.cultureID) LEFT JOIN (Select cs.CultureID, cs.WeekNr FROM (SELECT CultureID, MIN(CultureStepID) as MinCS FROM CultureStep GROUP BY CultureID ) Fcs INNER JOIN CultureStep cs ON fcs.MinCS=cs.CultureStepID) AS T ON Culture.cultureID = T.cultureID;

However, I no longer am able to update , add or delete since the now 3rd created table T has no primary key right?

So how can I rewrite the above statement such that I end up with tables Culture,Trial and CultureStep

Can you please provide help on how you come up with your solution to be able to learn how to think when approaching this and similar statements 🙂