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!

You may need just the first query, adapted to your 2 fields to group on:

Most recent value

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?

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

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.

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

Perfect! You solved this perfectly!

Happy to help!

Thank you so very much. You really helped me get a project done!