SEARCH

How-To Geek

Using Wild Card Entries In Access 2010

Carrying out complex operations & applying conditions on tables in Access are not that much easy as in Excel. Some users are still confusing it with Excel which is synonymous to belittling Access capabilities.

Editor Note: This Guest Article was written by Office 2010 Club, a forum managed by Office 2010 experts where anyone can ask questions and participate in discussions. 

Access however, provides essentials of Relational Database Management System. RDBMS are not abound and only advance users can manipulate the database structure for applying different conditions, meanwhile the rapid influx of RDBMS-based dynamic web apps and other utilities also poses a dire need of comprehending the basics of relational databases.

As of now, SQL stands first in handling database structure for pulling out specific data from tables, but a database novice needs to learn the language syntax & its proper usage. On the other hand, Access weighing GUI interface, provides Wild Card entries as replacement of SQL WHERE command for easy extraction of specific data from database tables & queries.

This post will demonstrate the simple usage of Wild Card entries. We will start off with applying Wild Card conditions on an existing database. For illustration, we have created a small store management database containing multiple tables; Customer, New products, Sales, Phone Numbers, and Staff. For pulling out data through Wildcard Entries, switch to Create tab and click Query Design.

create

It will bring up Show Table dialog, allowing you to add tables. Now start adding desired tables into Query Design window.

 add table

Once added, start dragging required table into Query Design fields.

fields added

Now we are interested in pulling out all relevant data residing in database tables against Product name “Pepsi”. For this, we will simply write Wildcard Entry, i.e, Like/Not Like condition under Product Name field like this;

Like “Pepsi”

On executing the query it will show all the relevant info included in Query Design fields ranging from Customer ID, Customer Name to Employee Name, etc.

run

 run1

Lets take a look at another example. Suppose we need to find out all the Customers having their names start with ‘G’. For applying this condition, we will write Like condition under Customer Name as;

Like “G*”

This condition will make Access pull out all the field values which matches with the condition specified (alphabet, numeric value, etc) before asterisk sign.

astterisk

On running query, it will show all the relevant data of customers having their name starts with ‘G’.

asterisk1

For excluding a specific record/data value from the Query. ‘Not Like’ condition comes in handy. It will exclude the specified data value from the records and shows only remaining records.

not like

It will show results excluding all the records where Employee Name starts with ‘L’.

not like1

We have touched only few examples of Wild Card entries to extract specific data out of records. However, there are innumerable ways to extract specific records. Try out these examples on your database to explore some other dimensions of Wildcard Entries.

Like “E#” This will return specific data from selected table having only two characters. first one is E and # represents that character is a number.

Like “G?” it will return two characters if found in specific table, starting with character ‘G’.

Like “*16” This will return data ending on 16 in the specific table.

You can also try out all aforementioned ways with Not Like condition as well. This functionality concludes infinite powerful ways possible to extract the desired data from database. We have applied these conditions on small scale database, but its real usage can be observed when you are dealing with enormous database, having hundreds of related tables.

Comparing the procedure of extracting data from tables with CLI-based SQL concludes that, it is far easier in Access. The reason being, CLI(Command Line Interface) brings monotony that repels novices to get their hands on to it. On the other hand, Access 2010’s simple GUI layout encourages user to begin creating database without having to indulge in labyrinth of command controls, & hard-to-grab syntax.

Lowell Heddings, better known online as the How-To Geek, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on if you'd like.

  • Published 06/22/10

Comments (8)

  1. Peter

    Your last example should be Like “*16” if you want it to return “data ending on 16.” You can also combine them, for example Like “*geek*” will return records with the word geek anywhere in the field.

  2. OfficeGeek (Office2010club)

    Thanx for pointing out Peter. It will be corrected soon.

  3. Gerhard

    “Carrying out complex operations & applying conditions on tables in Access are not that much easy as in Excel.”

    After reading your opening sentence, I stopped reading. Very poorly done.

  4. Bon

    Awesome Post man thanx…

  5. Rowenna

    I have been on a lot ot sites much more complex and much less informative then this site, so well done!

  6. GrammarPolice

    I agree with Gerhard. Second sentence isn’t much better. Rowena – when using a comparative the correct word is “than” not “then”. If you can’t write a sentence there is not much hope for you with SQL syntax.

  7. Chad

    How do you do multiple not likes

  8. Christine

    To expand on Chad’s question, how do you do “not likes” against an entire table of values? Rather than write all the values in the criteria for the query, I want to extract any like fields from a table.

    I have a table of words such as *shipping* and when I query using “like” it pulls all matches that contain the word shipping. But when I query using “not like” it doesn’t exclude matches that contain shipping.

    Any help?

Enter Your Email Here to Get Access for Free:

Go check your email!