Hlookup and Vlookup questions in Access Queries

[Tamrak]

Good morning,

I am trying to do the queries and I think it can be done in Access. Here are the data samples:

Table 1: Project_name, Reviewer, Analyst, Specialist, Approver
project 1, 125AA, 1347X, 8905E, 68092
project 2, 1347X, 7908E, 6008R, 1255A

(I have around 2,000 rows)

Table 2: Emp_ID, Name
125AA, Aaron B.
1347X, Bessy G.
8095E, Clark F.
68092, David O.
6008R, Edward J.
7908E, Frank D.
90824, George P.
(I have 500+ records)

I am looking to create queries that will produce the following results:

Project 1, Aaron B., Bessy G., Clark F., David O.
Project 2, Bessy G., Frank D., Edward J., Aaron B.

I think it is related to either Hlookup or Vlookup. I am concerned that I will create several tables in order to join them. Can anyone recommend simple queries that may produce the requested results? Thanks.

 

[HarleyQuinn]

Have a look at faq701-4233, it should give you what you're after.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Tamrak]

Thank you for your response. It is not what I am looking. I have four fields (reviewer, analyst, specialist and approver) and would like to replace each field with the name.

I have problems setting the relationship because each field can link to the value in the table 2.

I am uncertain whether I need to create 4 tables and join them together.

 

[PHV]

A starting point (SQL code):
SELECT Project_name, A.Name, B.Name, C.Name, D.Name
FROM (((Table1
INNER JOIN Table2 AS A ON Reviewer = A.Emp_ID)
INNER JOIN Table2 AS B ON Analyst = B.Emp_ID)
INNER JOIN Table2 AS C ON Specialist = C.Emp_ID)
INNER JOIN Table2 AS D ON Approver = D.Emp_ID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

Actually, your Table1 is not normalized. It should be:
Project_Name Processor(or whatever) Position thus -
Project1 125AA Reviewer
Project1 1347X Analyst
Etc.
Primary Key would be multifield Project_Name and Processor.

Now you can directly match that table to Table2 to get the names and then use HarleyQuinn's suggestion to concatenate the records.

Reason it is not normalized:
Let's say a project doesn't have an approver appointed let, or any of the other positions. Then you have blank fields throughout the table with the possibility of variable length records.
What happens when someone decides to add a pre-approver? Now the table structure changes and again some projects may or may not need this extra person. Again variable length records.
The table I presented doesn't have these problems. It doesn't matter it there is one person or a million in the process. It'll always be rectangular - normalized.

But it's up to you if you want a correct Access table or not.