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.

I thought I would write an article on Microsoft Visio since I haven’t written one for awhile, and also because it seems that a lot of people are not very familiar with this great tool. Visio is an extremely powerful application, and more people should take advantage of it (*steps off soap box*).

This article will focus on some common Visio Flowchart shapes and what kinds of uses you should put them to. You can find a detailed definition for a Flowchart from this link to the Wikipedia definition.

Here are the shapes we will be discussing:

1. Process Block - You can use this shape to describe “processes”, which can be defined for our purposes as a certain action being taken. Some examples of a process could be:
   • Performing a calculation, such as “Calculate the average of all the sales”.
   • A group taking an action, such as “The Product Test group validates the software modules”.
   • A generic action, such as “Document is placed in the tray”.

   These are normally the building blocks of your Visio diagrams.

2. Decision Symbol - As the name implies, you would use this symbol to branch out your diagram based on a decision that is made. You can think of this as an “If” statement (for those familiar with programming concepts).

   There are two basic ways to use this symbol:

   • Yes/No Path - This has two outcomes, Yes or No. An example would be “Is the average greater than 0?”. Based on the answer, you would either go down a “Yes” path or a “No” path.
   • Multiple Path - The flow can go multiple ways based on the outcome. For example, if could you “Choose location to check”, and then each location could be a path.

   The key here is to make sure you label each path, even if it is with a “Yes” or a “No”. Otherwise, you won’t be able to navigate your diagram.

3. Terminator Symbol - This is what you use when you want to end or start your diagram. Normally, you would have one at the beginning labeled “Beginning of (process)”, and one at the end labeled “End of (process)”. However, if your diagram can end in many places, you should have a terminator for each place it can end. This also works if you have multiple beginning points, but this is less common. Make sure to give them descriptive names instead of just “Begin” and “End”.

4. Document Symbol - Pretty obvious explanation here, but you might find yourself using it a lot. If you have any kind of document that you are trying to represent in your diagram, you should use this symbol. For instance, if the input to your process is a file, it would be represented by this symbol. Another example would be if the output of your process is a file.

5. On Page Reference - This should be used if you need to “jump” back to another part of your process in a different part of the diagram, but can’t connect them using a standard connector. Also, this can be used to modularize your diagram. For example, if you have a repeatable process (like a “Function” for those familiar with programming terms), you could reference that process by just using this symbol instead of repeating that part of the diagram multiple times. You should use this when what you are referencing is on the same page as this symbol.

   Another important point is that these symbols work in pairs, like two ends of a connector. The key is to label them both the same thing so that you know which reference goes where. I use numbers, which seem to work fairly well for small diagrams. However, if your diagram is more complex I recommend using more descriptive text like “Determine customer payment information function”.

6. Off Page Reference - This has pretty similar uses to the On Page Reference, however the key difference is that you would use this when what you are referencing is on a different page than the symbol. Using an example above, if you were to have repeatable functions in your diagram, you might just have one page where the details for it reside and then use this reference when you want people to skip to it. This way you don’t have to repeat the same set of symbols multiple times in your diagram. This also creates a cleaner, less cluttered diagram.

7. Database Symbol - I think you would use this more when designing software processes, as it represents a connection to a database or more specifically a table in that database. For example, say part of your diagram writes a value to a table, then that table should be represented by this symbol. This symbol should be labeled with the name of the table if there is only one database, if there are multiple databases than the name of the database should preface the table name.

Shape Location

Shapes 1-6 are located on the Basic Flowchart Shapes menu, while Shape 7 is located on the Miscellaneous Flowchart Shapes menu. Both of these menus can be accessed by clicking on File -> Shapes (”Stencils” in some older versions of Visio) -> Flowchart.

These are just some of the more common shapes used when creating a Flowchart. As you design your diagram, explore the different shapes available to you in order to better tailor your diagram to your project’s needs.