One of the easiest ways to make sure that your tables are consistent throughout your Microsoft Word documents is to create one, and then use that as the template for the others. Basically, once you create your template table you cut and paste it to other parts of your document to use it as a base. Sounds simple right? It is, however sometimes it can be difficult to select all the rows and columns in your table by highlighting them. This can especially be true if your table is large and spans multiple pages and/or the rows of your table break across multiple pages.

Read the rest of this entry »

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.

See the new articles below:

• It’s been a little while since I’ve written an article on Microsoft Word, so I thought I would put together some tips on making table creation easy. They save me lots of time, I hope at least one or two of them help you as well.
• Today’s Tip of the Day deals with the tricky beast that is grammar check.

Inserting a table into your Microsoft Word document is pretty easy. However, getting it to look and act exactly how you want can be challenging. In light of this, I’m going to share five common practices I use to make my life easier when I’m designing tables.

• Use the TAB key - Need to insert a new row at the end of your table? Don’t bother clicking through the menus, simply press TAB in the last cell in the last row of your existing table and a new row will be added.
• Add multiple rows - Another shortcut for adding more than one row to your table, simply highlight cells in more than one row and insert a row as you normally would. This will insert a number of blank rows equal to the number of rows you highlighted. For instance, if I highlight the first cell in two rows of my table, then click Table -> Insert -> Rows Above, then two blank rows will be inserted above the first row with a highlighted cell. This will also work for columns.
• Easily make your table fit - This is a problem that I’ve run into multiple times: Making my table fit onto a page. Even if it’s landscape, I still may have a problem. I usually see this when I’m porting over tables from Excel. If you work in Print Layout View like me, then resizing this table can be difficult. However, if you just switch to Normal view, you can view the whole table and easily manipulate its size.
• Make your tables consistent - This is more of a design tip for your document. Making your tables consistent in your document adds a professional touch. Once you create a table, use it as a base for your other tables (read: cut and paste) if you can. Not only does this save you time by having the table pre-formatted, but it also saves you time because you don’t have to design a new table layout each time. By adding or subtracting a few columns, your table is quickly done.
• Use Auto-Numbers in your table - If you are creating any kind of table where each row has a unique number (like steps), then make sure to use the Auto-Number function for these cells (the little “1..2..3″ icon). This way, when you add a new row, that cell will automatically be populated for you. Also, the other numbers will adjust themselves if you make changes in the middle of your table.

One article update and a new Tip of the Day, see below:

• Like that update title? Check out today’s Tip of the Day concerning maxing out your 401k match bonus.
• If you’re having trouble getting your tables right in Microsoft Access, maybe it’s because you’re not following a good design process. See the latest article for Microsoft Access about the Basic Table Design Process.

How you design your tables can make or break your database. It doesn’t matter what you have working on top of it (i.e. forms, code modules, etc.), if your underlying table structure is not sound you’re in for a lot of headaches. With that being said, designing tables for your database doesn’t have to be torturous, as along as you follow a few basic guidelines.

First, make sure you fully understand the purpose of the database. If you don’t, you’re not going to be able to create all of the right tables and relationships to make it work correctly. Get the requirements from your client (which could just be your supervisor), and make sure that you ask questions about anything you are not sure of. One thing is for certain in any kind of project lifecycle: The farther down the road a problem gets, the more costly it is to fix it.

Next, before you create even a single table you need a Database Blueprint. This is basically the structure for your database. It should include all of the tables that you want to create as well as their associated fields. Also, the Primary Key(s) for each table should be specified. Once this is in place, creating the tables themselves is quite simple.

How do you go about creating this blueprint? First, you need to decide what tool you are going to create the blueprint in. Unless your database is going to be pretty large, you can probably use Microsoft Excel. If you think your database might be relatively large (say 20+ tables), there are a number of commercial software products out there that are specifically designed for database design. These can get expensive, but they are worth the money in the long run.

Let’s say you decide to use Microsoft Excel. I would suggest creating a template to display the table layouts, then creating a separate Excel document based on your template for each planned table. Some of the basic fields in your template should be:

• Table Name
• Field Name
• Field Data Type
• Field Size
• Field Description

Also, you should highlight the Primary Key(s) in some way so that they are easily identifiable. The Field Description is especially important to include, because if somebody who is not familiar with the database needs to change it later on, these descriptions will help them to understand how the database works.

Now that we have our template ready, it’s time to get down to work. Layout all of the information that you think you are going to need to store in the database. For example, if I’m creating a database to store customer information, I’m going to need their names, addresses, payment information etc. After you list all the information you need, group this information into logical sets. You can use the same field more than once if you have to.

Here’s an example containing the following fields:

• Customer Name
• Customer Address
• Customer Payment Type
• Order Number
• Order Description
• Order Due Date

It looks like I’ll want to create two tables out of this: One to hold the first three fields, and one to hold the last three. We’ll call them “Customer Information” and “Order Information”. The initial tables are set, but how will they relate to each other? This is the next step: Determining the table relationships. In the above example, it looks like I’ll want to be able to link an order to a specific customer. To do this, I’ll add the “Customer Name” field to the “Order Information” table. This way, when we are processing an order, we can use the “Customer Name” to do a look up on the “Customer Information” field to get their address and payment information.

*Quick Tip: If you can make your table lookups use number compares instead of text compares, do it. Text compares are generally slow than number compares. In the above example, you could add a unique ID number per customer then use this ID number on the “Order Information” table instead of the “Customer Name”.

Also to note, I used descriptive field names in this example. This is something that you must be aware of every time that you are designing any kind of database. Your field and table names should always be descriptive of what they represent. For example, if you name your tables “Table 1, Table 2,…” you are just going to get confused later on as to what table holds which information. This is especially true in large databases. Not only will you get lost in it, but anybody else who tries to maintain it will be unable to do so as well.

Now decide what should be the Primary Key(s) of each table. You can have more than one, and this is common in complex databases. For our examples, let’s make “Customer Name” the Primary Key of both tables.
Your Primary Key needs to always be a unique identifier for a single row on the table. This is an important decision to make, as it could have major consequences to your design later on if you have to change it.

In summary, we have accomplished the following:

1. Have a full understanding of our database requirements.
2. Decided to use Microsoft Excel to create our Database Blueprint in.
3. Created our template to use when designing our tables.
4. Decided what information we’ll need to store our database.
5. Sorted this information into logical groups (our tables).
6. Defined the relationships between our tables.
7. Completed our design for each table

Our Database Blueprint is now complete. Take another look at it to make sure you didn’t miss anything. It’s also a good idea to have somebody else review it to see if they can add any input.

You’re now ready to actually build your tables. Remember, you can change something if you have to, but make sure to keep your designs updated at all times. This is extremely important because if your designs are not up to date, you have to go into the database each time you or anybody else has a question about a field.

Three new articles today (one involving hair, hence the title), check it out:

• Check out the first part in our Tales of the Metro series for a “hair-raising” experience (I slay myself).
• Want to embed a Microsoft Word document in a Microsoft Access table?  See the new article on How to Embed a Document in Microsoft Access.
• The next part of our “Surviving a Meeting” series is here! Take a look at Surviving a Formal Meeting - Part II for tips on how to act once your in the meeting.

As always, feel free to comment on these articles or send us feedback via the Contact Us page.  We want to hear from you!

This is a question that I get more often than you might think:  How do you embed a document, such as a Microsoft Word document, into a field in a table in Microsoft Access?  It may seem like the answer to this would be complicated, however it actually is quite a simple process.

To begin, let’s define what you will actually be embedding.  You will be embedding what is called an OLE Object.  OLE stands for “Object Linking and Embedding”.  It is a standard created by Microsoft that allows you to create an object in one application (say a Microsoft Word document) and embed or link it into another application (such as Microsoft Access).  For a slightly more detailed definition from Webopedia, click here.   In our example, I will be using a Microsoft Word document.

First, go to your table in Design View.  You can do this by following steps 1-3 shown below:

1. Click on Tables.
2. Highlight your table.
3. Click on Design View.

You should see a screen like this come up:

 

This table has two fields.  One is the ID field, which is just an auto-number to be our primary key.  The other field is the test field.  This is where we will be embedding our object.  It has been defaulted to Data Type “Text”.  Change this to “OLE Object”.  Close the table and save the changes to it.

Now, let’s embed a Microsoft Word document into a sample row.  Open the table by double clicking on it. You should get a screen like this:

Right click on the empty test field of the first row.  Select Insert Object from the menu.  The following menu will appear:

Here are your options:

1. Create New - You can pick an object to create from the list box and create an empty object of that type into the test field.  You can then double click on that field and that object will open up in the native application.  For example, a Microsoft Word document will open up in Microsoft Word.  You can then save changes and it will be stored in the database. *Note: be careful, if you just close a document while it is open it will save, make sure to undo your changes before closing if you don’t want them to be saved.
2. Create from File - You can copy an existing object into the database, and then edit that copy in the database (like for a new object) without affecting the original object.  If you choose to link it (there is a “Link” checkbox for this option), the original object will be changed when you edit it in the database and vica-versa.

Choose either an existing Microsoft Word document or create a new one and click “Ok”, and there you have it.  Try editing the document for yourself and see the results.  Also, play around with the options a bit, this tutorial is just a quick way to get started with embedding objects. There are also multiple different options for using this in Forms, depending the purpose of your application.