I presume you are talking about the 3-table structure.
One employee can have many skills. One skill can be possessed by many
employees. It's a classic many-to-many relation. The solution is to break
it down into a pair of one-to-many relations, by using a junction table.
Employee table (one record for each person):
EmployeeID primary key
Surname
FirstName
...
Skill table (one record for each skill):
SkillID primary key
...
EmployeeSkill table (one record for each combination):
EmployeeID foreign key to Employee.EmployeeID
SkillID foreign key to Skill.SkillID
If employee 99 has 3 skills then there are 3 records in this table for
employee 99.
The interface is a main form bound to the Employee table, with a subform
bound to the EmployeeSkill table. The subform has a combo that has the
Skill table as its RowSource. The selects a skill for the employee in the
main table, and then a 2nd skill on the next line of the continuous
subform, and so on.
For another example, see:
Relationships between Tables
at:
http://allenbrowne.com/casu-06.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Derek Brown said:
Christ I'd like to see how that works. 3 tables? Been doin' this for 10
years never heard of that. Still when you work alone perhaps you miss
some vital stuff. Would you mind going into this alittle more. I just
need how the principles work. how do you join tables in the relationship
or the query?
Thanks again
Allen Browne said:
Well, once you have the form filtering correctly, you can use that
filter in the WhereCondition of OpenReport:
Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "report1", acViewPreview, , strWhere
Of course, the best solution would be to split this into 3 tables:
- a table of people, with an EmployeeID;
- a table of skills, with a SkillID;
- a table of EmployeeSkills, with EmployeeID and SkillID as foreign
keys.
Thanks Allen
As usual i missed the important thing out. The query is to produce a
report. Actually what I am doing is working with a form that selects
suitable people for job opportunities and printing a report that shows
suitable candidates. Criteria could be "Age" (but not always) "Skilled"
or "Unskilled" etc etc and i just need a report that shows all suitable
candidates. Many of the fields are just Tick boxes returning Yes or No
but to get it to filter correctly when not selected I have to put in
the query's criteria :
IIf([Forms]![JobTitleRequirementsForm]![Filler
5]=-1,-1,(([Employees].[Filler 5])=0 Or ([Employees].[Filler 5])=-1))
.................Which looks frighteneing but it works Problem is
idealy i need 30 field like this one?????? so user can select criteria
and print a report of all that fit criteria.
As you found it gets really messy trying to filter a form by using
criteria in the query. The best result is usually to drop the criteria
from the query, and filter at the form instead.
There is a Filter By Form on the toolbar. That's a fairly simple way
of just entering a few criteria to filter the form.
In any form, there are usually not more than 2 - 6 fields that you may
need to filter on. Another approach is to place some unbound controls
in the Form Header section (or somewhere obvious), along with a couple
of command buttons for Filter and Remove Filter. The Filter button
builds up a string from the non-blank boxes, and assigns it to the
Filter property of the form. Post back if you want an example of hwo
to code that.
I have a form that i use to filter a tables records by select query.
The form has ten fields but I sometimes only require to filter on two
or three fields yet I sometimes need all of them. If I leave the
fields blank then the query obviously looks for blank entries and
finding none it returns no records. How can I stop this. How can I
say only filter with fields that have an entry on the form?
