Please help me with my database

[Ben Taylor]

I am trying to create a recruitment database for work,
but I am completely stuck, I would appreciate it if
somebody could please help me.

I will try my best to explain my problem, and keep it as
simple as i can.

I have a main table of candiates, and another table of
job types (selected from a list), linked in a 1 to many
relationship - ie each candidate has many job types.

I am trying to create a search form that allows a user to
input one or more job types, and then displays results of
candidates with all of these job types.

I have created a query with both tables in, and taken the
criteria from the search form, if i enter two job types I
can only ever get it to display candiates with either one
or the other, and both, and it displays them numerous
times - as there is a full candidate record in the query
for each job type.

I am currenltly using this line in my query:

[Forms]![FrmSearchDiscipline]![Search1] Or [Forms]!
[FrmSearchDiscipline]![Search2]

Does anyone have any idea what way I can go about this??
I would really appreciate anyones help, if anyone can
make sense of it all!

Thanks

Ben

 

[Gerald Stanley]

If you want your query to return only those candidates who
match on both types then you will need to use a subquery
along the lines of
WHERE candidateId IN (SELECT candidateId FROM JobTypes
WHERE jobType = [Forms]![FrmSearchDiscipline]![Search1] OR
jobType = [Forms]![FrmSearchDiscipline]![Search2] GROUP BY
candidateId HAVING Count(jobType) = 2)
The above is untested aircode and you will need to change
the table and column names to suit your app.

Regarding the issue of duplicate details, thsi depends uopn
whether the columns selected for output come from one table
or multiple tables. If the latter, then you will get the
candidate details repeated multiple times as the query will
always populate the results if there is data in the
undelying tables.

Hope This Helps
Gerlad Stanley MCSD

 

[PER HILDAL]

Hello !

Try making 3 tables the 3 table should consist of
the Employe ID and Job ID this togehter makes
a unique key.
This makes a many to many relationship

Make a query and a form of this 3 tables and
create a filter or a search criteria
this will probably solve your problem.

PSH