problems with IIF logic

[ScottishFencer]

I am having difficulty with the logic for a query.

I have a field (date) which records a certification expiry date. This field is tied to an assessment that is done yearly. I am in a position where we are rolling out the next years assessments and I need to make sure that the sheet handed to the assessors has the expiry date on it. There can be 3 types of entry for this field: a null (new member or new assessment), a date (for last years assessment) or a date for the lucky few who have already been assessed.

So for example:

SOPANo, txtCertTo, txtAssID
001A, , 1048
002, , 1063
002, 30/06/2004, 891
002, 30/06/2005, 926
009, , 1076
009, 30/07/2006, 677
009, 31/08/2005, 110
011, 31/01/2006, 344
011, 31/03/2007, 759
011, , 1433

The logic would have to go something like this:

IF txtcerto is NULL then check for previous assessments,
IF there are none then display NULL else display last txtcerto date.

AS you can see I've been thinking of an IIF nested loop but I can't get my head round the logic. I'm errr going in circles...

Any help would be appreciated.

 

[PHV]

With your posted example what is the expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ScottishFencer]

Ah sorry,

sopano, txtcertto
001a, NULL
002, 30/06/2005
009, 30/07/2006
011, 31/03/2007

assessment ID is not really important - I just thought it might prove useful for ranking the cert dates.

 

[PHV]

You wanted this ?
SELECT soprano, MAX(txtcertto) FROM yourTable GROUP BY soprano

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bboffin]

Why not eliminate any earlier assignments by using the Max function to return only the most recent txtcertto?

SELECT SOPANo, max(txtcertno) FROM MyTable
GROUP BY SOPANo

Bob Boffin

 

[ScottishFencer]

Ummm Thanks guys.

I honestly thought that I had already tried that. It looks ok.

Feeling a bit embarassed at the moment ...