Errors with TOP 1 lookup on previous record

[Jossy]

Hi. I have a query I've built that produces three fields:

Rank - 1, 2, 3 4 etc (sorted in descending order)
ID1 - an identifier of a person, with duplicates
ID2 - an identifer of a second person, with duplicates

From this query I want to write a second query that selects both the first two fields and then creates a third field that looks for the previous ID2 of the current ID1 record.

Using SQL cobbled from a few places I was pretty successful with this:

SELECT ELO_ranked_lookup_atp_small.Rank, ELO_ranked_lookup_atp_small.ID1, (SELECT TOP 1, Dupe.ID2
FROM ELO_ranked_lookup_atp_small as Dupe
WHERE Dupe.ID1 = ELO_ranked_lookup_atp_small.ID1
AND Dupe.Rank < ELO_ranked_lookup_atp_small.Rank) AS PRIOR_ID2
FROM ELO_ranked_lookup_atp_small;

However, if the ID1 record is the first instance in the list then I get a blank as there is no previous record to lookup. Instead of the blank I want to record a value of 1500.

So I thought it was a case of a simple Iif statement as follows:

SELECT ELO_ranked_lookup_atp_small.Rank, ELO_ranked_lookup_atp_small.ID1, IIf((SELECT TOP 1, Dupe.ID2
FROM ELO_ranked_lookup_atp_small as Dupe
WHERE Dupe.ID1 = ELO_ranked_lookup_atp_small.ID1
AND Dupe.Rank < ELO_ranked_lookup_atp_small.Rank)<>"",(SELECT TOP 1, Dupe.ID2
FROM ELO_ranked_lookup_atp_small as Dupe
WHERE Dupe.ID1 = ELO_ranked_lookup_atp_small.ID1
AND Dupe.Rank < ELO_ranked_lookup_atp_small.Rank),1500) AS PRIOR_ID2
FROM ELO_ranked_lookup_atp_small;

However, whilst this now inserts the 1500 correctly, the other records for the third field come back as errors. I'm sure it's a simple fix but for the life of me I can't figure it out. Thanks in advance.

This follows on from a previous thread: https://www.access-programmers.co.uk/forums/showthread.php?t=306249

 

[Jossy]

Managed to figure this out using

ISNULL

:

SELECT ELO_ranked_lookup_atp_small.Rank, ELO_ranked_lookup_atp_small.ID1, IIf(ISNULL((SELECT TOP 1, Dupe.ID2
FROM ELO_ranked_lookup_atp_small as Dupe
WHERE Dupe.ID1 = ELO_ranked_lookup_atp_small.ID1
AND Dupe.Rank < ELO_ranked_lookup_atp_small.Rank)),1500,(SELECT TOP 1, Dupe.ID2
FROM ELO_ranked_lookup_atp_small as Dupe
WHERE Dupe.ID1 = ELO_ranked_lookup_atp_small.ID1
AND Dupe.Rank < ELO_ranked_lookup_atp_small.Rank)) AS PRIOR_ID2
FROM ELO_ranked_lookup_atp_small;