Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2017
    Posts
    5
    Provided Answers: 1

    Answered: Query to show only records base on most recent date change

    Hey all,
    Please help! Im new to Access and Sql. I have a table that looks something like the one below. I want the resulting query to only show the records with the most recent date for each item. If I simply use max() on the ModifiedDate field it returns all records with the most resent date for each item, but doesnt remove the other records and i still end up with 9 records.

    UniqueID ITEM PRICE ModifiedDate
    232323 item-1 $1.23 1/1/2017
    111344 item-1 $3.12 12/11/2016
    11111567 item-1 $2.17 12/13/2016
    232300 item-2 $7.23 1/12/2017
    119989 item-2 $8.43 11/1/2016
    12202211 item-2 $6.23 12/13/2016
    232212 item-3 $14.22 2/13/2017
    122002 item-3 $13.14 10/11/2016
    11887556 item-3 $12.12 12/23/2016

    Looking for the result to be this (just 3 records):
    UniqueID ITEM PRICE ModifiedDate
    232323 item-1 $1.23 1/1/2017
    232300 item-2 $7.23 1/12/2017
    232212 item-3 $14.22 2/13/2017

    Code:
    SELECT TempTable.UniqueID, TempTable.ITEM, TempTable.PRICE, Max(TempTable.ModifiedDate) AS MaxOfModifiedDate
    FROM TempTable
    GROUP BY TempTable.UniqueID, TempTable.ITEM, TempTable.PRICE;
    Help is greatly appreciated!
    Thanks in advance

  2. Best Answer
    Posted by PHILnTEX

    "
    Quote Originally Posted by MikeTheBike View Post
    Hi Again

    I indicated you needed to JOIN the sub query not use it as a criteria, something like this

    Code:
    SELECT TempTable.UniqueID, TempTable.ProdItem, TempTable.ProdPRICE, TempTable.ModifiedDate FROM TempTable INNER JOIN (SELECT TempTable.UniqueID,  Max(TempTable.ModifiedDate) AS MaxOfModifiedDate FROM TempTable GROUP BY UniqueID) AS MaxTempTable ON (MaxTempTable.MaxOfModifiedDate = TempTable.ModifiedDate) AND (MaxTempTable.UniqueID = TempTable.UniqueID)
    This is obviously untried, but it should be somewhere near!

    HTH


    MTB
    PREFECT! Thanks MikeTheBike!!!
    Just needed a slight adjustment. It was grouping on UniqueID - which is unique so it was reporting back all 9 items. I replaced that with ProdItem and it worked perfectly.

    Code:
    SELECT TempTable.UniqueID, TempTable.ProdItem, TempTable.ProdPRICE, TempTable.ModifiedDate
    FROM TempTable INNER JOIN (SELECT TempTable.ProdItem, Max(TempTable.ModifiedDate) AS MaxOfModifiedDate 
    FROM TempTable GROUP BY ProdItem)  AS MaxTempTable ON (TempTable.ProdItem = MaxTempTable.ProdItem) AND (TempTable.ModifiedDate = MaxTempTable.MaxOfModifiedDate);
    I really appreciate the help.
    Cheers,
    Phil"


  3. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    use the Groupby and Set the ModifiedDate to Last
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  4. #3
    Join Date
    Feb 2017
    Posts
    5
    Provided Answers: 1
    Quote Originally Posted by myle View Post
    use the Groupby and Set the ModifiedDate to Last
    myle,
    Thanks for your suggestion.
    It doesn't appear to have any effect - that is if i'm understanding correctly. Does the following code reflect your suggestion?

    Code:
    SELECT TempTable.UniqueID, TempTable.ITEM, TempTable.PRICE, Max(TempTable.ModifiedDate) AS MaxOfModifiedDate
    FROM TempTable
    GROUP BY TempTable.ITEM, TempTable.PRICE,TempTable.UniqueID,TempTable.ModifiedDate;

  5. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    805
    Provided Answers: 2
    Hi

    I would create a sub query
    Code:
    SELECT TempTable.UniqueID,  Max(TempTable.ModifiedDate) AS MaxOfModifiedDate FROM TempTable TempTable GROUP BY UniqueID
    and then join this back to TempTable on UniqueID=UniqueID and MaxOfModifiedDate = ModifiedDate

    This would only return the required records!?

    HTH


    MTB

  6. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    snap MikeTheBike

    that work
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  7. #6
    Join Date
    Feb 2017
    Posts
    5
    Provided Answers: 1
    Quote Originally Posted by myle View Post
    snap MikeTheBike

    that work
    MikeTheBike,

    Thanks so much for your help. I'm a true novice but was really hoping to figure out your suggestion without asking you to spoon feed me. But not able to figure it out - at least not yet.
    Base on my interpretation of your suggestion, this is the code I have so far.

    Code:
    SELECT TempTable.UniqueID, TempTable.ProdItem, TempTable.ProdPRICE, Max(TempTable.ModifiedDate) AS MaxOfModifiedDate
    FROM TempTable INNER JOIN TempTable AS TempTable_1 ON (TempTable.MaxOfModifiedDate = TempTable_1.ModifiedDate) AND (TempTable.UniqueID = TempTable_1.UniqueID)
    WHERE TempTable IN
    (SELECT TempTable.UniqueID,  Max(TempTable.ModifiedDate) AS MaxOfModifiedDate FROM TempTable TempTable GROUP BY UniqueID)
    GROUP BY TempTable.UniqueID, TempTable.ProdItem, TempTable.ProdPRICE;
    Clearly, I'm doing something wrong.
    The error its giving me is "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field. (Error 3306)"

  8. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    805
    Provided Answers: 2
    Hi Again

    I indicated you needed to JOIN the sub query not use it as a criteria, something like this

    Code:
    SELECT TempTable.UniqueID, TempTable.ProdItem, TempTable.ProdPRICE, TempTable.ModifiedDate FROM TempTable INNER JOIN (SELECT TempTable.UniqueID,  Max(TempTable.ModifiedDate) AS MaxOfModifiedDate FROM TempTable GROUP BY UniqueID) AS MaxTempTable ON (MaxTempTable.MaxOfModifiedDate = TempTable.ModifiedDate) AND (MaxTempTable.UniqueID = TempTable.UniqueID)
    This is obviously untried, but it should be somewhere near!

    HTH


    MTB

  9. #8
    Join Date
    Feb 2017
    Posts
    5
    Provided Answers: 1

    Thumbs up

    Quote Originally Posted by MikeTheBike View Post
    Hi Again

    I indicated you needed to JOIN the sub query not use it as a criteria, something like this

    Code:
    SELECT TempTable.UniqueID, TempTable.ProdItem, TempTable.ProdPRICE, TempTable.ModifiedDate FROM TempTable INNER JOIN (SELECT TempTable.UniqueID,  Max(TempTable.ModifiedDate) AS MaxOfModifiedDate FROM TempTable GROUP BY UniqueID) AS MaxTempTable ON (MaxTempTable.MaxOfModifiedDate = TempTable.ModifiedDate) AND (MaxTempTable.UniqueID = TempTable.UniqueID)
    This is obviously untried, but it should be somewhere near!

    HTH


    MTB
    PREFECT! Thanks MikeTheBike!!!
    Just needed a slight adjustment. It was grouping on UniqueID - which is unique so it was reporting back all 9 items. I replaced that with ProdItem and it worked perfectly.

    Code:
    SELECT TempTable.UniqueID, TempTable.ProdItem, TempTable.ProdPRICE, TempTable.ModifiedDate
    FROM TempTable INNER JOIN (SELECT TempTable.ProdItem, Max(TempTable.ModifiedDate) AS MaxOfModifiedDate 
    FROM TempTable GROUP BY ProdItem)  AS MaxTempTable ON (TempTable.ProdItem = MaxTempTable.ProdItem) AND (TempTable.ModifiedDate = MaxTempTable.MaxOfModifiedDate);
    I really appreciate the help.
    Cheers,
    Phil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •