SELECT query to match on 5 out of 7 fields

[Kristen]

I'm trying to write a query that will Select all records from a table
where at least 5 out of 7 fields match certain criteria. In SQL Server
I believe it can be done like this

SELECT * FROM clients where first_name='Ann' OR last_name = 'White' OR
ssn ='111-11-1111' OR dob ='#01/01/1999#' OR area_code ='415' OR
phone='222-2222' OR gender='F' as Results WHERE Results.first_name +
Results.last_name + Results.ssn + Results.dob + Results.area_code +
Results.phone + Results.gender >=5

Access isn't allowing me to do that. Is there another way to only
return the records where at least X number of fields match the criteria
in the query?

Thanks!

 

[Jeff Boyce]

Kristen

I may not understand what you are trying to do ...

It sounds like you want any 5 of 7 fields that match your selection
criteria. Wouldn't that mean you'd need to look for every possible
combination of 7 fields, taken 5 (or more) at a time? If my stats serves
me, that would be 21 different possibilities. Are you ready to cover all
these in your query?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Kristen]

I have a stored procedure that uses the code I posted above to return
any client that matches on at least 5 of the 7 fields. So it seems like
it can be done in SQL Server. I'm just wondering if there is a similar
why to do it in Access, or am I going to have to do what you describe
and have 21 different queries to get all the possible matches. I hope I
don't have to do that!

 

[MGFoster]

I'm trying to write a query that will Select all records from a table
where at least 5 out of 7 fields match certain criteria. In SQL Server
I believe it can be done like this

SELECT * FROM clients where first_name='Ann' OR last_name = 'White' OR
ssn ='111-11-1111' OR dob ='#01/01/1999#' OR area_code ='415' OR
phone='222-2222' OR gender='F' as Results WHERE Results.first_name +
Results.last_name + Results.ssn + Results.dob + Results.area_code +
Results.phone + Results.gender >=5

Access isn't allowing me to do that. Is there another way to only
return the records where at least X number of fields match the criteria
in the query?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

My guess is to use some IIF() functions in the WHERE clause:

WHERE (
IIf([first name]='Ann',1,0)
+ IIf([last name]='White',1,0)
+ IIf(ssn='111-11-1111',1,0)
+ IIf(dob=#1/1/1999#,1,0)
+ IIf(area_code='415',1,0)
+ IIf(phone='222-2222',1,0)
+ IIf(gender='F',1,0)
) >=5

In SQL'r I'd use a CASE expression to do the same thing.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRASwJ4echKqOuFEgEQKvggCg4x25Q4WUaBByRRccyFnUbs0OxoQAoMB1
kMGAHY9EZKXc4sC/bcg/uVL7
=i5+B
-----END PGP SIGNATURE-----

 

[Kristen]

Thank you! That worked!