Lookup from Table's own contents

  • Thread starter Thread starter ITMA
  • Start date Start date
I

ITMA

Can anyone help me crack what I think is a typical problem illustrating how
Access is very hard if not impossible to learn through self teaching ...

SITUATION:: An Employee table, with a 'reports to' field. In other words,
the Reports to field is related, and looks up, other employees in the same
table. There is just such a situation in the Northwind database.

QUESTION: How can I get the lookup combo box to exclude the person reporting
to themselves? This feature was side-stepped in the Northwind database!

As regards my opening comment, it is easy to have a Query that will, for
instance, pull together information where the criteria for one field is a
match with a field in another table or even itself. So, I figured, it would
be a simple case of inserting '<>' in that criteria box to simply make it
pull out everything that was not equal, but no, access chokes on that and
returns nothing at all. Hmmm, not sure words are the best medium to convey
Access questions!
 
ITMA,

I have personally found that "self teaching" always involves a fair
bit of trial and error.

I am not sure what you mean by "pull out everything that was not
equal", and since you didn't actually say what you tried, it is hard
to give specific help. But "not equal" has to specify "... to what?"
If I understand you correctly, the Row Source of the 'Reports To'
combobox will be a query based on the Employee table, with the
equivalent of this in the criteria of the EmployeeID field...
<>[Forms]![NameOfYourForm]![EmployeeID]

- Steve Schapel, Microsoft Access MVP
 
If you want to list all the Employees with the exception of those that supervise
themselves, then use a query like the UNTESTED one that follows.

SELECT Employee.EmployeeName as Worker,
E2.EmployeeName as Supervisor
FROM Employee INNER JOIN Employee as E2
ON Employee.ID = E2.ReportsTo
WHERE Employee.ID <> E2.ID

Add the following if you want to restrict this to a specific supervisor

AND E2.ReportsTo = TheReportsToValueOfTheSupervisor
 
SITUATION:: An Employee table, with a 'reports to' field. In other
words,
the 'reports to' field is related, and looks up, other employees in the same
table. There is just such a situation in the Northwind database.

QUESTION: How can I get the lookup combo box to exclude the person reporting
to themselves? This feature was side-stepped in the Northwind database!
Click to expand...

If I understand you correctly, the Row Source of the 'Reports To'
combobox will be a query based on the Employee table, with the
equivalent of this in the criteria of the EmployeeID field...
<>[Forms]![NameOfYourForm]![EmployeeID]
Click to expand...

I think you've understood me correctly and I think I've tried what you're
suggesting, and it doesn't work, for me at least! The combo box is
completely empty, presumably because for every EmployeeID, it is always
looking at the same EmployeeID that the criteria is checking against.
Checking if 2<>2, or 6<>6 is always going to come back false! The real
problem seems to be restricting the comparison to the EmployeeID of the
current record.
 
ITMA,

We must be at cross purposes here! You are not checking for "2<>2" at
all, you are trying to return "all except where x=2". The SQL view of
the query for the combobox's rowsource should look like this:
SELECT EmployeeID, EmployeeName FROM Employees
WHERE EmployeeID <> [Forms]![NameOfYourForm]![EmployeeID]

This absolutely should *not* return no records in a blank combobox. I
mean, on your form, you have your controls bound to the form's
recordsource fields in the Employee table, right? And you have the
EmployeeID field (or whatever your primary key field is called) on the
form, right? And the combobox we are talking about is bound to the
[Reports to] field, right? And the data that goes in there is the
EmployeeID of the supervisor of the employee whose current record is
on the form, right? And you want the combobox to list all employees
from the employee table except for the current dude, right? Well,
what I've said is designed to do just that, and if it doesn't we need
to track down why, but I assure you the basic concept is sound!

- Steve Schapel, Microsoft Access MVP
 
The SQL view of the query for the combobox's rowsource should
look like this:
SELECT EmployeeID, EmployeeName FROM Employees
WHERE EmployeeID <> [Forms]![NameOfYourForm]![EmployeeID]
Click to expand...

I'm sure we're getting there, but I get an error box with

Enter Parameter Value
Forms![NameOfYourForm]!EmployeeID
 
The SQL view of the query for the combobox's rowsource should
look like this:
SELECT EmployeeID, EmployeeName FROM Employees
WHERE EmployeeID <> [Forms]![NameOfYourForm]![EmployeeID]
Click to expand...

I'm sure we're getting there, but I get an error box with

Enter Parameter Value
Forms![NameOfYourForm]!EmployeeID
Click to expand...


Right! Got rid of the error message - a typo.

It works - but only for the first Employee. Move to the next employee's
form and the whole thing breaks down
 
ITMA,

Phew!

I think it will sort it out if you put this code on the OnEnter event
of the combobox, or the OnCurrent event of the form...
Me.NameOfCombobox.Requery

- Steve Schapel, Microsoft Access MVP
 
I think it will sort it out if you put this code on the OnEnter event
of the combobox, or the OnCurrent event of the form...
Me.NameOfCombobox.Requery
Click to expand...

You're right it does!!

But it just goes to illustrate my point that you cannot really work it out
for yourself. Going back a few years, and using a different example, I
really liked WordPerfect 5 for DOS because you could, with the excellent
inbuilt help and the 'reveal formatting codes' option, see exactly what was
going on and learn how to use it. Microsoft stuff, however, is a doddle to
use if you know how to do it, but is completely hopeless without some kind
of external training.
 
I don't suppose you could offer some outline guidance as to what I should be
doing to now get a report grouped on who reports to who? Its possible of
course that the chain of reporting might run to more than one level, if you
see what I mean. For example, Fred might report to Mary who in turn reports
to Jack. How can I get a report structure such as :

Main Group - Jack
Sub Group - Mary
Sub-Sub Group - Fred

Somehow I've got to test, within the underlying table, all the inter
relationships.

I'm fairly good at thinking the required logic or process through but the
impossible part as ever is knowing how Access requires you to do it!
 
ITMA,

Many people find the learning curve with Access to be fairly steep.
But there are certainly many people, including myself, who are working
it out ourselves without external training.

- Steve Schapel, Microsoft Access MVP
 
ITMA,

In a nutshell: Make a query and add the Employees table to it twice.
Join them based on the EmployeeID field in one copy of the table with
the ReportsTo field in the other. Take the EmployeeName from both
tables into the query design grid. This query should give you a list
of all employees and their supervisors. You can then use selection
criteria, sorting, or Sorting&Grouping facility in a report, to
present this in the way you want.

- Steve Schapel, Microsoft Access MVP
 
The SQL view of the query for the combobox's rowsource should
look like this:
SELECT EmployeeID, EmployeeName FROM Employees
WHERE EmployeeID <> [Forms]![NameOfYourForm]![EmployeeID]
Click to expand...
Click to expand...

I notice that to get this to work its necessary to manually edit the SQL
view rather than simply click away in the expression builder, which for
instance, puts brackets around the text .... or am I clicking the wrong
options?
 
In a nutshell: Make a query and add the Employees table to it twice.
Join them based on the EmployeeID field in one copy of the table with
the ReportsTo field in the other. Take the EmployeeName from both
tables into the query design grid. This query should give you a list
of all employees and their supervisors. You can then use selection
criteria, sorting, or Sorting&Grouping facility in a report, to
present this in the way you want.
Click to expand...

That's brilliantly simple yet I would probably have never worked it out -
all to do with that mystical knowledge of what makes Access tick!

Last favour, if I may ....

How can I get it to suppress, in the first 'Grouped By' set of employees
those who are subsidiary to someone else?

I realise a lot has to do with the nature of the relationship between the
tables, but by changing that setting I go from the problem just outlined, to
excluding all those who do not have anyone reporting to them!
 
That's brilliantly simple yet I would probably have never worked it out -
all to do with that mystical knowledge of what makes Access tick!
Click to expand...

Sure. I know exactly what you mean. I had the same experience with
helicopters. I can't see why they can't just make a machine you can
jump in and fly, without having to study aerodynamics and learn how to
read the funny little dials and buttons in the front.
How can I get it to suppress, in the first 'Grouped By' set of employees
those who are subsidiary to someone else?
Click to expand...

Sorry, must be a bit fuzzy headed this morning. Isn't nearly everyone
subsidiary to someone else? Does "suppress those who are subsidiary
to someone else" mean the same as "only show those who report to
no-one"? Maybe an example would help me understand.

- Steve Schapel, Microsoft Access MVP
 
Sure. I know exactly what you mean. I had the same experience with
helicopters. I can't see why they can't just make a machine you can
jump in and fly, without having to study aerodynamics and learn how to
read the funny little dials and buttons in the front.
Click to expand...

My point about Access is that all the 'little dials and buttons' are
hidden - they're there if you know, and not if you dont. Knowledge of VBA
seems to be the way to get Access to work in any useful form yet it seems to
me a case of needing to know all of VBA before you can use any one bit of
it.
Does "suppress those who are subsidiary to someone else" mean the
same as "only show those who report to no-one"?
Click to expand...

Yes. I realise, or at least presume, it need be done at in the control for
the Report rather than the query that is its control source, but cannot get
any further than that
 
ITMA,

Well, I'm not sure if this is going to give you exactly what you are
looking for. I suppose you realize that what you are doing is not
entirely simple and straightforward,... even for those who know where
some of the buttons are hidden? :-)

Anyway, if you do like before, with the Self Join on the two copies of
the table in the query, and this time make it a Left Join...
Double-click the actual line joining the tables in the query design
window, and then in the properties window that pops up, select the 2nd
option, which should be the one that says something about all records
from the first table (the one where the join line comes from the
EmployeeID field), and only those matching records from the other
table (the one where the join is to the Reports To field). OK. Now
you can put the Employee Name field from both tables in the query
design grid, and in the criteria of the ont from the second table,
put...
Is Null

This query should give you all Employees who report to no-one.

The SQL view of the query will probably look something like:
SELECT Employees.[Employee Name], Employees_1.[Employee Name]
FROM Employees LEFT JOIN Employees AS Employeess_1 ON
Employees.EmployeeID = Employees_1.[Reports To]
WHERE ((Employees_1.[Employee Name]) Is Null)

- Steve Schapel, Microsoft Access MVP
 
Back
Top