In Microsoft Access, how do I make a query search, but the result don't have to fully match?

Question

Hello,
For example, I got a database, and I want to make a search for the list of entries that includes "John Doe", but sometimes, it might be inputted as "John D." or just "John", so I tried putting under the Criteria "John", but the results only show that matches "John", none of the "John D." or "John Doe"'s. Now how do i make a query to search to match any part of the field?

Thank you.

Answer 1

MS Access is a "relational" database like Oracle, SQL Server, DB2 and others, and one of the many features they all have in common is the ability to search through data using the "where like" function. This function is used by including "pattern match" and "wild card" characters in your criteria. They tell the search engine to get results without having an exact match - which is what it sounds like you want to do.



For example, try putting: John* in your query criteria. The asterisk is a pattern match character that tells Access to get every record where the first 4 characters are "John" and it doesn't matter what comes after.



The wild card is similar, but only works on a single character. So if you put: J??? Smith in your query criteria, it would return John, Josh, Jack and Jeff - as long as they all have the last name of Smith.



You can combine these characters into a single criteria - try different approaches so you can get a feel for how they work. Another example: Jo??*Smith* should return rows for John Smith and Josh Smithers, but not Jack or Jeff.



You can get more control by creating multiple criteria using "and" and "or" logic. To use "or" - enter more criteria on the row below (where it says "or"). Keep in mind that this returns rows where either criteria is true.



You can do "and" logic (where more than one criteria must be true) by adding the same column a second (third, fourth, etc) time to your query and putting more criteria on it. Note that in Access, you can keep the additional columns from appearing by un-checking the "Show" check box in the query.



If you want your search to ignore upper vs. lower case in your names, you can make the field all upper case by changing the way the field is selected. If you look at the "Field" row in your query definition, it normally just shows the field name.

Normally: NAME

Change to: Name: UCase([NAME])

If you add the Access function "ucase" it will convert the field to all upper case so that you can make your criteria all caps. This trick lets you match regardless of case.



One last thing - it might make sense to separate your name fields into: First name, Last name, Middle name, etc. This would make it easier to control how you create your searches since you could easily apply different logic to first name from what you apply to the last name.



Hope this long answer has some helpful info for you. The big thing is to just experiment & see how it all works. Have fun!

Answer 2

Make the search criteria *john* for the field that contains the name. I use this type of search all of the time in my Access database.

Answer 3

For any part of the field, you would search for "%john%", for starting with, "John%" and for ending with "%John"



The % is a wildcard character that will match 0 or more characters.



Good luck!

Answer 4

As the previous responder said, hard to say without more info - possibilities include DISTINCT, GROUP BY, subqueries to filter out duplicates or some combination of the above.

Answer 5

Use the wildcard operator: %

example:

SELECT * FROM Table1 WHERE Table1.Name = 'John%'