RE: linking data from one table to another
- From: Ken Sheridan <KenSheridan@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 2 Jun 2008 04:53:00 -0700
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
.
- Follow-Ups:
- RE: linking data from one table to another
- From: kimmieboot
- RE: linking data from one table to another
- Prev by Date: RE: Using Results from Macro to create list
- Next by Date: Replies
- Previous by thread: RE: Using Results from Macro to create list
- Next by thread: RE: linking data from one table to another
- Index(es):
Relevant Pages
|
