I have 2 tables, one with records for staff shift data (tbl:duty). The other table (tbl:shiftdefinition) has shiftname and their definitions.
The 'duty' table has a field 'duties' and the records will have multiple characters in this field. The 'shiftdefinition' table has a field 'dutycode' with single character and another field as 'definition'. I need to join this 2 tables on the 'shiftname' and 'dutycode' to get all records from 'duty' table with the definition from the 'shiftdefinition' table.
left(duty!shiftname),1) will be always like left(shiftdefinition!dutycode,1).
I tried a formula in a select query as below:
Expand|Select|Wrap|Line Numbers
- GetDef: IIf(Left([duty]![shiftname],1) like Left([shiftdefinition]![dutycode],1) & "*",[shiftdefinition]![definition],"")
Please guide me