If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Filtered and Multiple Lookups
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!!! |
#2
|
|||
|
|||
Filtered and Multiple Lookups
This is called Cascading Lists in Access. Have a look at :-
http://www.fontstuff.com/access/acctut10.htm for more information. HTH Peter Hibbs. On Thu, 30 Oct 2008 00:00:02 -0700, JacqStar wrote: 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!!! |
#3
|
|||
|
|||
Filtered and Multiple Lookups
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/...g=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 "JacqStar" wrote: 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!!! |
Thread Tools | |
Display Modes | |
|
|