Solved

Access Inner Join Query and Most recent date - Part 2

Posted on 2014-01-07
7
521 Views
Last Modified: 2014-01-10
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
0
Comment
Question by:dnt2009
  • 3
  • 3
7 Comments
 
LVL 39

Expert Comment

by:als315
ID: 39762061
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
0
 

Author Comment

by:dnt2009
ID: 39762103
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.
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39762122
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

Open in new window

0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 39

Expert Comment

by:als315
ID: 39762398
MAy be you can upload some sample DB with dummy records for testing?
0
 

Author Comment

by:dnt2009
ID: 39765079
Please find attached sample database.

thanks for your help.

@John_vidmar - i get a syntax error.

Thanks
SampleDatabase.mdb
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 39765440
Look at sample. There are 2 variants:
1. 2 sequental queries (may work faster with big tables)- qry_LastUpdate2 and qry_Show_All_with_Updates
2. Nested query - qry_Show_All_with_Updates_nestedQuery
Results are same
SampleDatabase.mdb
0
 

Author Comment

by:dnt2009
ID: 39770310
thank you als315. I used the nested query and it works perfectly.

Many thanks for your help.

dnt
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now