Querying for records from a form

[Guest]

The placement dept. wants to search the database for different student's
skills to match them to possible job openings.
This is what I would like to do (I have not implemented yet because I do not
know how):
Make each skill a yes/no field.
Create a form where the user check marks each skill (then skill = yes)
Clicks in a [Find records] command button. All the records where any skill =
yes will be displayed.

I do not know if this the best apporach or there is an easier way to do it.
Any help will be greatly appreciated.

Once again, thanx.

 

[Rick B]

Personally, I would not do this. It will make it very hard to search. It
will not be expandable. You will constantly have to change your forms,
queries, and reports every time you change the skills.

This is (in my opinion) a one-to-many relationship. Or many-to-many really.

I would have three tables. One for Student info (which you already have)
that includes a student ID or some other primary key to identify the
student.

I'd build another tble where I maintain the possible skills. It should have
a skill number and description. I'd also add an "inactive" checkbox.

Then, I'd have a third table for StudentSkills. This table will have a
field for the studentID and a field for the SkillNumber. If a student can
do five skills, he'd have five entries in this table. If he can do twenty
skills, he'd have twenty entries.

I'd display all this on my Student MAintenance form as a separate tab, or
section in my form. I'd use a subform that pulls that third table. I'd use
a datasheet view.

 

[Guest]

Thank you, it makes sense and it is a lot simpler!

But... I am a little confused... How is the user going to be able to find
the records? For example, she is looking for skills 2, 5 and 7. The form
should display only the students that match these 3 skills.

Now, I am thinking of creating a form based on a query (based on table 3)
that asks for the skill numbers.

Thanx.

Personally, I would not do this. It will make it very hard to search. It
will not be expandable. You will constantly have to change your forms,
queries, and reports every time you change the skills.

This is (in my opinion) a one-to-many relationship. Or many-to-many really.

I would have three tables. One for Student info (which you already have)
that includes a student ID or some other primary key to identify the
student.

I'd build another tble where I maintain the possible skills. It should have
a skill number and description. I'd also add an "inactive" checkbox.

Then, I'd have a third table for StudentSkills. This table will have a
field for the studentID and a field for the SkillNumber. If a student can
do five skills, he'd have five entries in this table. If he can do twenty
skills, he'd have twenty entries.

I'd display all this on my Student MAintenance form as a separate tab, or
section in my form. I'd use a subform that pulls that third table. I'd use
a datasheet view.


--
Rick B

The placement dept. wants to search the database for different student's
skills to match them to possible job openings.
This is what I would like to do (I have not implemented yet because I do not
know how):
Make each skill a yes/no field.
Create a form where the user check marks each skill (then skill = yes)
Clicks in a [Find records] command button. All the records where any skill =
yes will be displayed.

I do not know if this the best apporach or there is an easier way to do it.
Any help will be greatly appreciated.

Once again, thanx.

 

[Rick B]

That is exactly right. Build a form based on a query, or simply a query
that lets the users find students that match certain skills.

The query will pull all the records in the Many-to-Many table where the
entered skills exist. They will use the related tables to show the user the
student name (instead of just number) and the skill description).


good luck,

--
Rick B

Thank you, it makes sense and it is a lot simpler!

But... I am a little confused... How is the user going to be able to find
the records? For example, she is looking for skills 2, 5 and 7. The form
should display only the students that match these 3 skills.

Now, I am thinking of creating a form based on a query (based on table 3)
that asks for the skill numbers.

Thanx.

Personally, I would not do this. It will make it very hard to search. It
will not be expandable. You will constantly have to change your forms,
queries, and reports every time you change the skills.

This is (in my opinion) a one-to-many relationship. Or many-to-many really.

I would have three tables. One for Student info (which you already have)
that includes a student ID or some other primary key to identify the
student.

I'd build another tble where I maintain the possible skills. It should have
a skill number and description. I'd also add an "inactive" checkbox.

Then, I'd have a third table for StudentSkills. This table will have a
field for the studentID and a field for the SkillNumber. If a student can
do five skills, he'd have five entries in this table. If he can do twenty
skills, he'd have twenty entries.

I'd display all this on my Student MAintenance form as a separate tab, or
section in my form. I'd use a subform that pulls that third table. I'd use
a datasheet view.


--
Rick B

The placement dept. wants to search the database for different student's
skills to match them to possible job openings.
This is what I would like to do (I have not implemented yet because I

do
not

know how):
Make each skill a yes/no field.
Create a form where the user check marks each skill (then skill = yes)
Clicks in a [Find records] command button. All the records where any

skill
=

yes will be displayed.

I do not know if this the best apporach or there is an easier way to

do
it.

Any help will be greatly appreciated.

Once again, thanx.