complicated select query problem

[dharkangel]

Hi everyone,

I have a query that almost works but I don't know how to do one final part. I need to pull the [Assigned To] field and match each Distinct [Assigned To] entry with its proper 'Role' which is found in the 'UserRoleData' table. So I need the output to look like this:

Assigned To: Role:
Smith, Jones EUM
Darvey, Tom EUM
Peters, Bob DIR
etc...

the 'UserRoleData' table has each employee and their respective role like this:

Resource Name Role
Smith, Jones EUM
Darvey, Tom EUM
Peters, Bob DIR

The problem is that the QPTActions table contains all tasks assigned to any user. So it will be a many to one relationship. It does not include their role which is why I have to use the 'UserRoleData' to find the Role. This is how the 'QPTActions' table looks:

Assigned To
Smith, Jones
Smith, Jones
Darvey, Tom
Darvey, Tom
Darvey, Tom
Peters, Bot

Here is my query I am building:
SELECT DISTINCT (QPTActions.[Assigned To]) AS Assignee, UserRoleData.Role
FROM QPTActions, UserRoleData
WHERE DateValue(QPTActions.[Entry Date])<=DateValue('1/2/2006') And DateValue(QPTActions.[Close Date])>DateValue('1/2/2006');

 

[Melagan]

I'm no expert but it looks to me like this query might need an INNER JOIN? (typed, not tested, and just guessing at that)

SELECT DISTINCT q.[Assigned To] as Assignee, u.Role

FROM QPTActions AS q 

INNER JOIN UserRoleData AS u ON u.[Resource Name] = q.[Assigned To]

WHERE DateValue(q.[Entry Date])<=DateValue('1/2/2006') And DateValue(q.[Close Date])>DateValue('1/2/2006');

~Melagan
______
"It's never too late to become what you might have been.

 

[dharkangel]

thank you this worked!

 

[Melagan]

My pleasure - I'll bet the idea of JOINS has opened a whole new world for you!

~Melagan
______
"It's never too late to become what you might have been.

 

[lespaul]

read this article for a better understanding of what joins do and the different kinds.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design