Whats the best method to take?

[hollinshead]

hi there, I wounder if you can help, i am looking for some advice on the best
method to take in my database. The situation is that the database is going to
primarily going to be used to filter the inputted data. Its to make the
process of filtering a candidates CV easier, so in this database will be
fields for professional qualifications and industrial sectors etc.There are
Large range of tables in the database an exmaple can be seen below;

tblCandidatesDetails
CandidatesID(Primary key)
FullName
Email Address
Gender
Nationality

tblIndustrialSectors
IndustrialSectorID
IndustrialSector

tblCandidatesIndustrialSector
CandidatesID
IndustrialSectorID

Now there are loads of others like this and that may primarily the problem. I
have a form to input the details in and this is made up of the
tblCandidatesDetails as the Mother table and all the other tables such as
tblCandidatesIndustrialSector in subforms. Entering the data is fine, however
the problem is with the search.

I need to be able to create a form that has all the criteria on for which the
user can select, then once selected click a command button and it opens a
form displaying the results of all the candidates that match that criteria.
Due to the fact that ive got subforms i have done this but in the results it
shows repetative data for the same candidate so if they have worked in more
than one industrial Sector. I dont know if im making myself clear, if not i
can reiterate a little. Thanks for your time.

 

[Barry A&P]

Here is code from a search form i use

i have a search form with unbound comboboxes for all my criteria and a
subform in the search forms Footer section that will display all of the
canidate info that i want to filter by. the code creates a filter string
that when activated (by hitting the search button) filters the subform and
then makes the footer (subform) visible..

Private Sub Search_Click()
Dim strwhere As String
Dim strError As String

strwhere = "1=1"

' Add category info (category is a number)
If Nz(Me.Category) <> "" Then
'Add it to the predicate - exact match
strwhere = strwhere & " AND " & "T_PartNumbers.CategoryID = " &
Me.Category & ""
End If

' Add Part Number info (part number is text)
If Nz(Me.PartNumber) <> "" Then
' Add it to the predicate - match on leading characters
strwhere = strwhere & " AND " & "T_PartNumbers.PartNumber Like '*" &
Me.PartNumber & "*'"
End If

' add description info (description is text)
If Nz(Me.Description) <> "" Then
' Add it to the predicate - match on leading characters
strwhere = strwhere & " AND " & "T_PartNumbers.Description Like '*"
& Me.Description & "*'"
End If

' add Home location combo (home location is a number)
If Nz(Me.HomeLocation) <> "" Then
'Add it to the predicate - exact match
strwhere = strwhere & " AND " & "T_PartNumbers.HomeLocationID = " &
Me.HomeLocation & ""
End If

' Add Notes Combo Info ( notes combo is text)
If Nz(Me.Notes) <> "" Then
' Add it to the predicate - match on leading characters
strwhere = strwhere & " AND " & "T_PartNumbers.Notes Like '*" &
Me.Notes & "*'"
End If

If strError <> "" Then
MsgBox strError
Else
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.F_Parts_Browse_All.Form.Filter = strwhere
Me.F_Parts_Browse_All.Form.FilterOn = True
End If
End Sub

when building the filter string there are lots of nitpicky stuff about
having the correct "'s and "'"#'"s for the data type

and maybe in the subforms datasource query you might be able use a select
distinct
so that only on instance of each canidate shows but i am not sure exactly
how to make sure all records get the filter applied

Maybe from the results of that form you can use a report with a distinctrow
im just blabbing now so ill go

Barry