Access Inner Join Query and Most recent date - Part 2

Hello,

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
INNER JOIN
(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?

Thanks

Microsoft OfficeMicrosoft AccessSQL

Last Comment

dnt2009

8/22/2022 - Mon

als315

1/7/2014

If you like to see all records from one table/query and only matching from other, you should use Left Join (or Right Join) instead of Inner Join:
http://office.microsoft.com/en-us/access-help/left-join-right-join-operations-HP001032251.aspx
Try to change first INNER JOIN to LEFT JOIN

dnt2009

1/7/2014

ASKER

If I change the first INNER JOIN to LEFT - I get all the updates for every item. I would like to get the most recent update - even if empty for all items.

John_Vidmar

1/7/2014

I agree with using an outer-join (such as left or right, I favor left-join):

SELECT	whatever
FROM	tblItem		i
LEFT
JOIN	(	SELECT	ItemID
	,		MAX(DateOfUpdate) AS MaxOfDateofUpdate
		FROM	tblitemUpdates
		GROUP
		BY	ItemID
	)		q	ON	i.ItemID = q.ItemID
LEFT
JOIN	tblitemUpdates	u	ON	q.ItemID = u.ItemID
				AND	q.MaxOfDateofUpdate = u.DateOfUpdate
WHERE	i.itemtatus2 =[Forms]![item Status Reports]![Combo28]
ORDER
BY	something