linking to latest date that is not greater than the date produced

[bcblair]

I want to link a table(Items) to another table(Test Reports). The common field will be item number(itemno) and Plant Number(plantno) but there may be more than one alike. When there are more than one, I want to compare The date sampled in the test report table to the date updated in the item table and link to the item that was the latest updated(DateUpdated) as long is it wasn't greater than the date tested(datemolded).

 

[DougP]

Make a query then paste this in the &quot;SQL&quot; view as a staring point.<br>
<br>
SELECT Items.ItemNo, Items.plantno, Items.date<br>
FROM [Test Reports] INNER JOIN Items ON ([Test Reports].date = Items.date) AND ([Test Reports].ItemNo = Items.ItemNo);<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[elizabeth]

You may have a structural problem that you need to address before you get to your relationship problem. If you have an item that goes through several processes (molds?), I would expect you would have a table Item related one-to-many with a second table ItemMold. ItemMold would then relate one-to-many to a third table, ItemMoldTest. In that case, you only need to look for the latest date in ItemMoldTest that relates to ItemMold. Does this make sense?