asked on

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;

Rey Obrero (Capricorn1)

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;

Dale Fye

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)

dnt2009

ASKER

@ Capricorn - I get an error message saying "Syntax error in JOIN oeration" and the tblItem after the From statement is highlighted

dnt2009

ASKER

@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

Rey Obrero (Capricorn1)

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;

Dale Fye

that last line should read:

ON IU.ItemID = LatestItem.ItemID AND IU.DateOfUpdate = LatestItem.MostRecentUpdate

Dale Fye

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.

mbizup

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

Rey Obrero (Capricorn1)

:-)

mbizup

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

dnt2009

ASKER

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

mbizup

<< 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

dnt2009

ASKER

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.

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dnt2009

ASKER

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

dnt2009

ASKER

I change
SELECT u.[Update], u.[some Other field], q2.* to SELECT u.[ItemUpdate], u.*, q2.*

so all is good now ;-)

Many thanks mbizup.

mbizup

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.

mbizup

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

dnt2009

ASKER

Great thanks.

mbizup

Glad to help. :)

dnt2009

ASKER

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