How to score matching results

[pimmc]

Hi, everyone

I'm running a qbf based on two main tables (CVs, Ofertas), in order to find records that have at least one match (see enclosed file), and I would like to include in the underlying query (Consulta_CVs) a field containing the percentage of matching criteria, amongst the parameters being considered, namely: Id_CNP, Id_Esc, Idioma, Competencias_).

Here's the code of the query in question:

SELECT CVs.Id_CV, CVs.NIF, Candidatos.Nome_Candidato, CVs.Id_CNP, CNP.Desc_CNP, CVs.Id_Esc, Escolaridade.Desc_Esc, CVs.Idioma, CVs.Competencias_CV
FROM Idiomas INNER JOIN (Escolaridade INNER JOIN (CNP INNER JOIN (Candidatos INNER JOIN CVs ON Candidatos.NIF=CVs.NIF) ON CNP.Id_CNP=CVs.Id_CNP) ON Escolaridade.Id_Esc=CVs.Id_Esc) ON Idiomas.Idioma=CVs.Idioma
WHERE (((CVs.Id_CNP)=Forms!Form_Ofertas!Id_CNP)) Or (((CVs.Id_Esc)=Forms!Form_Ofertas!Id_Esc)) Or (((CVs.Idioma)=Forms!Form_Ofertas!Idioma)) Or (((CVs.Competencias_CV)=Forms!Form_Ofertas!Competencias_Of))
ORDER BY CVs.Id_CV, CNP.Desc_CNP, CVs.Id_Esc;


Thanks in advance,

Paula

 

[pimmc]

Ok, no one replied so far but I've found this fairly simple (too simple) solution, in a microsoft forum:

(IIf([CVs.Id_CNP]=Forms!Form_Ofertas!Id_CNP,1,0)+IIf([CVs.Id_Esc]=Forms!Form_Ofertas!Id_Esc,1,0)+IIf([CVs.Idioma]=Forms!Form_Ofertas!Idioma,1,0)+IIf([CVs.Competencias_CV]=Forms!Form_Ofertas!Competencias_Of,1,0))/4

(4 is the max number of matching fields)

which is working fine. However, I would like to use the operator like, in some of the fields in question...

Paula