Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2012
    Posts
    11

    Unanswered: Access 2010 Earliest Date

    I need to find the earliest date that each product was received at each store in my retail chain.

    I have a receiving table, and a product table

    The fields in each table are:

    PRODUCT TABLE
    product_number
    product_name

    RECEIVING TABLE
    product_number
    store_number
    receiving_date

    ...the product_number field is common between the two tables and can be joined.



    Can anyone help? Thank you!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You may need just the first query, adapted to your 2 fields to group on:

    Most recent value
    Paul

  3. #3
    Join Date
    Jul 2012
    Posts
    11
    Thank you....I feel like I tried this....

    SELECT articles.article, Min([purch]![ym_pu]) AS Expr1
    FROM articles INNER JOIN purch ON articles.article = purch.art_pu;

    but I am getting this error

    'You tried to execute a query that does not include the specified expression 'article' as part of an aggregate function.'

    articles is the product table
    article is the product name in the product table

    purch is the receiving table
    art_pu is the product name in the product table
    ym_pu is the receiving date in the product table

    What am I missing in the query code?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You missed the GROUP BY clause. Try

    SELECT articles.article, Min([purch]![ym_pu]) AS Expr1
    FROM articles INNER JOIN purch ON articles.article = purch.art_pu;
    GROUP BY articles.article
    Paul

  5. #5
    Join Date
    Jul 2012
    Posts
    11
    Here is what I have

    SELECT articles.article, Min([purch]![ym_pu]) AS Expr1
    FROM articles INNER JOIN purch ON articles.article = purch.art_pu;
    GROUP BY articles.article

    but I received the following error

    'The LEVEL clause includes a reserved word or argument that is mispelled or missing , or the punctuation is incorrect.'

    I have no clue what that means.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    My bad, missed the semi-colon:

    SELECT articles.article, Min([purch]![ym_pu]) AS Expr1
    FROM articles INNER JOIN purch ON articles.article = purch.art_pu
    GROUP BY articles.article
    Paul

  7. #7
    Join Date
    Jul 2012
    Posts
    11
    Perfect! You solved this perfectly!

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help!
    Paul

  9. #9
    Join Date
    Jul 2012
    Posts
    11
    Thank you so very much. You really helped me get a project done!

Posting Permissions

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