I am trying to write a formula within my make table query (I'm a complete novice so gradually learning!). In this one particular field I would like to be able to combine data from 3 different fields in my input file. In doing so I need to also concatenate and change the wording. I've attached a file to explain what I mean (I've changed the actual data for data protection reasons).
So if in field1 it said Example1, Example 4 I would want to query to change this to 1A,1R. However, if in field1 it said Example1,Example2 AND in Field2 it said Example2,Example 6, Example7 AND in field 3 it said Example2 I would want to query to turn it into 1A,1S,2S,2C,2D,3S.
So there are a lot of variations and I've tried using an IIF statement but there are just too many variables.
Any ideas?Access Example.txt
Thanks!