I thought I’d mention this site, as it’s a great resource for Microsoft Office articles. Basically, it aggregates these articles from multiple sources (with the author’s permission) and provides a little synopsis for each one and a link to the original article. I like this site because it’s quick loading and it provides an easy to use interface. Another good feature is that it’s not littered with ads like a lot of the other aggregation sites out there.

It’s definitely worth perusing if you have a chance, as there articles on a ton of different Microsoft Office related topics. Here’s the link:

Microsoft Office Reporter.

As I have talked about in previous articles, there are many areas where Microsoft Access and Microsoft Excel overlap. Because of this, there exists the ability to quickly interchange data between the two programs. In this article, I’m going to go over a few simple steps to transfer all of the data from a table in an Access database to an Excel spreadsheet.

Read the rest of this entry »

This is the second part in my two part Access Vs. Excel series, where I’m basically trying to differentiate when you would want to use one of these tools over the other for a project. This question comes about because of the many similarities that these two tools share. You can see the first article here: Access: Vs. Excel: When to Use Access.

Here are three example cases where I think you would want to use Excel over Access:

• You need to create a one-time, formatted report - If you’re creating a one-time report based on a set of data, then Excel is really the way to go. On the average, setting up a new report model in Access takes longer than creating a new report model in Excel. This is because in Access, you have to setup the database and other backend structures, while in Excel you can pretty much start plugging away from the get-go. If it’s only a one or two time report, you don’t need to take the time to setup a whole database for it. Plug the data into Excel, throw a format at it, and you should be good to go.
  
  
• The relationships/fields won’t change - This basically means that once you put the report together, the structure of your data won’t change, and neither will the relationships. For example, say you are creating a spreadsheet that shows all of your customers and key parts of their company info. This is pulled from a hidden spreadsheet that contains all the info from each company. This is fine, however, if you want to add new fields to the company info, this could cause cascading cell reference problems. Also, if you have some complex relationships between data fields, it’s much harder to check to make sure that they are still intact and correct. As long as your backend is pretty set (relationships and fields), Excel can be a good tool because of the shorter setup time involved.
  
  
• The report format won’t change - Don’t get me wrong, I think that Excel does have some flexibility when it comes to changing an existing spreadsheet. However, once you get past a certain point of complexity, it gets very difficult to make changes that don’t break other parts of the spreadsheet. For example, if you have a bunch of different formulas with cascading dependencies, changing just one requires you to check in multiple different places to make sure that nothing broke. I prefer Access for these kinds of reports. However, if you don’t have to change your format too drastically, I recommend Excel.

It’s critical to understand that both Access and Excel have their uses. Sometimes these overlap, most of the time you can clearly see which one would be better to use. It may not seem like it now, but as you get more familiar with both tools, you’ll see which one is better to use in certain situations. Also, there are always exceptions to the rules. Maybe your client doesn’t have Access, maybe they don’t like to use Excel. You might be forced to use one, all you can do in that situation is roll with the punches.

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.

Check out the new article on using the SQL statement AS for a quick tutorial on how to use this powerful keyword.

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.

That’s right, it looks like it’s going to be a beautiful day in my neck of the woods. To celebrate, I have a new Tip of the Day and an article for Microsoft Access for you.

• The Tip of the Day deals with saving your co-workers sanity by writing down their answers to your questions.
• Another sanity saver for Microsoft Access deals with descriptively naming your controls.

Picture this: You’ve just inherited a database from its previous owner. You’re new to the project, but you have substantial database experience. Opening up the database for the first time, you begin to take a look around some of the forms to try to get a feel for how everything works. The first Control you look at is named “Text9″. Trembling, you look at the next one: “Label7″. This is when the horror sets in.

Does this sound familiar? If you’ve ever been in an experience like this, then you know how important it is to take the time to name your Controls. In case you’ve never been in this situation, here are three compelling reasons why you should do this:

• It will make it easier for others to learn how the database works -Descriptive names will allow someone who may have subject matter expertise but no experience with your database to pick up the structure quickly.

• It will make the database more maintainable - This somewhat coincides with the first point, except it also applies to you. If you have worked on a part of the database for a few months and then suddenly you have to fix or change something, meaningful names will allow you to get right back into the swing of things.

• It will keep you sane - This is absolute truth. When you’re knee-deep in development, trying to work with “Text9″ and “Label11″ is going to drive you mad. Do yourself a favor before you go off the deep end.

Note: For our purposes, we’ll define a Control as pretty much any item you can place on a Form. Some examples of this would be a list box, text box, label, etc. Basically, anything that you put on a form should have a meaningful name.

There are two basic parts of naming a Control: Physically naming the control, and naming conventions. These are described below.

How To Name a Control

This is simple. First, right click on the Control you want to name and select Properties (Alternatively, you can double-click on it). The window pictured below should open (in this example I used a Text Box).

The part circled in red is what we are concerned about. This is the Name field, and this is where you will be making your edits. When you’re done making the edit, simply close the dialog box and your changes will be saved.

Naming Conventions

If you have any programming experience, naming a control can be likened to naming variables. If not, don’t worry, it’s a simple concept to grasp. When you name your controls, you want to adopt the same Naming Convention for each control. A Naming Convention is basically the way in which you construct a name for each Control.

One example that I use is to capitalize the first letter in each separate word in the name. For instance, if I want to name a text box where the user enters their name, I might use “UserNameEntry” as the Control name. Alternatively, you could also use an underscore to separate the words, creating “User_Name_Entry”.

It is possible to use spaces in-between words, however I don’t advise this simply because it makes programming in the backend possibly harder since you have to make sure the full name is read when you reference it (if this seems Greek to you, don’t worry, it is more for the intermediate to advanced users). This is why I prefer the first example, since it is no hassle to use.

You can always come up with your own, just remember to be consistent and descriptive across the board.

Overall, the small amount of time it takes to name a Control properly is well worth it when compared the enormous amount of benefits that you’ll see.