March 18, 2004 at 6:35 am
|
March 19, 2004 at 3:17 pm
Christy;
Best solution would be to normalize the data and put the surnames fields in a (sub-)table. Then you only have to search the subtable.
But if a database redesign is not feasible or desirable, use a UNION query:
sSQL = "SELECT DISTINCT P1N1_Surname as surname " sSQL = sSQL & "FROM tblDocInfo WHERE " sSQL = sSQL & "P1N1_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") " sSQL = sSQL & "UNION " sSQL = "SELECT DISTINCT P1N2_Surname as surname " sSQL = sSQL & "FROM tblDocInfo WHERE " sSQL = sSQL & "P1N2_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") " sSQL = sSQL & "UNION " sSQL = "SELECT DISTINCT P1N3_Surname as surname " sSQL = sSQL & "FROM tblDocInfo WHERE " sSQL = sSQL & "P1N3_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") " sSQL = sSQL & "UNION " sSQL = "SELECT DISTINCT P1N4_Surname as surname " sSQL = sSQL & "FROM tblDocInfo WHERE " sSQL = sSQL & "P1N4_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") " sSQL = sSQL & "UNION " sSQL = "SELECT DISTINCT P2N1_Surname as surname " sSQL = sSQL & "FROM tblDocInfo WHERE " sSQL = sSQL & "P2N1_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") " sSQL = sSQL & "UNION " sSQL = "SELECT DISTINCT P2N2_Surname as surname " sSQL = sSQL & "FROM tblDocInfo WHERE " sSQL = sSQL & "P2N2_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") " sSQL = sSQL & "UNION " sSQL = "SELECT DISTINCT P2N3_Surname as surname " sSQL = sSQL & "FROM tblDocInfo WHERE " sSQL = sSQL & "P2N3_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") " sSQL = sSQL & "UNION " sSQL = "SELECT DISTINCT P2N4_Surname as surname " sSQL = sSQL & "FROM tblDocInfo WHERE " sSQL = sSQL & "P24_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") "
Recognize that you may get some "duplicate" records, when "Smith" is in P1N1_Surname in one record, and also in P2N3_Surname in another record.
Mark
March 19, 2004 at 3:24 pm
For the message above Make the Union a UNION ALL and the duplicate records will be removed.
March 19, 2004 at 3:26 pm
Thanks, Mark. Forgot about that. That's what I get for posting quickly on a late Fri afternoon.
And thanks for one Mark picking up another.
Mark
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy