Circular Relationship?

[LLudlow]

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

 

[repete]

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 ??

s-)

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

 

[LLudlow]

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

 

[repete]

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

 

[perrymans]

"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.