getting the Very Last Login from series of columns

[iainrkemp14]

Hi all,

I have a table that shows name, id number, and last login. It has 21 last login columns (with the more recent checks being the furthest to the right). If the person's last login was in LL4 (last login colum 4), 1-4 would hold dates, and 5-21 would be empty. If the last login was in LL15, 1-15 would hold dates and 16-21 would be empty. And so on.

I want to add a colum that shows the Very Last Login, that is to say the value from the most recent (furthest to the right) Last Login colum that holds a date.

Any ideas?

Cheers,

- Iain

 

[Golom]

If you had a normalized table this would be simple but with all these repeating fields its a real chore. First make a query that generates the normalized table that you should have like this.

   (Select ID, LL1 As [LastLogIn] From tbl Where LL1 IS NOT NULL)
   UNION ALL
   (Select ID, LL2 As [LastLogIn] From tbl Where LL2 IS NOT NULL)
   UNION ALL
   (Select ID, LL3 As [LastLogIn] From tbl Where LL3 IS NOT NULL)
   UNION ALL
  
   etc.

   (Select ID, LL21 As [LastLogIn] From tbl Where LL21 IS NOT NULL)

Save that as a query (call it qryLL for example)

Then

    Select A.ID, MAX(B.LastLogIn) As [LatestLogIn]
    From tbl As A INNER JOIN qryLL As B 
         ON A.ID = B.ID
    Group By A.ID