Using MS Access/Access search


QUESTION: My Access database records include 2 fields Surname1 and Surname2.

How can I search the database to find those records containing a name (input at run time) occurring in either of the 2 surname fields?



ANSWER: Why do you have 2 surname fields? When you have fields with a number like that, its referred to as a Repeating group which violates normalization rules. If a record is going to be associated with multiple surnames, you should be using a child table.

Proper design will eliminate your problem.

---------- FOLLOW-UP ----------

QUESTION: I've called the fields Surname1 and Surname2 for this question. They could, for example, just as well be called Father and Mother or anything else you like. My users have been running with this database for 5 years. I'm not about to redesign it now. I'd just like them to be able to enter a value once and find matches. Thank you. Chris

Doesn't really matter what you call them, its not properly normalized. If it was this would not be a problem.

You can do this in a couple of ways. One way would be a UNION query that creates a single column from. for example:

SELECT recordID, Surname1 As Surname
FROM table

UNION SELECT recordID, surname2 As Surname
FROM table;

Then you could Search through the Surname column

Another way would be to use an OR

SELECT RecordID, Surname1, Surname2
FROM table
WHERE surname1 like Forms!formname!controlname OR surname2 Like Forms!formname!controlname;  

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.