Display last column in which row contained data

[analyst1]

Hi there,

I have a table that looks like the following:
Name, Q1, Q2, Q3, Q4, Q5, Q6
ABC 3 , , , , , 2
CCC , , 4, ,5, 4

Sorry for the messing output, in essence, ABC has values for Q1 and Q6 only meanwhile CCC has values for Q2, Q3, Q4 only.

Now, Q2-Q5 are null values for ABC, and as such, I want a field that will display the last column in which the row did not have a null value. In other words, Last update field would read “Q6” for ABC, and “Q4” for CCC.

Is this possible in Access?



Any help is appreciated.

 

[Galaxiom]

It appers your table could be incorrectly structured. Try turning it ninety degrees.
I think the rows should be fields (columns) while the columns should be records (rows).

Access can then easily find the information.

 

[analyst1]

To paint a more clear picture, it isn't an actual table but rather a view. My DB will consist of a table that is routinely appended to, but will always remain with 5 fields. The view I created looks at how many absences they have in each quarter (1 of the 5 fields). I want to know if theirs a way for me to create a custom field in the query window that will display the last quarter someone was absent.

 

[gemma-the-husky]

just write a function to return the value you want

more complex than this, but this sort of thing

if not isnull(q5) then goto return5
if not isnull(q4) then goto return4
if not isnull(q3) then goto return3
if not isnull(q2) then goto return2
if not isnull(q1) then goto return1
goto return0 'noabsences