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.
It will bring up Show Table dialog, allowing you to add tables. Now start adding desired tables into Query Design window.
Once added, start dragging required table into Query Design fields.
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;
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.
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;
This condition will make Access pull out all the field values which matches with the condition specified (alphabet, numeric value, etc) before asterisk sign.
On running query, it will show all the relevant data of customers having their name starts with ‘G’.
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.
It will show results excluding all the records where Employee Name starts with ‘L’.
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.
Programmer by day, geek by night, The Geek, also known as Lowell Heddings, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on Google+ if you'd like.
- Published 06/22/10