I have two tables named job (parent) and Interface (child) related with jobID = jobID (one-to-many) relationship.
Now, in job table I have a field "art date" . I want this field to calculate or get the value of field "scandate" in Interface table with the condition : record's "code" field value starts with "2". I also want only the first record matching this criteria.
I tried with the following SQL query but doesn't seem to work:
ExecuteSQL ( "SELECT scandate FROM Interface I JOIN job J ON I.jobID = J.jobID WHERE Left(I.code; 1) = '2' " ; ""; "")
I hope there is an easier way to achieve this.