Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

Access Inner Join Query and Most recent date

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
dnt2009
Asked:
dnt2009
  • 8
  • 7
  • 3
  • +1
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
Dale FyeCommented:
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
 
dnt2009Author Commented:
@ Capricorn - I get an error message saying "Syntax error in JOIN oeration" and the tblItem after the From statement is highlighted
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
dnt2009Author Commented:
@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
 
Rey Obrero (Capricorn1)Commented:
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
 
Dale FyeCommented:
that last line should read:

ON IU.ItemID = LatestItem.ItemID AND IU.DateOfUpdate = LatestItem.MostRecentUpdate
0
 
Dale FyeCommented:
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
 
mbizupCommented:
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
 
Rey Obrero (Capricorn1)Commented:
:-)
0
 
mbizupCommented:
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
 
dnt2009Author Commented:
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
 
mbizupCommented:
<< 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
 
dnt2009Author Commented:
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
 
mbizupCommented:
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
 
dnt2009Author Commented:
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
 
dnt2009Author Commented:
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
 
mbizupCommented:
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
 
mbizupCommented:
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
 
dnt2009Author Commented:
Great thanks.
0
 
mbizupCommented:
Glad to help. :)
0
 
dnt2009Author Commented:
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

  • 8
  • 7
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now