Complex query

[Marina]

I am trying to put together a query, from two tables:

Table 1 - USERS

Field1: UserID (Integer)
Field2: FirstName (Text)
Field3: Lastname (Text)

Table 2 - PROJECTS

Field1: ____
Field2: ____
Field3: Handler (Integer)
Field4: Reviewer(Integer)
Field5: TestedBy (Integer)

Fields 3, 4 and 5 in table PROJECTS reference Field 1 (UserID) in USERS. I
need to combine these two tables and create 3 fields: Handler, Reviewer and
TestedBy, each of which needs to be a combination of First and Last Name
from USERS...

Thank you in advance for your help!

 

[John Viescas]

Include three copies of USERS on your query grid and one copy of Projects.
Eliminate any linking lines that Access draws (if any) and then link the
first from UserID to Handler, the second from UserID to Reviewer, and the
third from UserID to TestedBy. Access should name the three copies of USERS
as USERS, USERS_1, and USERS_2. On the query grid create fields:

HandlerName: Users.FirstName & " " & Users.LastName
ReviewerName: Users_1.FirstName & " " & Users_1.LastName
TestedByName: Users_2.FirstName & " " & Users_2.LastName

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Tom Ellison]

Dear Marina:

This is the ideal place to introduce the concept of "aliasing" a
table. You will need 3 independent instances of PROJECTS to do this,
since you will join on it 3 different ways.

SELECT P.Field1, P.Field2,
H.FirstName AS HandlerFirstName, H.LastName AS HandlerLastName,
R.FirstName AS ReviewerFirstName, R.LastName AS ReviewerLastName,
T.FirstName AS TestedByFirstName, T.LastName AS TestedByLastName
FROM PROJECTS P
INNER JOIN USERS H ON H.UserID = P.Handler
INNER JOIN USERS R ON H.UserID = P.Reviewer
INNER JOIN USERS T ON H.UserID = P.TestedBy

After you enter this, you can also view it in design view and see how
you can also create this through the Design Grid interface.

You would need to put in actual column names where I have Field1 and
Field2, or you could eliminat them.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[John Vinson]

Fields 3, 4 and 5 in table PROJECTS reference Field 1 (UserID) in USERS. I
need to combine these two tables and create 3 fields: Handler, Reviewer and
TestedBy, each of which needs to be a combination of First and Last Name
from USERS...

Create a query by adding the PROJECTS table to the query window, then
add the USERS table *three times* - Access will alias the second and
third tablenames by adding _1 and _2 to the name. Then link the first
instance to HandlerID, the second to ReviewerID, and the therd to
TesterID. You can then put calculated fields like:

Handler: [USERS].[FirstName] & " " & [USERS].[LastName]
Reviewer: [USERS_1].[FirstName] & " " & [USERS].[LastName]
etc.