How to score matching results

[Paula]

Hi

I'm running a qbf based on two main tables (CVs, Ofertas), in order to find
records that have at least one match, and I would like to include in the
underlying query 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!Compete ncias_Of))
ORDER BY CVs.Id_CV, CNP.Desc_CNP, CVs.Id_Esc;


Thanks in advance,

Paula

 

[John Spencer]

I might try the following. This gives you the match percentage for each
record (.25 to 1.00). If you want you can multiply that by 100 to give you a
whole number.

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
, (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!Compete ncias_Of,1,0))/4
as MatchPercent
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!Compete ncias_Of))
ORDER BY CVs.Id_CV, CNP.Desc_CNP, CVs.Id_Esc;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Daryl S]

Paula -

If you always have the same number of options, then you can add a field to
calculate the percent, assigning each match a value of one and a no-match a
value of zero. Add them together and divide by the number of options. This
would be a new field in the SELECT section of the query. Try this (untested):

(if(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!Compete ncias_Of,1,0))/4 AS
Pct_Match