Why Buy?

These database tools address specific needs and can save you a great deal of time. The tabs after the next one provide you information that may also help you in your database work.

Tools Overview

  • Access2Crystal allows you to convert your Microsoft Access reports to Crystal Reports. This can save you enormous time and money, versus creating the reports from scratch. Free Trial Version Download Available.
  • For Crystal Reports 8.5 and later, Crystal Calendar is a fast, simple way to generate a set of records based on a date or time, or as a series of numbers.
  • cViewSQL is a powerful database utility that lets you process SQL commands against a database using an ODBC connection. It supports ODBC compliant database (e.g. MS-Access, SQL-Server, Oracle, MS-SQL). Unlike many other SQL command processores, it's easy to run. Free 30-day trial.

2 Principles

Follow these database principles to save time and frustration:

  • The Atomic Principle. Early physicists and chemists discovered it is much easier to join atoms together than to split them apart. Remember this when designing your database.

    Use several individual fields rather than a single field that combines data elements (e.g., first name and last name) together. It can be difficult, and sometimes impossible, to extract the required data element from a combined field. However if you have individual fields, you can always find ways to join them together.

    An example of this would be to create fields for firstname and lastname rather than have a combined field for name. With separate fields, you can sort by lastname, and select data based on firstname.

    A combined field would make one, the other, or both difficult to do. While you are at it, include fields for title and initials, a common name (or nick-name), and maiden name. You may even want to include fields such as alias1, alias 2, dba1, and dba2.
  • The Singularity Principle. Use each field of a database for one purpose only. Do not use one field for several different things. Sometimes, a field has been used some of the time for one purpose and is then gets used for another purpose. Avoid this. Create a separate field for each role. Then there is no confusion about what a specific field is used for.

    Yes, you can have several variations of a basic field. The USPS, for example, has Address 1, Address 2, and Address 3. Each has a specific purpose.
    An example of double usage would be a field for apt/suite. This is a bad idea. People live in an apartment and work in a suite. So one is a residential address and the other is a business address. These should be separate fields.

Old Data?

When you design your application and database, most of the energy and time goes into how to load new entries into the database (or processes to modify the data already there).

But you also need to think about what to do when your data need to be removed. While it might seem simplistic just to delete those records you no longer need, this can cause other problems.

What do you do with those transactions when you want to delete the customer or product? If the master record is no longer there, the transactions may disappear from your reports. If you also delete the transactions, then the historical totals will be incorrect.

A safer approach is to include a field to indicate obsolete or deleted records. Keeping that historical data in your database will also keep your auditors happy. You just need to remember to exclude the deleted records from your reports. That is just a simple record selection rule to include in your report. Another approach is to create a separate, archival database for obsolete records. Export the records you want to archive, then delete them from the database.

Time Formats

Date Time fields in our databases include both a date and a time component. We are all pretty clear about the when things happen at 2am or 3pm. But times like midday and midnight can be confusing. It's usually best to use a 24 hour time format.

The Time component can be displayed in several formats, so we recommend you select a format and hard code it in your report. Regional settings can vary from machine to machine and this can change the way they display to different users.

Midnight is a time value of 00:00:00 and midday is 12:00:00. This will appear as 12am and 12pm respectively. The place it causes confusion is when you add ten minutes to 11:58am to get 12:08pm, which is followed by 1:08pm an hour later.

To avoid confusion, always use 24 hour time, and keep away from that am/pm format. It is too easy to slip into the wrong 12 hour period. This applies to both field display and parameter input in your reports. There is an exception, however. Many people are uncomfortable with a 24 hour clock. This is probably the only reason to ever use a 12 hour clock. It's a concession to custom. That's why we still see 12 hour times given on Websites that operate 24 hours a day and in all time zones.

Date Formats

If you are working with date time fields and care about business hours between 7am and 7pm (or slightly later) only, then regular date time fields with local time will be fine.

But if your clock runs 24 hours and/or 7 days a week, and you cover multiple time zones, or have daylight wasting time, then you need to be careful. Real-world examples of such environments include air travel, factory processing, and energy billing.

You will need two date time fields in your database. One with the local time, and one with the time in UTC (Universal Time Coordinates). UTC is what we used to call Greenwich Mean Time (GMT). There are two hours a year when you cannot do accurate time variance calculations using local time only. These are the hours when daylight wasting moves forward and back--they will make these calculations wrong. You need an equivalent period without the daylight wasting component. UTC is the solution to this.

Rounding

We rely on our databases for accurate calculations. We like to see information generated "on the fly" from raw data. But a bias among managers, going back to the early days of spreadsheets, can lead us to thwart this process and introduce inaccuracy.

For this tip, we're going to look at spreadsheets. You can apply the lesson to your particular database program, and you may need to apply it to the spreadsheets that feed those database programs. Today, Excel is the dominant spreadsheet application. Though Excel is in wide use (or rampant use, depending on your viewpoint), many users don't understand the basics of the program. When it comes to rounding, that is certainly the case.

By default, Excel rounds a number for display purposes but uses the unrounded number for calculation. This means you may see an "error" in the math--but what you are really seeing is display vs. actual. A way to fix this is to increase the number of digits displayed. But the display is really for reference. What counts is the calculation.

If you use the ROUND function before the final calculation, you make display and actual agree the whole way through. But then you are losing accuracy at every step by forcing Excel to work with the rounded numbers.
 
Understand three things when creating financial calculations in Excel spreadsheets:

  1. Excel does the calculation with actual numbers.
  2. If you format the numbers as currency, Excel displays the product of each row as rounded. But, it maintains the actual (unrounded) numbers behind the scenes for purposes of calculation.
  3. The total for each worksheet is rounded for purposes of display by the "format as currency" command.

Let's say you keep this in mind, and thus don't use the ROUND command except for the final number. (If you use currency formatting, you don't need to use ROUND at all). That is, you don't use ROUND during calculation. This will result in a maximum error of less than 0.49999 cents (we can assume 5 decimal places is adequate, for purposes of illustration here).

So, whether you have 10 rows or 10,000 rows, your maximum error will be statistically insignificant.

But, that's not true if you are rounding during the calculation process. The maximum error, if using the ROUND function for each row would be (0.49999 * N), where N is the number of rows. So for 20 rows, the maximum error is then $10.

Excel is good to 255 digits (for obvious reasons--256 being the square of 16). After that, it simply truncates the number at the least significant digit (I'm 99% sure about the truncation, rather than rounding). You can display all 255 digits, if you choose. For financials, you should choose not to--that follows the aesthetics convention of showing only two decimal places.

But aesthetics and calculation are going to have differences. It's the calculation that you want to rely on.

cViewSQL Command Processor
cViewSQL is a powerful database utility that lets you process SQL commands against a database using an ODBC connection. It supports ODBC compliant database (e.g. MS-Access, SQL-Server, Oracle, MS-SQL) More Info
$245.90
chlsdbsqlcp
Crystal Calendar for Crystal Reports
For Crystal Reports 8.5 and later, Crystal Calendar is a fast, simple way to generate a set of records based on a date or time, or as a series of numbers. More Info
$199.99
ckw-crystalcalendar
iT Analyzer
Organize and manage your iTunes music library. Designed particularly for Crystal Reports administrators and other IT professionals. More Info
$99.00
ckw-itanalyzer