group by

[Nancy]

How do I use group by for the following:
data has 3 records:
jo 8/2003 48004
jo 9/2003 48003
jo 7/2003 48009

I want the middle record to be my final answer, but if I
do a group by name, max on date, max on zip code, my final
answer will be "Jo 9/2003 48009" instead of "Jo 9/2003
48003"

Please help!

Thanks

 

[Brian Camire]

You might try a query whose SQL looks something this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Date] =
(SELECT
Max([Self].[Date])
FROM
[Your Table] AS [Self]
WHERE
[Self].[Name] = [Your Table].[Name])

 

[Van T. Dinh]

See the techniques described in The Access Web article:

<http://www.mvps.org/access/queries/qry0020.htm>

HTH
Van T. Dinh
MVP (Access)

 

[Nancy]

This is great ... I tried it and it works ... Thanks

-----Original Message-----
You might try a query whose SQL looks something this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Date] =
(SELECT
Max([Self].[Date])
FROM
[Your Table] AS [Self]
WHERE
[Self].[Name] = [Your Table].[Name])

How do I use group by for the following:
data has 3 records:
jo 8/2003 48004
jo 9/2003 48003
jo 7/2003 48009

I want the middle record to be my final answer, but if I
do a group by name, max on date, max on zip code, my final
answer will be "Jo 9/2003 48009" instead of "Jo 9/2003
48003"

Please help!

Thanks

.

 

[Van T. Dinh]

Must be the Regional Settings in your PC as I get these articles in English!