Query maximum values

[Bryan Brassell]

I asked this before, but got an answer in sql - can anyone help with giving
me something I can use in the expression builder?

Tables are connected by CustNo field. Table2 has multiple records for each
customer CustNo. There is another field (dates) called Service_To. I need
to pull only the records with the most recent Service_To date.

As there are multiple customers it appears I can't simply sort and pull the
"top", unless I am misunderstanding something.


--
Regards,

Bryan Brassell
Padgett Business Services
281-897-9141

 

[[MVP] S.Clark]

Copy the SQL into the SQL Window, then click on the QBE view. Sorry,
dork humor.

Because you want to find the Top per group, the only way to do this in a
single query is with a subquery. There are other ways to do it, but they
would be more complicated than the subquery. Like multiple queries or VBA.

 

[KARL DEWEY]

I think this is what you want.
First query as totals set for Max on the Service_To date.
Second query left join Service_To to MaxOfService_To of the first query.