relating fields

[Tried of asking]

I have: Name field, affiliation field, department visited field, I want to be
able to choose the name from the drop down menu after it has been entered
into the database and the other two fields to be field in without having to
choose from the drop down. This would save 2 steps since the name,
affiliation and department never changes. The other fields do. How do I do
this? I can't look it up because I am not sure what it is called and I did
not create the form. Please help.

 

[KenSheridan via AccessMonster.com]

You need two tables for this. Starting with a table with columns for the
name, affiliation and department with one row per name, you should not then
also store these values repeatedly in another table. All you need in a
'referencing' table is a foreign key column which references the primary key
column of the 'referenced' table, to which it is related in a many-to-one
relationship.

The primary key of the referenced table should not be the name column as
names can be legitimately duplicated. It should be a unique numeric column;
an autonumber is a convenient way of ensuring this. The referencing tale
should have a corresponding numeric foreign key column, but not an autonumber
in this case, It should not have a name, affiliation or department column as
that introduces 'redundancy' and leaves the table open to inconsistent data.

So lets assume for this example that the names are names of employees and the
referenced table is a table of projects, and each project can be assigned to
only one employee. So the tables would be something like this:

Employees
….EmployeeID (PK)
….FirstName
….LastName
….Affiliation
….Departnent

Projects
….ProjectID
….EmployeeID (FK)
….ProjectDescription
….ProjectDate
….ProjectLocation

In a form for entering projects a combo box bound to the EmployeeID column
would be set up like this:

Name: cboEmployee

RowSource: SELECT EmployeeID, FirstName & " " & LastName, Affiliation,
Department FROM Employees ORDER BY LastName, FirstName;

BoundColumn: 1
ColumnCount: 4
ColumnWidths: 0cm;3cm:2cm:2cm
ListWidth: 6cm

If your units of measurement are imperial rather than metric Access will
automatically convert them. The important thing is that the first dimension
is zero to hide the first column. Experiment with the other dimensions to get
the best fit. The ListWidth is the sum of the ColumnWidths.

Add two unbound text boxes to the form with ControlSource properties of:

=cboEmployee.Column(2)
=cboEmployee.Column(3)

These will show the affiliation and department for the employee selected in
the combo box. The Column property is zero-based, so Column(2) is the third
column, Affiliation and Column(3) the fourth column, Department.

Ken Sheridan
Stafford, England

 

[KenSheridan via AccessMonster.com]

My arithmetic is clearly not very good; the ListWidth would be 7cm, the sum
of 3, 2 and 2!

Ken Sheridan
Stafford, England

 

[KARL DEWEY]

Your approach is wrong. Name field, affiliation field, etc for employees to
be in one table and only once but with an ID field that is unique such as an
Autonumber.

Them to display that information in association with other tables
(department visited) just join in the query on the ID. The combo uses the ID
as bound column but not display it but show name. Other text boxes using the
fields from the employee table to show other employee information as desired.

If further details is needed then post your table structure.