strange JOIN and strange realtion of tables

[nst]

I have a table called 'students' with the appropriate fields. There is another table named parents_prof which contains records of common professions. The tables are related through the 'ID_parents_prof' of the 'parents_prof' table to two fields on the table 'students', thus 'students.father_prof' and 'students.mother_prof'.

1st question) Is that possible? Is it possible to create a relation between the key field of one table to two different fields of another table? Is it a good practice?


I did this because I want to build a query which checks for both fields together, meaning I want Access to return a record if the appropriate profession is in either field of mother's or father's profession.

SELECT students.surname, students.name, students.father_name, students.father_prof, students.mother_name, students.mother_prof

FROM parents_prof INNER JOIN students ON (parents_prof.ID_parents_prof = students.mother_prof) OR (parents_prof.ID_parents_prof = students.father_prof)

GROUP BY students.surname, students.name, students.father_prof, students.mother_prof, parents_prof.parents_prof

HAVING (((parents_prof.parents_prof) Like "*" & [enter profession] & "*"));

Access cannot represent the above graphical, so I had to enter the

OR (parents_prof.ID_parents_prof = students.father_prof)

in the SQL formula.

2nd question) Is the above proper query, according to the rules?

 

[KenHigg]

Is there anything else that you are track related to the profession, such as maybe 'avg salary', etc
.,...

 

[nst]

Is there anything else that you are track related to the profession, such as maybe 'avg salary', etc
.,...

No. The only relation parents_prof.ID_parents_prof = students.mother_prof and parents_prof.ID_parents_prof = students.father_prof

i.e.
in table parents_prof:
ID_parents_prof profession
1 carpenter
2 teacher

in table students:
Name Surname Father's Prof. Mother's Prof.
John Doe 1 2

 

[KenHigg]

I'm trying to understand why you don't just store the prof in the student table...(?)

 

[nst]

If I do this then the user should enter always the same data.

In this way I will have redundant data and possible errors.

He may put
.... Carpenter
....
..... Catpenter

Then by searching all parents whose profession is Carpenter (that's the objective) he will not get the 3rd record of the above example because of the typo.

I want him to be forced to select from a ComboBox which will read the data from the parents_prof table.

Do you find this useless complicated?

 

[KenHigg]

Hum... Seems a little overkill.

I'd keep the table with the profession names, and the then have the user select one using a combo box that has a rowsource based on the table...

You could even do a pop-up form/subform that allows you to add/edit the available professions...

Make sense?

 

[nst]

have the user select one using a combo box that has a rowsource based on the table...

Is it not exactly what I am doing?

You could even do a pop-up form/subform that allows you to add/edit the available professions...

How?

 

[KenHigg]

Is it not exactly what I am doing?

Looks like it. But you have all that relationship mess going on...

How?

Basic database stuff isn't it? You have a table and you have a form to edit the data...???

 

[nst]

Looks like it. But you have all that relationship mess going on...

How can I prevent that the user enters his own data? Without the relationship it can't be avoided (I think)

Basic database stuff isn't it? You have a table and you have a form to edit the data...???

You mean by putting a CmdButton on the student's input Form, which opens the Form of the Professions, right?

 

[KenHigg]

How can I prevent that the user enters his own data? Without the relationship it can't be avoided (I think)

Hum... I was thinking you simply have a combo box where you make a selection. That selection is stored in the table. The available options that the combo box lets you choose from is dictated by what you use as a rowsource. You can set the 'Limit to list' option for the combo box to true and this will not allow the user to enter any other data.

All the prof table does is supply the combo box with a rowsource. There is no need for any kind of relationship between the student and prof tables...

Is this making any sense. I'm not the best at explaining it...

You mean by putting a CmdButton on the student's input Form, which opens the Form of the Professions, right?

Yes!

 

[nst]

Is this making any sense.

Yes, it is, I will try it. Thank you.