Solved

Access Inner Join Query and Most recent date - Part 2

Posted on 2014-01-07
7
516 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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…

708 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

17 Experts available now in Live!

Get 1:1 Help Now