By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,701 Members | 1,966 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,701 IT Pros & Developers. It's quick & easy.

Join Fields from 2 tables

P: 171
Hi All
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
  1. 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
Apr 28 '18 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 15k+
P: 31,489
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.
Apr 28 '18 #2

Post your reply

Sign in to post your reply or Sign up for a free account.