Max Date Query

[bd528]

Hi,

I have a query returning 3 fields :-

Num - MaxEndDate - EndType
0001 - 01/03/12 - E
0001 - 23/02/12 - N
etc
etc


How can I change my SQL below to ensure that only the the record with the highest date is returned per "Num"

SELECT tblInvoice.InvNo, Max(tblInvoice.EndDate) AS MaxOfEndDate, tblInvoice.EndType
FROM tblInvoice
GROUP BY tblInvoice.InvNo, tblInvoice.EndType;

Thanks in advance.

 

[pbaldy]

Drop EndType from the query. If you want the EndType of the last record:

http://www.baldyweb.com/LastValue.htm

 

[bd528]

Hi,

Based on the link you sent, I created 2 queries :-

SELECT tblInvoice.InvNo, Max(tblInvoice.EndDate) AS MaxOfEndDate
FROM tblInvoice
GROUP BY tblInvoice.InvNo;

and

SELECT tblInvoice.InvNo, tblInvoice.EndType
FROM tblInvoice 
INNER JOIN qryRTypePart1 
ON (tblInvoice.Enddate = qryRTypePart1.MaxofEndDate) 
AND (tblInvoice.InvNo = qryRTypePart1.InvNo);

The first works fine, but the second is bringing through additional enrties per InvNo. Any ideas where I'm going wrong?

 

[pbaldy]

One possibility that comes to mind is that an InvNo may have multiple records on the same date. Is that possible? Can you post a sample of the db?

 

[bd528]

Yes, you're right, there were multiple records. Also, I didn't have the query set as a Totals query.

Is there a quick way to force a result of just one result per InvNo? It is not necessary to do any checking on the result brought through.

 

[pbaldy]

You'd have to make the second query a totals query too, with a Max or something on the second field.

 

[bd528]

You'd have to make the second query a totals query too, with a Max or something on the second field.

I went with MAX, and added some extra checking for null values.

Thanks a lot for your help, and the useful website.

Solved

 

[pbaldy]

Happy to help!