Filtered and Multiple Lookups

[JacqStar]

I'm really not very good at access, so this will probably be a sinch for most
of you

I have a form with one of the inputs as a lookup (to another table). For the
sake of an explanation I'll call this lookup the "Department" field

I would then like the next two lookups to look to the same table table which
contains Employee first and last names, for example and the departments which
they belong to. I would like the lookup to only show the fields of people who
belong to the department which has already been chosen by the first lookup,
and then by making only one selection, put two different values (first and
last name) in two different columns.

If anyone can help a beginner it would be great!!!

 

[Peter Hibbs]

This is called Cascading Lists in Access. Have a look at :-
http://www.fontstuff.com/access/acctut10.htm
for more information.

HTH

Peter Hibbs.

 

[Ken Sheridan]

With entities which are hierarchically related like this you should only
store the lowest level of the hierarchy in the referencing table, i.e. in
your example you should only store the EmployeeID or similar in a table such
as ProjectEmployees. EmployeeID is a foreign key which references the
primary key of a table Employees say, the one with the employee names. This
in turn will have a foreign key such as DepartmentID referencing the primary
key of a table Departments. If you store the department in the referencing
table as well as the employee you are introducing redundancy and the risk of
inconsistent data being entered. In the jargon of the relational model
the department is said to be 'transitively functionally dependent' on the key
of the table, which is against the normalization 'rules'.

You can still select a department first, even though there is no field for
it in the referencing table, and then an employee from a list of those from
the selected department only. For this you'd use an unbound combo box for
the department. Code in the form's Current event procedure assigns a value
to this combo box on the basis of the employee for existing records.

You'll find a demo of how this can be done in both single and continuous
forms at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


It uses the local administrative units of parish, district and county in my
neck of the woods, but the principle is the same for any hierarchical set of
entities. In your case its even simpler as you have only two levels not
three.

Ken Sheridan
Stafford, England