Home > Back-end >  trying to compare data in one recordset to the column name of another MS Access
trying to compare data in one recordset to the column name of another MS Access

Time:06-18

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.

  • Related