Max of Count and date query

[boycie]

Hi everyone,

I am trying to create a query that will return 1 row as follows;

I have a query with 2 fields: CountofSomething, AccDate

I want to return the max(Countofsomething), AccDate

I am getting the max for each date - I want the max count and the date for that field ie. one row (I am grouping the date field because I do not know what aggregate to use)

Any ideas?

thanks

 

[gemma-the-husky]

all you can do is find the date that has the max - then use that date to find the count for that date.

you cant get them in a query together.

i find it very strange that you can't use a dmax/dmin etc, and get some other info relating to the max row but you can't - even "expression" doesn't do it, unfortunately - you just get something like a "not part of an aggregate function error", I think

 

[MStef]

Send a some records (data) and tell what you
want as a result.

 

[boycie]

fCountD custaccDate

2 02/05/2003
16 06/05/2003
10 07/05/2003

I want to return:

16 06/05/2003


ie. 16 is the max of that column and I require the date with it

thanks

 

[MStef]

Look at "DemoCountMaxA2002.mdb" (attachment, zip).
run Query1 and see.

 

[boycie]

Thanks MStef - that works great

thanks also Dave for your reply