Return the Column Name for row with last non-null value

Question

User234494117 posted

 

I have a Table, which contains a list of league players. Each season, we record their Points per Dart. Their total PPD for that season is stored in other tables and extracted through other queries, which in turn are imported to the master table "Player History" at the end of the season for use as historical data.

The current query retrieves each players PPD for each season they played, when they played last, and how many seasons played.

The code for Last season Played has become too long and unstable to use. it was originally created, and split into two separate columns because a single SQL was to long. (LSP1) and LSP2) which work, but as I add seasons, Access does not like the length of code.

In short, i need to find a more simple code that will look at each row, and look in that row for the last non null cell and report which column that last non null value is in.

So if a player played seasons 30 & 31, but did not play 32..but did play 33, the Column with the code should be titled Last Season Played, and for that Player, it would state "33" in that cell, indicating that this player last played season "33"

I will provide both tables and the query..

[e-mail]

Please help

Answer 1

User-1780053933 posted

Hello qtrmile69, not sure what the equivalent function would be for this in MSACCESS, It's probably the same and if not I'm certain it exist in some form.  In TSQL you can use the functions MIN(<column>) or MAX(<column>) to pull the corresponding lowest/highest value from a table column.  So one possibility for you might be to use a sub-query to extract this like so:

SELECT MAX(intSeason) FROM tblPlayerSeasons WHERE intPlayerID=@intPlayerID

Hope this helps.

Answer 2

User234494117 posted

I would like to send you a copy of the DB so you can look at it !!

when you have a second ...send an email to [e-mail]...and I'll reply with DB...  Thanks again