dnt2009
asked on
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.DateOfU pdate) 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
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.DateOfU
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
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.
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
MAy be you can upload some sample DB with dummy records for testing?
ASKER
Please find attached sample database.
thanks for your help.
@John_vidmar - i get a syntax error.
Thanks
SampleDatabase.mdb
thanks for your help.
@John_vidmar - i get a syntax error.
Thanks
SampleDatabase.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you als315. I used the nested query and it works perfectly.
Many thanks for your help.
dnt
Many thanks for your help.
dnt
http://office.microsoft.com/en-us/access-help/left-join-right-join-operations-HP001032251.aspx
Try to change first INNER JOIN to LEFT JOIN