Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-12-2019, 04:56 AM   #1
Jossy
Newly Registered User
 
Join Date: Aug 2009
Posts: 31
Thanks: 2
Thanked 0 Times in 0 Posts
Jossy is on a distinguished road
Errors with TOP 1 lookup on previous record

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...d.php?t=306249


Last edited by Jossy; 08-12-2019 at 06:09 AM.
Jossy is offline   Reply With Quote
Old 08-12-2019, 09:50 AM   #2
Jossy
Newly Registered User
 
Join Date: Aug 2009
Posts: 31
Thanks: 2
Thanked 0 Times in 0 Posts
Jossy is on a distinguished road
Re: Errors with TOP 1 lookup on previous record

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;
Jossy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup the value from a previous record Jossy Queries 37 08-20-2019 04:17 AM
[SOLVED] How to use a previous Lookup to narrowdown the next Lookup Lucas79 Forms 4 08-22-2015 08:47 AM
Lookup at the previous record in the table bimmer5 Queries 18 01-27-2014 03:57 AM
[SOLVED] you cancelled the previous operation errors Totenkopf Modules & VBA 5 06-18-2006 12:40 PM
[SOLVED] How to lookup value in the previous record? maxkmv Modules & VBA 1 03-13-2002 02:03 PM




All times are GMT -8. The time now is 01:21 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World