Results 1 to 3 of 3
  1. #1
    Join Date
    May 2014
    Posts
    1

    Unanswered: Date Price Query/Report?

    I am VERY new to databases, although have significant experience in Excel and use it thoroughly. It is apparent that my spread sheets are becoming too advanced and therefore i am looking at moving over to database. The majority of this has been simple but i have come against a snag. I am certain that others will have had this problem, but i cannot seem to search for the same in forums, at least not one that has been answered...

    I have a table as follows

    PLU Price Date
    200002 10.50 01/03/2014
    200003 11.25 01/03/2014
    200004 16.75 08/03/2014
    200002 9.50 08/03/2014

    I would like a report to display all PLUs quoted, but ONLY returning the price with the most recent value. So in this case the report would show all info bar the first row, as the same PLU number is repeated in row 4, with an updated price, which in this case is lower.

    Hope you can help!

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    2 queries..
    Q1. In your query, turn on summation..and the the MAX date of the PLU..
    SELECT PLU, max(DateEarned) AS MaxOfDate
    FROM tTable
    GROUP BY PLU;

    Q2. Uses Q1, link PLU, to get the price.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution for the query:
    Code:
    SELECT a.PLU, a.Price, a.Date
    FROM (SELECT Tbl_Prices.PLU, Tbl_Prices.Price, Tbl_Prices.Date
            FROM Tbl_Prices) AS a 
    INNER JOIN (SELECT Tbl_Prices.PLU, Max(Tbl_Prices.Date) AS [Date]
                  FROM Tbl_Prices
              GROUP BY Tbl_Prices.PLU) AS b 
    ON (a.PLU = b.PLU) AND (a.Date = b.Date);
    Note: You should not use Date as the name of a column because it's a reserved word: Access 2007 reserved words and symbols - Access
    Quote Originally Posted by ranman256 View Post
    2 queries..
    Q1. In your query, turn on summation..and the the MAX date of the PLU..
    SELECT PLU, max(DateEarned) AS MaxOfDate
    FROM tTable
    GROUP BY PLU;
    This woul yield:
    Code:
    PLU	MaxOfDate
    ------------------
    200002	8/03/2014
    200003	1/03/2014
    200004	8/03/2014
    What do you do with the price?
    Have a nice day!

Tags for this Thread

Posting Permissions

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