Get the max value of a column depending on the max of other column

[RaghdaAbdalla]

Hello .....
I writing sql statment on access 2007, all what I need to do is to get the max value of a column depending on the max of another column..

an exmaple of the data is as below:
Invoice No-------- Document no--------Document Date-----Value
1001115600188757 /// 5100002019//////13/12/2010////////-30469.50
1001115600188757 /// 5100002018//////13/12/2010////////30469.50
1001115600188757 /// 5100001968///// 13/12/2010//////// -30469.50

I need the sql to get Distinct invoice no with the latest document date and the biggest document no summing all the values

to get by the end:
Invoice No---------------Document no------Document Date-----Value
1001115600188757////////5100002019////////13/12/2010////////30469.50

if the example was:
Invoice No-------- Document no--------Document Date-----Value
1001115600188757 /// 5100002019//////13/12/2009////////-30469.50
1001115600188757 /// 5100002018//////13/12/2010////////30469.50
1001115600188757 /// 5100001968///// 13/12/2010//////// -30469.50

the answer to be:
1001115600188757 /// 5100002018//////13/12/2010////////30469.50

could any one help me in writing the sql statment for this????

[urtrivedi]

select * from invoice 
where document_no in (
                 select max(document_no) from invoice 
                 where document_date in (select max(document_date) from invoice)
                     )

[RaghdaAbdalla]

Thanks Urtrivedi for your reply but the point is that If I made select * I wont be able to make sum on the values
It seems I wrote NO other than And in by explanation ....sorry for that
an example to better explain my point:
Invoice No-------- Document no--------Document Date-----Value
1001115600188757 /// 5100002019//////13/12/2009////////10
1001115600188757 /// 5100002018//////13/12/2010////////20
1001115600188757 /// 5100001968///// 13/12/2010//////// 50

the answer to be:
1001115600188757 /// 5100002018//////13/12/2010////////80

[urtrivedi]

select invoice_no, max(doucment_no), max(document_date), sum(value) 
from invoice
group by invoice_no

[RaghdaAbdalla]

select invoice_no, max(doucment_no), max(document_date), sum(value) 
from invoice
group by invoice_no

this will get the Max number in all the rows not the max number for the max date :)

[urtrivedi]

I hope this would work.

select a.invoice_no, a.max_date,a.total, b.max_document_no 
from (
       SELECT invoice_no, max(document_date) max_date, sum(value) total
       FROM invoice 
       GROUP BY invoice_no
      ) a
inner join 
     (
        SELECT invoice_no,document_date max(document_no) max_document_no
        FROM invoice 
        GROUP BY invoice_no,document_date
     ) b on a.invoice_no=b.invoice_no and a.document_date=b.document_date