Access, Sub Query, Group By, Max Date

[HardWorking]

I am using a recreated query from the Northwind DB which is similar to the query that I need an answer on.
I am trying to run a Group By SubQuery and use it's results for the outer query.
My goal is to have the records form my inner query control the total number of records from my outer query.

From Northwind DB
Orders Table has 830 records
My inner query shows 89 records if run by itself.
When I combine my 2 queries I get results showing 159 records
How do I get the inner query to control the number of records for my outer query?
Also, in my actual query I get "Invalid argument to function" with no results, but when I recreate this query using the Northwind
DB I do not get the error, but the total records do not match my records from the inner query.

Here is my query:

SELECT *
FROM Orders

WHERE (((Orders.OrderDate) In

(SELECT Max(Orders.OrderDate)
FROM Orders
GROUP BY [Orders].[CustomerID]
)));

 

[pbaldy]

Sounds like you want something like this:

Most recent value

You can do it with a subquery, but you need to join on customer and date.

 

[CJ_London]

Paul has provided the solution, but thought you might like an explanation of this

When I combine my 2 queries I get results showing 159 records

lets say you have 3 records

custID....Date
1..............1/1/2020
1...............2/2/2020
2..............1/1/2020

your inner query is returning 1/1/2020 (max for custID 2) and 2/2/2020 (max for custID 1)

and you are asking in your outer query for date equal to one of those two dates - so all 3 records will be returned

The other thing to be aware of when using subqueries is you have to use aliases - otherwise the sql compiler will treat them the same i.e. use

SELECT Max(Orders.OrderDate)
FROM Orders AS Tmp
GROUP BY [Orders].[CustomerID]

 

[HardWorking]

Thank you for replying so quickly. I played around with what you sent and I made some mistakes, but eventually I got it. What you said solved my problem. Thank you very much Paul.

 

[HardWorking]

CJ thanks for the extra information. I appreciate all the help I can get.

 

[pbaldy]

Thank you very much Paul.

Happy to help!