When I see bad data in a database, there are several things you need to look at to improve the outcomes of your reports. In general this is all going to fall under GIGO: Garbage IN, Garbage OUT. Cliche, but TRUE! Here are my top 5 tips to help control your data quality:
- Watch out for NULLS, which tend to break reports and formulas. Use NZ to plan for bad data or nulls. Example: nz([Income],”N/A”) will produce a result of N/A when there is no Income value.
- Put threshold rules on key fields. Example: If you have a date of birth field, add a rule for <date(). This ensure people do not accidentally enter 2107 as a year (I see this all the time).
- Use conditional formatting. Create conditional format rules that change the field color and font to highlight deviations which are out of our normal ranges. I like to build formula based rules on number fields where I can use something like +/- 10% as my test. You have to first understand what normal should look like before making these types of rules though.
- Set critical fields to not allow Nulls
- Create useful ControlTip on the Other Properties tab to better inform a user. This will display on the screen when the mouse hovers over the field.