Using MS Access/Access search

Advertisement


Question
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?

Thanks

Chris

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

Answer
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

Volunteer


Scottgem

Expertise

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

Experience

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

Organizations
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

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.