Lookup Multiple Values across Multiple Columns and return the value in a new column

[CStenner]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Col 1
[/TD]
[TD]Col 2
[/TD]
[TD]Col 3
[/TD]
[TD]Col 4
[/TD]
[TD]Col 5
[/TD]
[TD]Col 6
[/TD]
[TD]Lookup
[/TD]
[/TR]
[TR]
[TD]John Smith 12345 HoS
[/TD]
[TD]Jane Simpson 3456
[/TD]
[TD]Another Name 9876
[/TD]
[TD]Hector Wilson 34523
[/TD]
[TD]etc
[/TD]
[TD]etc
[/TD]
[TD]12345
[/TD]
[/TR]
[TR]
[TD]Jane Jones 53425
[/TD]
[TD]Another Name 9876
[/TD]
[TD]Kelly White 19282
[/TD]
[TD]Adam Lewis 45455
[/TD]
[TD]etc
[/TD]
[TD]etc
[/TD]
[TD]45455
[/TD]
[/TR]
[TR]
[TD]Hector Wilson 34523
[/TD]
[TD]John Smith 12345
[/TD]
[TD]Kelly White 19282
[/TD]
[TD]Phil West 765655
[/TD]
[TD]etc
[/TD]
[TD]etc
[/TD]
[TD]12345
[/TD]
[/TR]
[TR]
[TD]Adam Lewis 45455
[/TD]
[TD]Jane Simpson 3456
[/TD]
[TD]Jane Jones 53425
[/TD]
[TD]Hector Wilson 34523
[/TD]
[TD]etc
[/TD]
[TD]etc
[/TD]
[TD]45455
[/TD]
[/TR]
</tbody>[/TABLE]
Hi

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

 

[Joe4]

However, now I have over 30 values I'm hoping there's a much quicker way to do this?

Your have a data structure issue. The issue is that your data is not normalized. De-normalized data can make seemingly simple tasks much more difficult and complex to do.
Do you have any ability to reorganize your data?

In a well-designed Access table (which would follow the rules of normalization), the following would be true:
- You would not have multiple pieces of data in the same field (names, titles, and codes would all be separate fields)
- You would not have repeating field types going across a single record
- You would not have repeating sets of fields (i.e. if the same name and title appear in multiple records, it would be better to have a separate employee table that lists all employees just once with their details, and has an ID field, then you could refer to them in other tables by the ID field)

So, the proper structure for your data table would only have one field for the name or ID, not multiple ones going across.

If you are unable to clean-up or change the data structure, then I think it would be easier to attach this problem in Excel rather than Access. Working with de-normalized data in Access can be very difficult, which is why it is recoemmended to normalize it.

See here for details on normalization: https://support.microsoft.com/en-us/help/283878/description-of-the-database-normalization-basics

 

[CStenner]

Hi

Thanks for you response. This is the way the data exports from the system. I'm unable to change that currently but can look at tidying it up once it's out. The 6 columns are a hierarchy structure so each row is an employee and then there are 6 fields to show the management structure above them.

If the data was cleaner, let's say each column contained just the ID I want to use, surely I'd still be left with the same issue of how to look across the management structure and pull out the people I want?

 

[Joe4]

What I would probably do is create a Function to extract the 5 digit ID from each record, and include it in a calculated field in a query.
Then create a table that has all the values that you are looking for (one ID per record).
Then, create a mtached query which joins the above query and table on the ID fields. This should return all the records you want.

If there are just 6 columns of people, one way to "normalize" the data a bit is to create a UNION query which converts them, i.e.

SELECT [Column1]
FROM [Table1]
UNION
SELECT [Column2]
FROM [Table1]
UNION
...
UNION
SELECT [Column6]
FROM [Table1];

Then, use this as basis for the first query above, where you are extracting the ID.

Here is a write-up on Union Queries, if you are interested: https://support.office.com/en-us/ar...e-result-1f772ec0-cc73-474d-ab10-ad0a75541c6e