I have a department table, a consultant table and a Human Rights table. Each consultant is assigned to a department. Each dept has only one consultant. Each human rights issue belongs to only one department, but each department may have more than one issue. A consultant handles the human rights issue based upon what department the issue is in. My question is that I need to place the consultants name or id in the human rights table. Can i do this by going through the department table. Other wise if i link the human rights and consultant tables directly i am going to end up with a circular relationship.
Any suggestions?



Thanks
lludlow

I think you could get what you want using the following table structures, although this does not have your consultant id in the human rights table, you can get to it via the department table.

I have show the field names, and 3 example data records to the right of the field names for illustration.

Using these structures, the first and second issues are  controlled by the first consultant (thru the first department); and the third issue is controlled by the second consultant (thru the second department).

Am I close ??



human rights table                    
rightsid    01,02,03
rightsdesc    first issue, second issue, third issue
deptid        01,01,02
                    
department table
deptid            01,02
deptname    first department, second department
consultid    01,02
                    
consultant table
consultid    01,02
consultname     first consultant, second consultant

Pete Bloomfield
Down Under

Thanks Repete,

the post is very helpful. I am creating a form to display the contents of this database, so i can display the consultant name on the form by going through the department
table which has the consultnat id. Am i correct?

Elway

yep, using the table structures mentioned, you can use a sql statement to get the data into a cursor to display in a form

select rightsdesc, deptname, consultname from hrtable, dtable, ctable where hrtable.deptid = dtable.deptid and dtable.consultid = ctable.consultid order by consultname into cursor curtemp

let me know if you need more explanation

Pete Bloomfield
Down Under

"My question is that I need to place the consultants name or id in the human rights table."

Change the table field properties for Lookup to Combo Box. Populate with queries from the tables with ID and Name. Hide the ID column. Change field type to Number (if ID is a number). This will store the ID, but display the Name.

Sean.