To many people, there is some confusion as to what the difference is between Microsoft Access and Microsoft Excel. To try and clear some of the confusion up, I’m starting to write a series on Access Vs. Excel. In this part, I will be examining example cases where it’s better to use Access rather than Excel.

Here are the three example cases:

• You need to store complex dynamic data and report on it - In this case, you have data that is going to change over time and it’s not straightforward (think many interrelationships). Not only that, but you will need to be able to produce reports on this data even after it changes. While this can be done in Excel, it is usually easier to do this in Access. One reason for this is because it is generally easier to dynamically alter/add/subtract data in Access once you have the database setup. Consequently, because your reports in Access will be based off of database queries, you shouldn’t even have to update the reports to get the new data, you should just have to run the queries. In Excel, you will spend hours deleting rows, adding rows, reformatting your report, and all the other fun tasks that follow, and you’ll have to do this each time you change the data.

• You will need to be able to create custom reports on the fly - This is another huge advantage for Access. Once again, you can design new queries easily in order to create a custom report. All you then have to do is run the queries to get your new report. In Excel, this would be a much more arduous process, as you would probably have to create a new spreadsheet, link the cells, transfer the data, format it, etc.

• You need to store your data for a period of time - If you need to store your data for the long run, Access is the hands down choice. It is much easier to take this data later and put it into other databases or manipulate it in your own database rather than digging through an excel workbook trying to cut and paste what you need, or change it.

Even though Access is the better choice in the example cases above, there are times when Excel is the better option. The next article in this series will deal with these example cases.

Many times the name of a column in a table may make perfect sense to you, but to someone not familiar with the structure of your database it could be very confusing. Consequently, when you need to create queries or reports for these people, you can’t use the actual column names. Another example would be where you need to combine the values in multiple columns or perform other operations on them. For the end user, all you want them to see is the result in a single column. This is where the AS statement comes in.

The AS SQL statement allows you to alias a column with another name, or put the result of multiple operations in one result column.

Here’s a simple example. We have a table named CustInfo with a number of columns, and we want to pull the following three columns from the table:

• CustName - The customer’s name.
• CustAddress - The customer’s address.
• CustNum - The customer’s phone number.

CustName and CustAddress seem pretty straightforward, however you will want to rename them for your user into more friendly names. On the other hand, the name CustNum could mean many things to some not familiar with your database structure. We’ll want to rename this to “Customer Phone Number”.

Here’s the query we would use:

SELECT CustName AS [Customer Name], CustAddress AS [Customer Address], CustNum AS [Customer Phone Number] FROM CustInfo

*Note: This syntax is specific to Microsoft Access. The syntax may change depending on what database platform you are using.

After this query is run, the following would be returned:

As you can see, the column titles are much friendlier to an end user than the original column names.

Another example occurs when you want to perform an operation on multiple fields and have it come out as one field. For instance, let’s say we have the table below:

What if we want to total the two values in each record and return only this total in our query? The query to accomplish this is shown below:

SELECT (num1 + num2) AS Total FROM Table1

This is the result of running this query:

The resultant query has returned a clean table back to the end user, without them having to be aware of what’s going on underneath.

Overall, the AS statement is very easy to use, and very powerful. I find it especially useful when you have to create queries where the results can be put directly into a report with little to no manipulation of the result table.

On a roll with the Microsoft Access articles, check out the new one on using the SQL statement LIKE for a quick guide to using the LIKE statement in Access.

This is coming straight off a problem I helped a co-worker with today. They were running some simple queries in Access, however they couldn’t nail down one problem: How to retrieve only those records from a table where the first part of their query field equaled the value “yes”. Their problem was that they had other information in the field beside the “yes”, but that’s all they wanted to query on.

The solution to this is very simple, and it comes in the form of the LIKE statement. This operator allows you to find all the records in a table where the value in your specified field matches the Regular Expression you provide. The detailed definition of Regular Expression can be found here. In short, it is basically a pattern that Access will match against the value in a field.

For example, the Regular Expression ‘yes*yes’, would match any values where the first three characters are “yes” and the last three characters are “yes”. The “*” specifies that any other characters can be between the two “yes” blocks. Any time you want to specify “any characters” in Access, you can use the “*”.

The syntax for the LIKE statement is as follows:

field LIKE ‘Regular Expression’

To break this down:

• field - The field to which you will be selecting the records on, and which the Regular Expression will be matched to.
• LIKE - The operator.
• Regular Expression - As described above, the pattern you want to evaluate the field on.

As an example, suppose I want to select all of the records in my table where the “CustomerName” field begins with the letter “A”. That statement would look like this:

SELECT * FROM CustomerName LIKE ‘A*’

It is also important to note that LIKE statement when used in Access is case insensitive. Using the above example, any value that began with “A” or “a” would be retrieved.

Those are basics of using the LIKE statement. I think you will find it increasingly useful as your skill with Regular Expressions increases.