So here is my dilemma: I have one table that has cost center numbers with the associated disciplines, in another table I have unique ID's with the disciplines as field names.
Example: Table1
| Cost Center | Discipline |
|---|---|
| 00001 | Project |
| 00002 | Design |
Table2
| Unique ID | Project | Design |
|---|---|---|
| CE-0001-01 | John Doe | Jane Smith |
| CE-0002-01 | Rodger Plant | Alvin Chip |
What I am looking to do is compare the discipline data in Table1 with the all the header labels in Table2 and if they match have it return in a query
Query
| Unique ID | Discipline | Discipline Lead |
|---|---|---|
| CE-0001-01 | Project | John Doe |
| CE-0002-01 | Design | Alvin Chip |
| CE-0001-01 | Design | Jane Smith |
| CE-0002-01 | Design | Rodger Plant |
A little more info: i am converting this from an excel file with VLookups and what i am trying to replicate is this formula:
=VLOOKUP(G2,'Disc Lead Lookup'!A:AC,MATCH(H2,'Disc Lead Lookup'!$A$4:$AC$4,0),FALSE)
where G2 is the unique ID, H2 is the discipline A:AC is the table the VLookup is looking into and A4:AC4 is the header names
Thanks
Russ
CodePudding user response：
A UNION query can rearrange Table2 fields to normalized structure.
SELECT [Unique ID], "Project" AS Discipline, Discipline AS [Design Lead] FROM Table2
UNION SELECT [Unique ID], "Design", Design FROM Table2;
Add UNION SELECT line for each additional discipline field.
There is no query builder/designer for UNION - must type or copy/paste in SQLView. There is a limit of 50 SELECT lines. The first line defines data types and field names.
If you want to extract the Cost Center value from Unique ID, use string manipulation functions in an expression Mid([Unique ID], 4, 4) AS [Cost Center] or Val(Mid([Unique ID], 4, 4)) AS [Cost Center].
Strongly advise not to use space in naming convention.