The Cardinal Rules of Microsoft Access and Excel

I. If it can be subdivided it must be subdivided.
The more you subdivide information into separate columns, the more versatile your database will be in the future when you want to develop charts for presentations, interactive maps, and other codependent documents like mail merge or XML.

II. Be specific when using column headers.
Make sure you can wake up the next morning and know exactly what should go in a column by its header description. If need be, you can use the “Insert Comment” to further describe the content requirements.

III. Never a full name in one cell.
A first middle and last name are three different things. Use three different columns.

IV. Never first names first.
Why always start with last names first? I’m not sure. The reasoning might be cultural in origin, but it is the official standard of phone books, libraries, and the government.

V. If you must use nicknames, give them a separate column.
If you substitute someone’s formal first name with a nickname, later you will never find Rick no matter how hard you search for Richard or Dick.

VI. Dividing punctuation requires dividing columns.
If you have a comma or semicolon in several cells, you’re in trouble. If you know you will need three or more items as part of a record, for example, the identity of multiple printers a person must access, then set a limit of five and create five columns.

VII. Special requirements need special paperwork.
Create an external document reference column.

VIII. Three dimensions require three columns.
Height, width, depth, or X, Y, Z; keep them seperated, it may seem like your using too many olumns at first, but you will be glad you did later.

IX. Pick one way to describe something and stick with it.
Don't mix building, floor, and section descriptions with cartographic like South West Corner, SW, NW. Keep them separate too.

X. Pick one column order and stick with it.
If you're going to have other people take copies of your spreadsheet out on PDA's or laptops to collect information, don't then add or rearrange columns and expect them to get resolved later in a timely and accurate manner.

XI. Keep comments to the far right.
Columns labeled “Comments,” or “Reason,” interfere with data that can be collected while holding a laptop in one hand and typing with the other. They should not separate columns that are needed to accurately select the field where data needs to be entered.

XII. Use complete dates.
Be sure to include the year, month, and day every time you use a date.

XIII. Keep time out of the date column.
The time and date format in Excel is too long to effectively sort and will cause problems down the road.

XIV. Get all the names before you start.
Make sure you know exactly what you need before you start. If rows need to be added in the process of data collection, you will add hours to the process of reconciling the remote database with the central database. Or, make sure your database is organized in a manner that allows for new records to be entered during the collection process with the remote copy of the spreadsheet or database.

XV. Keep a column for sequential record numbers.
If you want to import your Excel spreadsheet into an Access database, you will need a “Key” field. Each record should have a record number field because you won’t be able to import the row number from Excel as your key field in Access.

XVI. Don't use a Microsoft excel spreadsheet to collect remote data. Use Microsoft Access with a designed data entry form to prevent entering data in an adjacent cell.