Matching Data from 1 column across many columns

[MoniqueR]

Hi All,

I am attempting to marry a list of assets to various staff members

I have table A - which is a list of assets by Asset ID, Type of Asset,
Listed Location and Cost Centre (this is the list of what we pay for)

I have table B - which is a list of Employee No.s, Employee Name, Location,
Asset 1, Asset 2, Asset 3, Asset 4 (this is a list of what people have on
their desks)

The assets could be PC's, monitors, Printers etc.

I need to be able to check if the asset in Table A matches any of the assets
listed in table B.

The returned value from table A would need to be type of asset, and the
location code, and from table B would be employee id, employee name, and
location

Eg:

Table A
Asset ID Asset Type Location Cost Centre
12345 Printer Level 6 P&C
54321 Monitor Level 12 P&C
67891 Desktop Level 9 Finance

Table B
Emp No. Emp Name Location Asset 1 Asset 2 Asset 3 Asset 4
ABC F Smith Level 12 67891 54321 12345

Returned Data:

ABC F Smith Level 12 Printer 12345 Level 6 P&C
ABC F Smith Level 12 Monitor 54321 Level 12 P&C

And so on.

I have linked Emp No to my main resources table (controls active resources)
And assets 1, 2, 3, & 4 from Table B to the Asset ID in table A

Everytime I add both tables into a query - I get no information. Have tried
using Excel with a Vlookup - but it becomes to cumbersome writing 4 look up
statements to get all the info I want.

Thanks

 

[Allen Browne]

Whenever you have repeating fields like that, you need to break the table
up. Lose all those fields. Instead create a related table, with fields:
Employee No
AssessID

This table links between the employees and the assets. There is now only one
field to choose.

That's going to be much easier and more efficient than a monster UNION query
that combines selected each asset from each column into one big column so
you can then select and dedupe them, and still not have a maintainable
result.