Relationship (Access 2003)

[Guest]

I have an employee table named tblEmployee with fields EmployeeID (Primary
Key), EmployeeName, and Title. Also, I have a project table named tblProject
with fields ProjectID (Primary Key), ProjectName, and EmployeeID. Lastly, I
have a third table named tblTable with fields EmployeeID and Field1.

In a select query I've added the above three tables with fields EmployeeID
(tbTable), Title (tblEmployee), and Field1 (tblTable).

What I am trying to accomplish is to:

Have the field "Title" autofill when EmployeeID is selected when adding a
new record.

The question is:

In the Relationshop Window, how do I create the relationship amongst the
above three tables?

Pleas help. Thanks.

Chai

 

[strive4peace]

Hi Chai,

Looking at your explanation, I see:

*tblEmployee*
EmployeeID, pk (autonumber?)
EmployeeName, text -- you should separate this into Lastname and Firstname
Title, text

*tblProject*
ProjectID , pk, (autonumber?)
ProjectName, text
EmployeeID, long integer

*tblTable*
EmployeeID, long integer
Field1, ?

instead, your Projects table should define Projects just once and you
should have a cross-reference table between Employees and Projects...

*tblEmployee*
EmployeeID, pk (autonumber?)
EmployeeName, text -- you should seperate this into Lastname and Firstname
Title, text

*tblProject*
ProjectID , pk, (autonumber?)
ProjectName, text

*tblEmpProjects*
EmpProjID, autonumber -- PK
EmployeeID, long integer
ProjectID, long integer


"In the Relationshop Window, how do I create the relationship amongst
the above three tables?"

I do not really understand what tblTable is...

in the relationships window, drag a link from:
tblProject.ProjectID to tblEmpProjects.ProjectID
tblEmployee.EmployeeID to tblEmpProjects.EmployeeID

if you are storing EmployeeID, you do not need to also store the related
information for that employee -- instead, show it on the form or report
as calculated fields

Here is something you can draw an analogy from:

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, make the width .01 MORE than the sum of the
columns to prevent the horizontal scrollbar.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields.

For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access





Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*