Complex Query in Access 1

[JPimp]

i am trying to create a query where I am using Table 1 and Table 2. Table 1 has all my data, and table 2 has a list of users and email addresses. This is the code I have so far:

SELECT CFM00348.[Audit#], CFM00348.[Due Date], CFM00348.Name, CFM00348.[Name 2], CFM00348.[Name 3], CFM00348.AUDIT_DATE, CFM00348.ENDDATE, [Manager/Supplier].UserName

FROM CFM00348 INNER JOIN [Manager/Supplier] ON 

CFM00348.Name = [Manager/Supplier].Name

WHERE (((CFM00348.Name) Is Not Null) AND 

((CFM00348.ENDDATE) Is Null));

Basically, I can have up to 3 names in CFM00348, i.e. CFM00348.Name, CFM00348.Name2, CFM00348.Name3. I want to be able to fill 3 instances of [Manager/Supplier].Username to equal CFM00348.Name, Name2, etc. However, all I can get is CFM00348.Name = [Manager/Supplier].Username, do I need to create a [Manager/Supplier].Username2, and [Manager/Supplier].Username3 and link it that way? I thought there was another way to do this, but perhaps not?

Just call me Spunky

 

[PHV]

What about this ?
SELECT C.[Audit#], C.[Due Date], C.Name, C.[Name 2], C.[Name 3], C.AUDIT_DATE, C.ENDDATE, M.UserName
FROM CFM00348 AS C INNER JOIN [Manager/Supplier] AS M ON
C.Name = M.Name OR C.[Name 2] = M.Name OR C.[Name 3] = M.Name
WHERE C.ENDDATE Is Null;


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

 

[JPimp]

That seems to do the trick, awesome!! Thank you a bunch!

Just call me Spunky