Query DMax

[domivax]

Hi Everybody,

I have the following table :

Table: tbl_Global

Id CountryId CountyName CountryDate


__________________________________________________ ___
1 1 United Kingdom 13.03.2014
2 1 United Kingdom 02.03.2011
3 1 United Kingdom 03.04.2010
4 2 France 01.02.2012
5 2 France 03.03.2014
6 2 France 09.09.2010
7 2 France 08.04.2008
8 3 Swizterland 09.08.2009
9 4 Belgium 15.12.2013
10 4 Belgium 11.08.2011
11 5 Spain 23.08.2011
12 5 Spain 25.04.2014


I have a problem with the function DMax.

I would like to obtain a query with one record by country with the more recent date (see following query result)

My function in the sql_Global: Result: DMax("CountryDate","tbl_Global","CountryId")


Query: sql_Global

Id CountryId CountryName CountryDate
__________________________________________________ ___
1 1 United Kingdom 13.03.2014
5 2 France 03.03.2014
8 3 Swizterland 09.08.2009
9 4 Belgium 15.12.2013
12 5 Spain 25.04.2014


and the result is the following:


Id CountryId CountryName CountryDate
__________________________________________________ ___
1 1 United Kingdom 25.04.2014
2 1 United Kingdom 25.04.2014
3 1 United Kingdom 25.04.2014
4 2 France 25.04.2014
5 2 France 25.04.2014
6 2 France 25.04.2014
7 2 France 25.04.2014
8 3 Swizterland 25.04.2014
9 4 Belgium 25.04.2014
10 4 Belgium 25.04.2014
11 5 Spain 25.04.2014
12 5 Spain 25.04.2014


Thank you very much for your help.
Best Regards,
Xavier

[amrut]

Use aggregate query and not DMax for this purpose. See here ( last example) http://www.techonthenet.com/sql/group_by.php and here too http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

[John_G]

Hi -

It might not be as easy as it looks - if you need to have the ID value included in the query results, a simple aggregate query as suggested by Amrut won't work. Including ID in the "Group By" clause effectively results in no grouping at all, since ID is unique in each record.

John

[domivax]

Thank you both for your answers,

But John, you’re right and it would have been too easy, I also need in my request for my ID records to use them in another query.

Best Regards,