I doing a db that is supposed to show the development plan of some persons.

2 tables:
graduates
departments

in the graduates table there are among other data about the graduates, four fields for their departments in the next year.

i have called them:
rotation 1
rotation 2
rotation 3
rotation 4

these rotations should be choosed from the departments table, so i have created a lookup dropdown list in these fields.

now i want to show a query that shows a list of all the departments with the linked graduates. something like this:

Rot 1: Rot 2: Rot 3: Rot 4:
dep 1: Ben John - James
dep 2: James Ben John -
dep 3: - - James -
dep 4: John - Ben -
dep 5: - - - John
dep 6: - James - Ben

'-' = meaning empty field (onley for illustration in this forum)

My question is how i set up the links between the tables to create a query that show this.

Thank you.

I doing a db that is supposed to show the development plan of some persons.

2 tables:
graduates
departments

in the graduates table there are among other data about the graduates, four fields for their departments in the next year.

i have called them:
rotation 1
rotation 2
rotation 3
rotation 4

these rotations should be choosed from the departments table, so i have created a lookup dropdown list in these fields.

now i want to show a query that shows a list of all the departments with the linked graduates. something like this:

Rot 1: Rot 2: Rot 3: Rot 4:
dep 1: Ben John - James
dep 2: James Ben John -
dep 3: - - James -
dep 4: John - Ben -
dep 5: - - - John
dep 6: - James - Ben

'-' = meaning empty field (onley for illustration in this forum)

My question is how i set up the links between the tables to create a query that show this.

Thank you.

You may find some assistance in the attached link.

http://r937.com/relational.html