RE: linking data from one table to another

hi, thanks for the instructions, I have been trying for several hours to set
this up as per your instructins, with no result. I have entered the data
into the row source using your instructions as a guide, it has the drop down
box when you go to the form, but there is no data in the drop down box.

What I would really like to happen is - have a command button that allows
you to view a list of all the first names of employees, select the employee,
then all the employee contact details are listed back on the form into the
various fields ie first name, surname, address, phone number.

Are you able to provide any further assistance/help ?
--
thanks, kimmieboot


"Ken Sheridan" wrote:

What you have here is a 3-way relationship type between Clients, Employees
and Projects, the last being the work in question. So you need three tables,
one for each of these, each of which has columns representing the attribute
types of each, e.g. FirstName, LastName etc for Employees. To model the
relationship type you need a fourth table WorkAllocations say with foreign
key columns which reference the primary keys of the other three tables, e.g.
ClientID, EmployeeID and ProjectID. These can be autonumbers in the
referenced tables, but should be a straightforward long integer number data
type in WorkAllocations. You can also have other non-key columns in
WorkAllocations to represent other attributes types, e.g. DateAllocated.
Don't include other columns from the referenced tables in this table,
however, e.g. don't include a client's address as this is available from the
Clients table via the relationship on the ClientID columns.

In a form based on the WorkAllocations table you can use combo boxes bound
to the ClientID, EmployeeID and ProjectID columns. The value of these will
be the numeric IDs but you'll see the corresponding text values from the
referenced table by setting them up as follows, e.g. for the EmployeeID combo
box:

RowSource: SELECT EmployeeID, FirstName & " " & LastName AS FullName
FROM tblEmployees ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

To show other values from the referenced table you can use unbound computed
controls, e.g. to show the selected client's address you'd set the
ControlSource property of an unbound text box to:

=DLookup("Address","Clients","ClientID = " & [ClientID])

With the above model you can allocate as many projects per client to as many
employees as necessary. A single project for a client could be allocated to
one or more employees for instance; or different projects for a single client
can be allocated to the same or different employees. You could even have the
same project undertaken for more than one client, though I'd guess that's
probably not a realistic scenario.

Ken Sheridan
Stafford, England

"kimmieboot" wrote:

Hi, I have created a database in Access 2000 which has two tables. I
created a form from the data of one table which has all contact details of
new clients. It also has a section to add who the work has been allocated
to. I have created another table with all employees (this is who the work is
allocated to) I want to dump the information of the employees into the form
- the only way I have been able to do this is drop down list for each field
but this takes forever to go through them one by one with first name, last
name, address and contact numbers, etc.

Is there anyway that I can dump this information in one go by clicking on
one field in the other table ?????

Any help is most welcome as I have spent several days trying to do this !!!!
--
thanks, kimmieboot

.

Loading