Prev: iif statement what is wrong with this expression
Next: Query to find outliers
From: Tina S on 23 Mar 2010 15:12
I am trying to build a report about reviews done on employees. We have
Managers assgined to Employees that do reviews. Sometimes we have manager
who are NOT assigned to an employee do a review. I want to be able to find
these records.

So I need the records for when a non-assigned manager reviews a non-assigned
employee.

Employee 1 assigned to Mgr 1
Employee 1 reviewed by non assigned Mgr 2
From: Jeff Boyce on 23 Mar 2010 15:16
"how" depends on "what" ... and we don't know what data you're working with.

More info, please...

(for example, what tables do you find the data in?)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Tina S" <TinaS(a)discussions.microsoft.com> wrote in message
news:D3CCF9D3-1ABC-4BFA-B032-A18F0F6D6AB3(a)microsoft.com...
>I am trying to build a report about reviews done on employees. We have
> Managers assgined to Employees that do reviews. Sometimes we have manager
> who are NOT assigned to an employee do a review. I want to be able to
> find
> these records.
>
> So I need the records for when a non-assigned manager reviews a
> non-assigned
> employee.
>
> Employee 1 assigned to Mgr 1
> Employee 1 reviewed by non assigned Mgr 2


From: KARL DEWEY on 23 Mar 2010 15:33
I am assuming that all review records are in a single table --
SELECT FName, LName, EmployeeID, tblEmployeeReview.Reviewer, [XX].Reviewer
FROM (tblEmployee LEFT JOIN tblEmployeeReview ON
tblEmployee.EmployeeID = tblEmployeeReview.EmployeeID) LEFT JOIN
tblEmployeeReview AS [XX] ON tblEmployee.EmployeeID = [XX].EmployeeID
WHERE tblEmployeeReview.Reviewer <> [XX].Reviewer;

--
Build a little, test a little.


"Tina S" wrote:

> I am trying to build a report about reviews done on employees. We have
> Managers assgined to Employees that do reviews. Sometimes we have manager
> who are NOT assigned to an employee do a review. I want to be able to find
> these records.
>
> So I need the records for when a non-assigned manager reviews a non-assigned
> employee.
>
> Employee 1 assigned to Mgr 1
> Employee 1 reviewed by non assigned Mgr 2
From: Tina S on 24 Mar 2010 17:43
This is a bit complicated for me. Let me give you some more information and
maybe you can use it to help me understand.

I have tblReview with EMP and MGR fields. This is the review table.

I have tblHierachy with EMP and MGR. This is the Hierarchy table showing
the Manager and the Employees assigned to them.

When you give me the information, do I open a new query, go in the SQL view
and put in in there?

Thanks for your help and patience.


"KARL DEWEY" wrote:

> I am assuming that all review records are in a single table --
> SELECT FName, LName, EmployeeID, tblEmployeeReview.Reviewer, [XX].Reviewer
> FROM (tblEmployee LEFT JOIN tblEmployeeReview ON
> tblEmployee.EmployeeID = tblEmployeeReview.EmployeeID) LEFT JOIN
> tblEmployeeReview AS [XX] ON tblEmployee.EmployeeID = [XX].EmployeeID
> WHERE tblEmployeeReview.Reviewer <> [XX].Reviewer;
>
> --
> Build a little, test a little.
>
>
> "Tina S" wrote:
>
> > I am trying to build a report about reviews done on employees. We have
> > Managers assgined to Employees that do reviews. Sometimes we have manager
> > who are NOT assigned to an employee do a review. I want to be able to find
> > these records.
> >
> > So I need the records for when a non-assigned manager reviews a non-assigned
> > employee.
> >
> > Employee 1 assigned to Mgr 1
> > Employee 1 reviewed by non assigned Mgr 2
From: PieterLinden via AccessMonster.com on 24 Mar 2010 20:12
Tina S wrote:
>This is a bit complicated for me. Let me give you some more information and
>maybe you can use it to help me understand.
>
>I have tblReview with EMP and MGR fields. This is the review table.
>
>I have tblHierachy with EMP and MGR. This is the Hierarchy table showing
>the Manager and the Employees assigned to them.
>
>When you give me the information, do I open a new query, go in the SQL view
>and put in in there?
>
>Thanks for your help and patience.
>
>> I am assuming that all review records are in a single table --
>> SELECT FName, LName, EmployeeID, tblEmployeeReview.Reviewer, [XX].Reviewer
>[quoted text clipped - 13 lines]
>> > Employee 1 assigned to Mgr 1
>> > Employee 1 reviewed by non assigned Mgr 2

Looks like this works:

SELECT DISTINCT Review.ReviewID, Employee.EmployeeID, Employee.ManagerID,
Reviewer.ManagerID AS ReviewerID
FROM Employee AS Manager INNER JOIN (Employee INNER JOIN (Employee AS
Reviewer INNER JOIN Review ON Reviewer.ManagerID = Review.Mgr) ON Employee.
EmployeeID = Review.Emp) ON Manager.EmployeeID = Employee.ManagerID
WHERE (((Reviewer.ManagerID)<>[Employee].[ManagerID]));

If you open a new query and copy and paste the SQL into it, you can view the
joins in the top window.
The query SQL is confusing because of all the aliasing going on. The picture
is much clearer.

run that on a small subset of your data and see if that works. (It's just
easier to proof on a couple of records instead of hundreds)

--
Message posted via http://www.accessmonster.com

 | 
Pages: 1
Prev: iif statement what is wrong with this expression
Next: Query to find outliers