Join Fields from 2 tables

[rajeevs]

Hi All
I have 2 tables, one with records for staff shift data (tbl:duty). The other table (tbl:shiftdefin ition) has shiftname and their definitions.
The 'duty' table has a field 'duties' and the records will have multiple characters in this field. The 'shiftdefinitio n' 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 'shiftdefinitio n' table.
left(duty!shift name),1) will be always like left(shiftdefin ition!dutycode, 1).
I tried a formula in a select query as below:

GetDef: IIf(Left([duty]![shiftname],1) like Left([shiftdefinition]![dutycode],1) & "*",[shiftdefinition]![definition],"")

But not getting the expected result. I tried all 2 types of joins.
Please guide me

[NeoPa]

I would strongly advise you to strip that all out and start again after reading Database Normalisation and Table Structures.

You've been working in Access for over ten years so I'm very surprised to see such a question from you. And after 170 posts still posting code without the [CODE] tags.