spent an afternoon doing this and thought I can get some help.
Ok I have a table HeaderTable where I have a field called HeaderInfo.
The HeaderInfo field is a string type and I use that field to trim a ReportingPeriod from it .Then for each row in that table with the result from the first query I would like to populate another field in the same table HeaderTable .
I use the following update with self join to do so:
INNER JOIN HeaderTable AS HeaderTable_1
ON HeaderTable.ID = HeaderTable_1.ID
SET HeaderTable.ReportingPeriod = (select mid(HeaderInfo,39,8) from HeaderTable);
It looks ok to me but whenever I click the update button it doesn`t seem to do anything it just displays the query`s design view.
The ID field in the table is a primary key.
Tried removing the Primary key and ran the query again, still no help.
I am sure something isn`t as it should be .
Will appreciate any help
Thanks in advance