Relationships

[Souza]

i've 2 tables, for instance, employee and job. my job table has three fields(employee1,employee2,employee3 where all the three r different but compulsorily associated with a job) which contains employee code(foreign key) and employee code being the primary key of my employee table. if i have to print the records from the job table, i wud like to print the names of all the three employees related to the job instead of the code that is stored which obviously means that i've to maintain a relationship with these two tables. only one relationship is recognized, printing the same name thrice. what is the best possible solution without creating an other table ?

first let say 'emp_no' is a primary key for both of your table and it carry the same value... then what you need is

if you select the job 'no1' then use this code for the other table is

dim empset as recordset 'to open the employee table
dim empstr as string
dim jobset as recordset ' to open job table
dim jobstr as string

jobstr = "select emp_no from jobtable where job = 'no1'"
set jobset = mydatabase.openrecordset(jobstr)

empstr = "select emp_no from emptable where emp_no = '" & trim(jobset("emp_no").value & "' "
empset = mydatabase.openrecordset(empstr)
emp1 = empset("emp1").value
emp2 = empset("emp3").value
emp3 = empset("emp3").value

is this helping... i hope so..