Solved

Access Inner Join Query and Most recent date

Posted on 2013-12-17
21
329 Views
Last Modified: 2013-12-18
Hello,

I am trying to get the most recent update for any particular item.
Table 1 holds items and details of these items, table 2 holdes updates and date of update for any items. It's a 1 to many relationship.
I fail to get the query to populate only data for the most recent update. It populates all the updates for the items.

Please see SQL below.

I hope someone can help.

Dnt

SQL:
SELECT tblitem.BusinessChannel, tblitem.itemCategory, tblitem.itemID, tblitem.itemName, tblitem.itemDescription, tblitemUpdates.itemUpdate, Max(tblitemUpdates.DateofUpdate) AS MaxOfDateofUpdate, tblitem.itemDelegate, tblitem.itemChampion, tblitem.itemtatus2, tblitem.itemtatus
FROM tblitem INNER JOIN tblitemUpdates ON tblitem.itemID = tblitemUpdates.itemID
GROUP BY tblitem.BusinessChannel, tblitem.itemCategory, tblitem.itemID, tblitem.itemName, tblitem.itemDescription, tblitemUpdates.itemUpdate, tblitem.itemDelegate, tblitem.itemChampion, tblitem.itemtatus2, tblitem.itemtatus
HAVING (((tblitem.itemtatus2)=[Forms]![item Status Reports]![Combo28]))
ORDER BY tblitem.BusinessChannel, tblitem.itemCategory;
0
Comment
Question by:dnt2009
  • 8
  • 7
  • 3
  • +1
21 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39724419
try this query


select I.*, a.MaxOfDateofUpdate
From tblItem as I
inner join
(select b.itemID, Max(b.DateofUpdate) AS MaxOfDateofUpdate
from tblitemUpdates as b
group by b.itemID
) as a
on I.itemID=b.itemID
where I.itemtatus2)=[Forms]![item Status Reports]![Combo28]
order by I.BusinessChannel, I.itemCategory;
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39724440
What do you want to do with this data?  Are you just presenting it in a form or report, or do you expect to be able to update it in the query?

Your best bet is to create a subquery that pulls only the most recent updates for each item from tblItemUpdates, this might look like:

SELECT IU.*
FROM tblItemUpdates as IU
WHERE tblItemUpdates.DateOfUpdate =
DMAX("DateOfUpdate", "tblItemUpdates", "ItemID = " & IU.ItemID)

That should give you all of the fields from each ItemID, for only the  most recent update for that ItemID. You can then join that subquery to tblItem.

If you don't need to be able to edit the results, I've found that this generally runs quicker for the subquery:

SELECT IU.*
FROM tblItemUpdates as IU
INNER JOIN (
SELECT ItemID, Max(DateOfUpdate) as MostRecentUpdate
FROM tblItemUpdates
GROUP BY ItemID) as LatestItem
ON IU.ItemID = LatestID.ItemID AND IU.DateOfUpdate = LatestItem.MostRecentUpdate

This avoids the use of the DMAX( ) domain function.

DMAX("DateOfUpdate", "tblItemUpdates", "ItemID = " & IU.ItemID)
0
 

Author Comment

by:dnt2009
ID: 39724444
@ Capricorn - I get an error message saying "Syntax error in JOIN oeration" and the tblItem after the From statement is highlighted
0
 

Author Comment

by:dnt2009
ID: 39724477
@fyed - I don't need to edit the datat. So I've tried the second SQL. However, I get an error message saying "Syntax error in JOIN oeration" and the LatestID.itemID after the ON IU.itemID statement is highlighted
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39724501
try this


select I.*
From tblItem as I
inner join
(select b.itemID, Max(b.DateofUpdate) AS MaxOfDateofUpdate
from tblitemUpdates as b
group by b.itemID
) as a
on I.itemID=b.itemID
where I.itemtatus2)=[Forms]![item Status Reports]![Combo28]
order by I.BusinessChannel, I.itemCategory;
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39724502
that last line should read:

ON IU.ItemID = LatestItem.ItemID AND IU.DateOfUpdate = LatestItem.MostRecentUpdate
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39724517
I obviously didn't read the original query well enough.  It appears that the only info you want from tblItemUpdates is the actual date of the last update, not any additional fields from that table.  If you need any additional fields from tblItemUpdates that relate to the most recent update for each ItemID, then my query would give you that capability, but for the fields you need, I believe that the query capricorn1 posted is probably what you are looking form.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39724521
Try this:

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]
ORDER BY tblitem.BusinessChannel, tblitem.itemCategory; 

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39724541
:-)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39724632
This is another approach -- similar to your own original post, which is actually pretty close.  But you need to write the MAX as a full subquery, and you need a WHERE clause in it to limit the results to one max date per ID:


SELECT tblitem.BusinessChannel, tblitem.itemCategory, tblitem.itemID, tblitem.itemName, tblitem.itemDescription, tblitemUpdates.itemUpdate, (SELECT Max(tblitemUpdates.DateofUpdate)  FROM tblitemUpdates WHERE tblitemUpdates.ItemID = tblItem.ItemID) AS MaxOfDateofUpdate, tblitem.itemDelegate, tblitem.itemChampion, tblitem.itemtatus2, tblitem.itemtatus
FROM tblitem 
WHERE tblitem.itemtatus2 = [Forms]![item Status Reports]![Combo28]
ORDER BY tblitem.BusinessChannel, tblitem.itemCategory; 

Open in new window


Also, IF you might have multiple records with the same date as the max date you will still get multiple records per ID.  In Access, you can do something like this to prevent that:

SELECT tblitem.BusinessChannel, tblitem.itemCategory, tblitem.itemID, tblitem.itemName, tblitem.itemDescription, tblitemUpdates.itemUpdate, (SELECT FIRST(tblitemUpdates.DateofUpdate)  FROM tblitemUpdates WHERE tblitemUpdates.ItemID = tblItem.ItemID ORDER BY tblitemUpdates.DateofUpdate DESC) AS MaxOfDateofUpdate, tblitem.itemDelegate, tblitem.itemChampion, tblitem.itemtatus2, tblitem.itemtatus
FROM tblitem 
WHERE tblitem.itemtatus2 = [Forms]![item Status Reports]![Combo28]
ORDER BY tblitem.BusinessChannel, tblitem.itemCategory; 

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:dnt2009
ID: 39725943
Thank you all for your help

@ capricorn1 and fyed - I get error messages From clause or Syntax error. As I am new to SQL I'm unable to correct these errors.

@ mbizup -  the first SQL you have provided works however it returns the list of item with the most recent update but I also need to see the update and date of the update.

The other two queris give me the list of items in the number of occurence as the number of updates.

Anytime I try to see the udates, I get them all. I need the most recent to show for any particular item.

Thanks
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39726242
<< but I also need to see the update and date of the update. >>

Do you mean you want to see multiple fields (more than just the date) from tblitemUpdates?

Try this:


SELECT tblitemUpdates.[Update], tblitemUpdates.[some Other field], q2.*
FROM tblitemUpdates
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 tblitemUpdates.ItemID = q2.ItemID  AND tblitemUpdates.DateOfUpdate = q2.MaxOfDateofUpdate 
ORDER BY q2.BusinessChannel, q2.itemCategory; 

Open in new window


If you have multiple IDs with the same 'maxDateofUpdate', that might result in multiple results per ID.  If that is the case, try this (which might have to be refined if you have specific rules for 'tie-breakers'):

SELECT tblitemUpdates.[Update], tblitemUpdates.[some Other field], q2.*
FROM tblitemUpdates
INNER JOIN
(SELECT tblItem.*, q.MaxOfDateofUpdate
FROM tblItem INNER JOIN 
(SELECT tblitemUpdates.ItemID, FIRST(tblitemUpdates.DateOfUpdate) AS MaxOfDateofUpdate FROM tblitemUpdates ORDER BY tblitemUpdates.DateOfUpdate DESC GROUP BY tblitemUpdates.ItemID) q
ON q.ItemID = tblItem.ItemID
WHERE tblitem.itemtatus2 =[Forms]![item Status Reports]![Combo28]) q2
ON tblitemUpdates.ItemID = q2.ItemID  AND tblitemUpdates.DateOfUpdate = q2.MaxOfDateofUpdate 
ORDER BY q2.BusinessChannel, q2.itemCategory; 

Open in new window

0
 

Author Comment

by:dnt2009
ID: 39726268
Hi mbizup

In your 1st query (of your most recent post) - I get an error message: "The specified field 'tblItemsUpdates.[Ypdate}' could refer to more tham one table listed in the From cluase of your SQL statement"

With the later SQL(of the same post) I get an error message: "Syntax error in Order By clause"

thanks for your help on this.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39726277
Try this:

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; 

Open in new window

0
 

Author Comment

by:dnt2009
ID: 39726294
I get a pop up requesting : Enter Parameter Value for:
u.Update and u.some Other field. When I click ok whithout adding any values, I get the proper list. Items with update and date of most recent update.
So the list is correctly populated even without those values!

How do we get rid of the popo up?

Thanks
0
 

Author Comment

by:dnt2009
ID: 39726299
I change
SELECT u.[Update], u.[some Other field], q2.* to SELECT u.[ItemUpdate], u.*, q2.*

so all is good now ;-)

Many thanks mbizup.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39726303
Bingo!  Those names were just for example purposes, and you needed to replace them with your actual field names. That is very typical of responses you might receive here, because we don't necessarily know the exact names you are using in your own database.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39726306
Also, if you are selecting ALL fields from your Updates table, the u.ItemUpdate is redundant.  You should be able to get by with just this:

SELECT  u.*, q2.*
etcetera
0
 

Author Comment

by:dnt2009
ID: 39726348
Great thanks.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39726384
Glad to help. :)
0
 

Author Comment

by:dnt2009
ID: 39726742
Hi mbizup

Would it be possible to get the SQL to run without in criteria? in my previous SQL, I got a cretiria from a dropbox in a Fomr. Now in need to run the SQL to get last update for an item - no criteria on any fields.

Thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article will show you how to use shortcut menus in the Access run-time environment.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

16 Experts available now in Live!

Get 1:1 Help Now