new employee query tasks

Welcome to your new position! Ditto what JoAnn Strommen‍ said, plus a few of my other thoughts.


If you haven't already, I'd browse through all the tables first. Maintaining clean tables is essential to a clean database, in my opinion, and this may also lead to queries you'll want to build to find records that needs cleanup. For instance, when I started with my current database 6 months ago, I found that Spouse names were being kept in a attribute at some point in the past, which is not what we want for our database, and where the bulk of them are in the Relationships. So I built a query and have been working my way through, by descending last gift given, and fixing them. 1500 to start with, down to 300ish now.


Check out your duplicate constituent report too.


I also query for blank dates where applicable (for me, this is in addresses, relationships, and constituent codes, also campaigns, appeals and funds), blank countries in addresses, blank mandatory fields, and if addressees/salutations are editable.

Melissa, I'm sure you've gotten a ton of great query ideas from the three awesome ladies before me. Since you are in University, be sure to also check out your alumni records. Make sure they all have class years, start and end dates, graduate/nongraduate statuses, and the like. When I came on board, that area was nowhere on my radar, and so it was a huge mess when the day came that our Development department was tasked with creating an alumni directory. Yikes.


As Heather suggested, look at your tables for incorrect table entries. It's very common for people to mis-type an appeal name or title, and then accidentally add that to the existing table. I found words like "Susan" in our Titles table, as well as duplicate appeals. (Which is a mess, because as far as I know there is no "Merge Appeals" option.)


Lastly, in addition to missing or incorrect fields, look for items that you want to add for better data maintenance. I added a required "Donor Source" attribute, as well as a "Deceased info Source", "DNS Source", and "Append Source" attributes, each with corresponding Description tables and dates, to better track the consistency of our data sources and maintenance plans. These fields allow us to track our demographics, verify the accuracy of biographical data, and better document our permissions information. I can run a list instantly on how many donors came on board last year from events, acquisition mailings, college outreach, online gifts, and peer-to-peer sources, along with their respective retention rates and gift levels -- which helps inform us on better appeal segmentation and welcome program design -- all because of one added attribute.

Our previous database was a mess, so we started even more basic. I ran queries for "&" in first name since many spouses were not entered as relationships. Had to repeat for "and".... I also had to run "UK", "unknown", "none" etc on address lines and cities in order to check the no valid address boxes (really frustrating running mail merges only to find out someone lived on Unknown St. in Unknown city!); also ran queries on constituent codes and found many Orgs in Individual accounts. Those were fun, too. First name: National with last name Bank for example. As you start, you will think of so many more. Be patient. Data clean up is LOOOONG but makes maintenance so much easier.