Update on keys empty columns, identical keys, most recent date
I have a table that will everyone year or so have information loaded into it. We have hardcorded certain fields (line,type,quality) which is not found in the raw data being loaded into the table. As you see below the line, type and quality are all empty for 2015. How can i Update the 2015 row with the line, type and quality from the most recent year with the same account name and type?
as_of account name line type quality 2014 1 steve 1 medium medium 2013 1 steve 1 big wide 2015 1 steve 2014 2 dave 2 small narrow 2015 2 dave 2014 3 carl 3 medium medium 2015 3 carl
My update SQL statements are returning "must use an updateable query"
So, I was able to fix this, heh.
I had to select the as_of date in a combo box and select all dates less than the date I wanted to be updated, storied in a view.
I then Selected the max date of all results where the account name was the unique identifier, this returned the most recent record with line, type and quality entries, this is stored as a table.
I was then able to update from the created table to the main table the line type and quality values.
Geeze, that's a hassle, especially in Access.
Anyone know a better way? Thanks