Access Inner Join Query and Most recent date - Part 2
Posted on 2014-01-07
My question is following up my previous question (Access Inner Join Query and Most recent date) which mbizup helped me solved.
SELECT u.[Update], u.[some Other field], q2.*
FROM tblitemUpdates u
(SELECT tblItem.*, q.MaxOfDateofUpdate
FROM tblItem INNER JOIN
(SELECT tblitemUpdates.ItemID, MAX(tblitemUpdates.DateOfUpdate) AS MaxOfDateofUpdate FROM tblitemUpdates GROUP BY tblitemUpdates.ItemID) q
ON q.ItemID = tblItem.ItemID
WHERE tblitem.itemtatus2 =[Forms]![item Status Reports]![Combo28]) q2
ON u.ItemID = q2.ItemID AND u.DateOfUpdate = q2.MaxOfDateofUpdate
ORDER BY q2.BusinessChannel, q2.itemCategory;
I need to get updates for all items in the tblItems even if the field is blank. Whith the above query I get a list of items and the corresponding update.
I need to get the list of items even if there are no updates.
I've played around by changing the first joint but I only see part of the items list not all items.
Can anybody help please?