Errors with TOP 1 lookup on previous record (1 Viewer)

Jossy

Registered User
Joined
Aug 24, 2009
Messages
32
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:

Code:
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:

Code:
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
 
Last edited:

Jossy

Registered User
Joined
Aug 24, 2009
Messages
32
Managed to figure this out using
Code:
ISNULL
:

Code:
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;
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom