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