Group By, Max & Last

I've got a table that has:

ID Postsector Effective Date Contents Area Buildings Area
1 AB1 1 01/01/10 10 6
2 AB1 1 01/04/10 8 10
3 NR2 2 01/01/10 12 13
4 NR2 2 01/04/10 8 8

etc, etc.

I want the latest Contents & Building Areas for each postsector.
i.e. Postsector Effective Date Contents Area Buildings Area
AB1 1 01/04/10 8 10
NR2 2 01/04/10 8 8

I tried a query that did Group By on Postsector & Max on Effective
Date.
I'm then forced to choose something for the Contents Area & the
Buildings Area.
But if I choose Last I don't get the last entry in the table but the
highest number.

Please can someone advise what the query should look like?

Thanks in advance.

Try one of the methods presented at
http://www.mvps.org/access/queries/qry0020.htm


Vanderghast, Access MVP


"Statsman" wrote in message
...
I've got a table that has:

ID Postsector Effective Date Contents Area Buildings Area
1 AB1 1 01/01/10 10 6
2 AB1 1 01/04/10 8 10
3 NR2 2 01/01/10 12 13
4 NR2 2 01/04/10 8 8

etc, etc.

I want the latest Contents & Building Areas for each postsector.
i.e. Postsector Effective Date Contents Area Buildings Area
AB1 1 01/04/10 8 10
NR2 2 01/04/10 8 8

I tried a query that did Group By on Postsector & Max on Effective
Date.
I'm then forced to choose something for the Contents Area & the
Buildings Area.
But if I choose Last I don't get the last entry in the table but the
highest number.

Please can someone advise what the query should look like?

Thanks in advance.

On Feb 22, 1:27*pm, "vanderghast" vanderghast@com wrote:
Try one of the methods presented athttp://www.mvps.org/access/queries/qry0020.htm

Vanderghast, Access MVP

"Statsman" wrote in message

...



I've got a table that has:

ID * Postsector * Effective Date * Contents Area * Buildings Area
1 * * *AB1 1 * * * * *01/01/10 * * * * * *10 * * * * * * * * * *6
2 * * *AB1 1 * * * * *01/04/10 * * * * * *8 * * * * * * * * * * *10
3 * * *NR2 2 * * * * *01/01/10 * * * * * *12 * * * * * * * * * * 13
4 * * *NR2 2 * * * * *01/04/10 * * * * * * 8 * * * * * * * * * * *8

etc, etc.

I want the latest Contents & Building Areas for each postsector.
i.e. Postsector * Effective Date * Contents Area * Buildings Area
* * * *AB1 1 * * * *01/04/10 * * * * * * 8 * * * * * * * * * * * 10
* * * *NR2 2 * * * *01/04/10 * * * * * * 8 * * * * * * * * * * * *8

I tried a query that did Group By on Postsector & Max on Effective
Date.
I'm then forced to choose something for the Contents Area & the
Buildings Area.
But if I choose Last I don't get the last entry in the table but the
highest number.

Please can someone advise what the query should look like?

Thanks in advance.- Hide quoted text -

- Show quoted text -

Many thanks, that works fine.