Col 1 | Col 2 | Col 3 | Col 4 | Col 5 | Col 6 | Lookup |
John Smith 12345 HoS | Jane Simpson 3456 | Another Name 9876 | Hector Wilson 34523 | etc | etc | 12345 |
Jane Jones 53425 | Another Name 9876 | Kelly White 19282 | Adam Lewis 45455 | etc | etc | 45455 |
Hector Wilson 34523 | John Smith 12345 | Kelly White 19282 | Phil West 765655 | etc | etc | 12345 |
Adam Lewis 45455 | Jane Simpson 3456 | Jane Jones 53425 | Hector Wilson 34523 | etc | etc | 45455 |
<tbody>
</tbody>
I'm fairly new to Access and have a problem that I can't seem to work out on my own. Not really sure how to describe it so hope the title is ok.
I have a data set that contains (amongst other things) 6 columns that have lists of names with ID numbers and job role i.e. 1 record might be "John Smith 12345 Head of Sales". Often with brackets and other symbols thrown in.
I have a list of about 36 ID numbers that I want to look up across the 6 columns. I have done this before with a much smaller list of ID numbers (6) using nested iif's and "like".
However, now I have over 30 values I'm hoping there's a much quicker way to do this?
I don't mind if I have to look up to the 6 columns individually and then combine the results in to 1 column. I just don't want to nest an iif 30 odd times.
Someone suggested I use "in" but I can't work out how to do this in a query
Sample data above (I couldn't work out o to move it to the bottom)
Grateful for any help