Month: December 2020

The SQL Server Import and Export Wizard allows you to quickly and easily import information into a SQL Server 2014 database from any of the following data sources:

• Microsoft Excel
• Microsoft Acces
• Flat Files
• Another SQL Server Database

The wizard builds SQL Server Integration Services (SSIS) packages through a user-friendly graphical interface.

Starting the SQL Server Import and Export Wizard

You may start the SQL Server Import and Export Wizard directly from the Start menu on a system that has SQL Server 2014 already installed. Alternatively, if you’re already running SQL Server Management Studio, follow these steps to launch the wizard:

1. Open SQL Server Management Studio

1. Provide the details of the server you wish to manage as well as the appropriate username and password (if you’re not using Windows Authentication)
2. Click Connect to connect to the server from SSMS
3. Expand the Databases folder under the name of the server in the left-hand pane.
4. Right-click on the name of the database you wish to use and select “Import Data” from the Tasks menu

Importing Data to SQL Server 2014

The SQL Server Import and Export Wizard provides you with a guided process to import data from any of your existing data sources to a SQL Server database. In this example, we will walk through the process of importing contact information from Microsoft Excel into a SQL Server database. In this example, we will bring the data from our sample Excel contacts file into a new table of a SQL Server database.

Here’s how to get started:

1. Open SQL Server Management Studio
2. Provide the details of the server you wish to manage as well as the appropriate username and password (if you’re not using Windows Authentication)

1. Click Connect to connect to the server from SSMS
2. Expand the Databases folder under the name of the server in the left-hand pane.
3. Right-click on the name of the database you wish to use and select “Import Data” from the Tasks menu
4. Click Next to advance past the wizard’s opening screen
5. Choose Microsoft Excel as your data source
6. Click the Browse button, locate the address.xls file on your computer, and click Open
7. Verify that the “First row has column names” box is checked
8. Click Next to advance past the Choose a Data Source screen
9. On the Choose a Destination screen, select SQL Server Native Client as the data source
10. Choose the name of the server that you want to import data into from the Server Name drop-down box.
11. Verify the authentication information and select the options corresponding to your SQL Server’s authentication mode
12. Choose the name of the specific database you want to import data into from the Database drop-down box
13. Click Next to continue
14. Click Next to accept the “Copy data from one or more tables or views” option on the Specify Table Copy or Query screen
15. In the Destination drop-down box, choose the name of an existing table to import the data into a table that already exists in your destination database or type the name of a new table that you wish to create. In our example, we will use this Excel spreadsheet to create a new table called “contacts”
16. Click Next to move past the Select Source Tables and Views screen
17. Click the Finish button to skip ahead to the verification screen
18. After reviewing the SSIS actions that will take place, click the Finish button to complete the import. Depending upon the size of the file you are importing, this may take a few minutes.  If you are using our sample Excel file, it should only take a few seconds.

Exporting Data from SQL Server 2014

The SQL Server Import and Export Wizard provides you with a guided process to export data from your SQL Server database to any supported format. This example will walk you through the process of taking the contact information you imported into a SQL Server database in the previous example and export it to a flat file.

Here’s how to get started:

1. Open SQL Server Management Studio
2. Provide the details of the server you wish to manage as well as the appropriate username and password (if you’re not using Windows Authentication)
3. Click Connect to connect to the server from SSMS
4. Expand the Databases folder under the name of the server in the left-hand pane.
5. Right-click on the name of the database you wish to use and select “Export Data” from the Tasks menu
6. Click Next to advance past the wizard’s opening screen
7. Choose SQL Server Native Client as your data source
8. Choose the name of the server that you want to export data from in the Server Name drop-down box.
9. Verify the authentication information and select the options corresponding to your SQL Server’s authentication mode
10. Choose the name of the specific database you want to export data from in the Database drop-down box
11. Click Next to continue
12. Choose Flat File Destination from the Destination dropdown box
13. Provide a file path and name ending in “.txt” in the File Name text box (for example, “C:\Users\mike\Documents\contacts.txt”)
14. Click Next to advance past the Choose a Destination screen
15. Click Next to accept the “Copy data from one or more tables or views” option on the Specify Table Copy or Query screen
16. Click Next to advance past the Configure Flat File Destination screen
17. Click Next to move past the Select Source Tables and Views screen
18. Click the Finish button to skip ahead to the verification screen
19. After reviewing the SSIS actions that will take place, click the Finish button to complete the import.
20. When the export completes, verify that the file exists in your selected location.  You may open it using any text editor.

That’s all there is to using the SQL Server Import and Export wizard! Feel free to experiment with the wizard yourself, trying a variety of file formats and destinations!

As you may already know, databases use tables to organize information. (If you don’t have a basic familiarity with database concepts, read What is a Database?) Each table consists of a number of rows, each of which corresponds to a single database record. So, how do databases keep all of these records straight? It’s through the use of keys.

Primary Keys

The first type of key we’ll discuss is the primary key . Every database table should have one or more columns designated as the primary key. The value this key holds should be unique for each record in the database. For example, assume we have a table called Employees that contains personnel information for every employee in our firm. We’d need to select an appropriate primary key that would uniquely identify each employee. Your first thought might be to use the employee’s name.

This wouldn’t work out very well because it’s conceivable that you’d hire two employees with the same name. A better choice might be to use a unique employee ID number that you assign to each employee when they’re hired. Some organizations choose to use Social Security Numbers (or similar government identifiers) for this task because each employee already has one and they’re guaranteed to be unique. However, the use of Social Security Numbers for this purpose is highly controversial due to privacy concerns. (If you work for a government organization, the use of a Social Security Number may even be illegal under the Privacy Act of 1974.) For this reason, most organizations have shifted to the use of unique identifiers (employee ID, student ID, etc.) that don’t share these privacy concerns.

Once you decide upon a primary key and set it up in the database, the database management system will enforce the uniqueness of the key. If you try to insert a record into a table with a primary key that duplicates an existing record, the insert will fail.

Most databases are also capable of generating their own primary keys. Microsoft Access , for example, may be configured to use the AutoNumber data type to assign a unique ID to each record in the table. While effective, this is a bad design practice because it leaves you with a meaningless value in each record in the table. Why not use that space to store something useful?

Foreign Keys

The other type of key that we’ll discuss in this course is the foreign key. These keys are used to create relationships between tables. Natural relationships exist between tables in most database structures. Returning to our employees database, let’s imagine that we wanted to add a table containing departmental information to the database. This new table might be called Departments and would contain a large amount of information about the department as a whole. We’d also want to include information about the employees in the department, but it would be redundant to have the same information in two tables (Employees and Departments). Instead, we can create a relationship between the two tables.

Let’s assume that the Departments table uses the Department Name column as the primary key. To create a relationship between the two tables, we add a new column to the Employees table called Department. We then fill in the name of the department to which each employee belongs. We also inform the database management system that the Department column in the Employees table is a foreign key that references the Departments table. The database will then enforce referential integrity by ensuring that all of the values in the Departments column of the Employees table have corresponding entries in the Departments table.

Note that there is no uniqueness constraint for a foreign key. We may (and most likely do!) have more than one employee belonging to a single department. Similarly, there’s no requirement that an entry in the Departments table have any corresponding entry in the Employees table. It is possible that we’d have a department with no employees.

With the vast number of possible queries you can make it may be difficult to determine exactly what it is you need and when you need it. In this article we will take a look at two queries that become increasingly helpful (and powerful) as your database grows. When you are faced with ploughing through hundreds, thousands, or millions of lines of data, the SELECT TOP and SELECT TOP PERCENT queries

give you a way to comb through all of the data in a matter of seconds. Using either of these queries does require some knowledge of SQL, so this article is for more advanced users. If you are just starting out with your database, this article will not be very helpful to you right now. Bookmark it and return later when you are more comfortable with SQL and when the amount of data you have to deal with requires more than just a simple sort or filter can offer.

A precursory look at the names of the queries may make it seem as though they do the same thing, but they are both very specific as to how they are used. They are both universal to all databases, but this article focuses on how they are used in Microsoft Access. If you are at least familiar with SQL, you will be able to use the code for other databases.  

When to Use SELECT TOP

SELECT TOP is a way to look for a specific number of files that will be returned, such as the top 10 repeat clients, top 20 client sales, or top 10 countries for

sales. It is the quickest and easiest way to narrow your search to just the best (or possible worst) of any particular scenario. When you don’t have much data you aren’t going to have much use for this query, but as your databases grow, it becomes a requirement to quickly pull the data you need to concentrate on.

The reason running Select Top is better than trying to run a sort or filter is that it allows you to look at only the data that meets your criteria. You won’t have to copy/paste information from the database into a spreadsheet or anything else to exclude irrelevant information.

When to Use SELECT TOP PERCENT

SELECT TOP PERCENT performs a calculation before returning the data you want. In other words, it does more than just look at a number to determine if it is in the top X of all of the data; the query performs calculations to determine percentages.

For example, if you have a customer who buys $1,000 worth of your product at one time and another who is a repeat customer who buys $250 worth of your product every month, they will appear differently in the queries. Since $1,000 is considerably more money, it is more likely to appear in a list of top items, but with the regular customers they are much more likely to appear in a search for top 10% of top buying customers.

Executing SELECT TOP in Microsoft Access

While it is possible to create your own query from scratch, Access does make it easy to pick and choose what you need, and the program will fill in most of the required elements. The following will create the top 5 records. 

1. Create a new query by clicking on Create > Query Design.
2. Select the table or tables you want the query to run against.
3. On the Property Sheet to the right, find the Top Values option and change it to the desired top fields for that column. The default values for SELECT TOP are 5, 25, and 100. If you know SQL, you can go into the query and update it to a value that is not part of the default.
4. Open the query you want to change.
5. Click on Query Tools > Design > View > SQL View. A window opens and you can adjust SELECT TOP # to make # any number you want.
6. Save the change.

Executing SELECT TOP PERCENT in Microsoft Access

While it is possible to create your own query from scratch, Access does make it easy to pick and choose what you need, and the program will fill in most of the required elements.

1. Create a new query by clicking on Create > Query Design.
2. Select the table or tables you want the query to run against.
3. On the Property Sheet to the right, find the Top Values option and change it to the desired top fields for that column. The default values for SELECT TOP PERCENT are 5% and 25%. If you know SQL, you can go into the query and update it to a value that is not part of the default.
4. Open the query you want to change.
5. Click on Query Tools > Design > View > SQL View. A window opens and you can adjust SELECT TOP # PERCENT to make # any number you want.
6. Save the change.

Keep in mind that your queries will be tied to tables, so you need to be reviewing the tables themselves (not reports or forms). 

Practicing

While the process seems straightforward, it generally takes a bit of time to get accustomed to making your own adjustments. It is best to start with a sandbox version of a database instead of working with a live database. That will ensure that you don’t inadvertently change a query that someone uses or create any problems for yourself and others down the road.

The blockchain is the thing that ties bitcoin together. But how does it work?

Bitcoin is a decentralized technology, meaning that no one person controls it. This is great for people that don’t trust their banks, or bitcoin speculators for example, or simply people who prefer to take control of their finances themselves to help reduce their costs.

To make this work, bitcoin had to solve a big problem: it had to find a way for everyone in that decentralized network to agree who had sent bitcoins to whom, so that no one

could dispute it. That would prevent someone claiming to have sent money to someone else when they didn’t.

In short, bitcoin needed a general ledger, but it didn’t have a central place to store it. That’s where the blockchain comes in. But how does the blockchain work?

Inside the Blockchain

Bitcoin’s blockchain carries information about transactions on the bitcoin network. Every 10 minutes it adds information about the most recent transactions in the network. It does this in the form of a block of data, which is added to the end of the chain.

The first block in the blockchain, created when bitcoin first began, is the genesis block. It contains the first transactions that ever happened. The most recent block contains the transactions that happened during the last 10 minutes.

It is easy to find out which transactions happened in the last 10 minutes so that they can be written into the block. Whenever anyone makes a transaction with their bitcoin wallet, their wallet software broadcasts the transaction

to some of the other computers in the network. Those computers tell other computers, which then tell even more computers, and so on.

Avoiding Fraud

But what is to stop someone from going back and altering a block? Let’s say that you sold me some online music in exchange for bitcoins. If I send you the bitcoins using my software wallet, then the network sees that transaction, and relays it to you. Happy that you have been paid, you then send me my music, and everyone is happy.

But what if I am unscrupulous, and decide to reverse the transaction? What’s to stop me going back and altering the last block in the blockchain, deleting the transaction, so that the network has no record of me sending you any money?

To prevent this, bitcoin’s blockchain relies heavily on a particular branch of mathematics known as cryptography. It encodes information to make it secret, and enables people to prove that the information hasn’t been tampered with. That’s exactly what bitcoin needed to work.

Why Mining is Important for the Blockchain

Bitcoin does this using a process called mining, which helps to secure the blockchain. Miners are computers on the network that use their number-crunching power to secure the blocks in the blockchain, ensuring that no one else can tamper with them. Read about how mining works here.

Transparency

The bitcoin general ledger is secured by this tightly-bound mathematical chain of blocks, and because all of the transactions that have ever happened in the network are stored in the chain, it is also extremely transparent.

There’s even an online tool called the blockchain explorer that lets you look inside the blockchain. You can search for bitcoin addresses, or for individual transactions – whether they belonged to you, or not.

Try doing that with your bank.

Most people are adept at using numeric formulas because they are very similar between Microsoft Excel and Microsoft Access. However, text formulas are a little more complex, partly because people do not understand what they are or how to use them. A major part of the misunderstanding comes from the name; text formulas can do a number of things, including math with dates, but it is primarily a way

for you to manipulate text. It is commonly used for queries by more advanced users. When you first begin learning how to work with Access 2013, you probably skip over this function entirely, which is fine. As you gain more experience, you will begin to see how valuable text formulas can be because you will have been doing the work manually for a while.

Once you’ve spent time understanding Access and how to manage databases, you can start to venture into things like text functions to turn some of the painful manual processes into automated processes. Text functions spare you from having to manually count entries or having to create columns or rows that will count for you. Best of all, text formulas can be used for any field type, not just those that have text.

Text formulas can be very useful tools once you or members of your staff have seen what it is like having to do all of the work. However, they are very complex and usually they require you to learn coding to use them effectively.

How Can You Benefit from Text Functions?

When

you think of formulas, the only time you think of letters is when they represent variables. However, there are many other ways that you can manipulate entire words (not just variables) to get mathematical information from a database. The largest problem is reprogramming the way you think about math.

Perhaps the best example is physics. Everyone knows the equation E=mc² (even if they don’t know what it means). You can think of the formula as shorthand. E is energy, m is the mass of an object, and c is the speed of light (squared as the 2 indicates). E and m are variables, but c is a known numeric value, which could be used instead of the letter. Text formulas are similar, but there are no set values. As shown in the example, you could be dealing with dates, complete words, or a number of other less common uses.

You can translate dates from the default state (such as 21-AUG) into a numeric value (8) to indicate just the month. This can then be used to find out how many sales were completed for each month, determine how long a project took to complete, or track which months have higher than average customer complaints. Text formulas can be as simple as the use in Access total rows. For total rows, you can examine text to see how many entries there were for a particular column. Like Excel, Access lets you format numbers with text formulas.

When Should You Use Text Formulas?

There aren’t any differences between using a text formula and a regular mathematical formula besides the fact that it works with text. The easiest way to work with Access text functions and formulas is when you are coding, which is why it is one of the more advanced features. There are a number of different ways to use text formulas with Visual Basic to return things like string information, average number of letters in data entries, formatting text, and parse functions.

If you want to use the text formulas in Access, the total rows are the easiest and best way to use them. The limitation is that the formulas are pretty much only to run totals. They won’t format any of the information in columns or convert anything, but you won’t need to use any code or learn anything extra to use them.

When needed, you can use text formulas when you import data. This is the easiest way of ensuring that the formatting is consistent.

Using Text Formulas

With the majority of text formulas being utilized either through code or total rows, there are not many ways to use it for your everyday work. If you go into Access Help, for example, it will not return any information on how to work with text formulas. If you have the time to learn code to simplify working with text fields, the text functions website provides some very basic information to give you at least a basic understanding of your options and how you can use text formulas when coding.

However, when you are importing information, particularly from text files, into your database, there are some very easy ways to use text formulas to make sure strings go where they should. Text files do not include tables. Instead, data is divided by tabs, spaces, or other breaks to indicate when a field is complete and the next field starts.

You can create a query that will use text formulas to do this for you. Depending on what information is available, you will need to adjust the formulas. The website Mead In Kent has created a page that explains working text formulas into queries to make importing data easier and with fewer steps. 

As a marketing professional you already deal with so many apps and software packages you are probably wondering why you should have to deal with something that is designed specifically for databases. After all, you probably already have a number of Excel spreadsheets to handle calculations, contacts and email templates in your preferred email software, and marketing campaigns recorded in a third program.

This is exactly why you can benefit from an app or program that offers the organization of a database; it provides a centralized program to keep all of your information current from a single location instead of needing all of the different apps and programs.

Beyond making it easier to access information from a single place, databases give you the ability to pull reports so that you can track the progress of different campaigns. They also make it easier to ensure that information is entered consistently through forms, as well a way to verify information through queries. Any research that you conduct can be compiled and made available to others in the company through the database. There is even a type of marketing campaign that is database driven, and is aptly named database marketing.

Buy Versus Build

Your first question is likely to be whether you should buy a database or start from scratch. The answer is that it depends on how you plan to use the database. Even if you already have a wealth of different programs and apps and just need to centralize them, it may be easier to purchase a database than build one from scratch. Many database programs allow you to import data, making it both faster and easier to centralize your information. However, it is just as likely that you will find most software packages have more functionality than you actually need (or want).

While you are first thinking about a new database, do some research to see what kinds of databases are available. Marketing is one of the primary targets for database software because of its importance to companies. Too often people are willing to pay a lot for features they do not fully understand because there is a chance that it could be useful later. You already know that marketing is an area that is constantly on the frontlines of technological spread. You’ve seen a lot of promises and know that the majority of the time those promises do not pay off.

The best approach to finding the right database for your company is to sit down and determine which methods you currently use that you want to keep. As likely as not, something as basic as Microsoft Access will be more than adequate for the short term. It will help you get all of your information into a single place and get accustomed to maintaining a database. There are templates available to help you get started. Then all you have to do is schedule some time to get the database up and running; don’t forget to include some time for loading information from your other programs and apps to the new database. Access includes a number of help sections that cover things like importing contacts from address books.

If you find that the features you use in your different apps and programs require something more robust than Microsoft Access, set aside some time to see what kinds of databases are available that include the features you want. If there is an app for something, it’s almost guaranteed that there are several different software types that will work that feature into their programs. Look for a program that aligns with your company’s marketing style.

What a Successful Marketing Database Needs

Marketing requires that everything is easily connected and that queries can quickly and accurately pull the necessary information. Reports are perhaps the most important element that you will need for meetings and presentations. Marketing requires a lot of justification and analysis of the numbers to see what works and what doesn’t. You have to ensure that your database can pull all of the reports that you need to get the job done right. Relatively early in the process, you will need someone who understands how to query data and pull reports from the database.

You also need to be able to prepare and send emails and sales information to your customers. There are many ways to automate this process, but it requires that you keep all of your client and potential client information current. Setting up a regular schedule to update data and verify that it is correct will mean that you don’t have to worry about something being obsolete because no one has updated the information in over a year.

Finally, you have to be able to track the types of promotions and marketing projects. This gives you the data you need to determine how successful each of them is, as well as determine the best type of sale for different scenarios.

Marketing Research Databases

While it is possible to start your database with tables and forms for marketing research, the more successful your company is, the more appealing a marketing research database will appear. Between the research you do and the research done by others in your company, the data could reach a point that it needs a database on its own.

A marketing research database gives you a single location to store a period of weeks, months, or years’ worth of data. However, you may not need to create your own research database. There are established marketing research databases for numerous fields that are open and available (meaning they aren’t company specific). You can find these by running a simple search, and if you are interested you can even join some of them.

Database Marketing

Database marketing is an element that could be added to your regular databases or it could be broken out as a separate database. It is a way to collect and consolidate all of your consumer data for easier processing and analysis. The bigger your business, the more likely it is that you will need more space for your consumer data and information, leaving less room for other required data. Unlike a regular database, database marketing is a way to better study your customers, their responses to different information, determine when they are more likely to spend money, and compare results for different marketing strategies. Essentially, it is a place to store marketing history for analytical purposes.

You probably won’t need this early in your company’s history, but it may become a necessity later on. It is something to keep in mind as you create and load data into your database. If you already need to split out your customer history from other information, you will need to proceed with two databases. If you don’t feel that you need one yet, make sure to create your database so that this information is easily extracted by keeping marketing history separate from all of the other information, like your contact information. 

As a professional fundraiser, you know how important it is to have a reliable and easy to manage database. A good database makes it easy to manage, organize, and coordinate all of the intricacies of fundraisers, something you really need, especially if you are dealing with multiple fundraisers at the same time. Having the option to customize different parts of the database, such as reports and forms, makes the process considerably easier, but once things get going you won’t have time to create new ones or update existing ones. Even if you create the perfect experience for one fundraiser, you will be limited in how much of that process you can recycle because every fundraiser is different. You will have at least a few different vendors, a completely different set of donors (hopefully a lot of new ones), and a different beneficiary for each fundraiser. You need a database that is robust enough to handle a lot of data for multiple projects and is secure enough that your donors’ information is not compromised.

To better understand what functionality you will need in your database, spend some time getting familiar with the terms and database basics. While databases are complex, if properly implemented your users will never see the depths of complexity. The better a database is, the easier it is for users to enter information, and since fundraisers generally operate with volunteers, you

want to make the process straightforward so that any level of computer user can easily use it.

Buying a Database

If you have the time and energy you could make your own database, but with everything you have to keep track of, it is strongly recommended that you purchase the software you need. The software manufacturers have already figured out everything you will need to track (and there is a lot to track), and you really do not want to find out when you are well into a fundraiser that you left out something basic in your database. If you work with established software it will have wizards and templates already created, making your job so much easier.

When you start looking at software, you need to know what your primary foci will be.

• How will the campaigns be managed?
• Do you specialize in one or two types of fundraisers, or will you do any type requested by clients?
• Are there particular aspects of the process that you want to focus on?
• How will you coordinate donors and beneficiaries?
• What kinds of communication will you provide?
• How often will you backup your system and will you allow clients to have access to their information?
• What type of security measures do you want for the database (and does the database being sold meet minimum state and federal requirements for managing financial institution information)?

There are many other questions you will need to ask yourself, but these will get you started thinking. Sit down and go through the fundraising process, determine how you would like to manage and track information, and examine areas where you would like to improve the process. These are all things you need to consider when you buy your database. Make sure you have questions ready for customer service and shop around before making your purchase.

Managing Campaigns

One of the easiest ways to handle a lot of different clients and fundraisers is by breaking them up into campaigns. Of course you will need to have some variation between the different types of campaigns so you can create a similar flow and process that you can manage. As long as you track how you treat each type of campaign, you can streamline all of them at the same time. When you find a way to improve one campaign type, you can evaluate it against the others. This not only makes it easier to keep your fundraisers efficient, it keeps the process consistent.

Planning for a campaign is generally the most time consuming part. You need a lot of information upfront and you have to spend a lot of time planning communications, deadlines, and events. You need a database that will track all of this from the beginning so that there is no question about what comes next, who has pledged to donate, or what the current value of the campaign is.

You also want to have a way to compare your campaigns. If one was particularly problematic, you need to be able to see what was different between it and one of your highly successful campaigns to see where mistakes were made and how they can be fixed in the future. If you had a campaign that was wildly more successful than expected (such as the Ice Bucket Challenge), you want to spend time analyzing why it went so much better than anticipated. Campaign comparison lets you work to reproduce the successes and avoid making similar mistakes to the lowest yielding fundraisers.

Beyond Simple Data Entry

As a company that specializes in fundraising, you know that there is much more to managing than simple data entry. Perhaps it isn’t as critical as that of a hospital database, but with so much financial information, you have to contend with a lot of rules and regulations. You also have to deal with many different types of relationships and emotions. Any time you are working on a special case (and this can happen frequently), you need to have reliable notes to make sure that communication meets the needs and expectations of your clients. You need to adopt their tone and style in your communication, which means that you will have a lot of different tones to manage between the different campaigns.

You also have to consider extras that customers will want, such as gifts and awards. For example, silent auctions will require you to track all of the bids and ship each item to the winners. Other times customers may want you to send a gift to donors who spend or give at least $50. Even after the main event is over, you will still have a lot of work to do analyzing and finishing up the campaign.

Database Security

The biggest concern of any company that deals with financial information is security. You want a database that is extremely difficult to hack and that has different levels of access based on the needs of the user. However, you also want the processes to be as automated as possible to reduce human error. It is a fine line, and one that is very difficult to balance. If your clients are located in different states and countries, you will need to be aware of the rules and regulations for all of them, and you will need to ensure your database meets them all. You will need to create the databases to reflect what security measures are necessary. 

Securing databases is especially important for fundraising professionals given the sensitivity of donor information in many nonprofit organizations.  Normally information about donors is restricted to only those staff with an explicit need to know and the amounts of gifts are treated as one of the organizations most sensitive data elements.  People are naturally curious about financial information and administrators must configure fundraising databases to keep information safe from prying eyes.

Microsoft Access offers the more advanced users a way around some of the most time consuming and repetitive actions by using conditional functions. Conditional functions help you reduce how much data you have to store by telling Access how to calculate or compare information in specific fields so that you don’t’ have to create an entirely new row or column of information.

For example, if you are a teacher and you are storing students’ grades in an online database, you might feel compelled to add a row or column to show whether a grade is passing or failing. This would be redundant since the numeric value of the grade already shows this. However, there will be times when you want to run reports on the number of people who passed or failed tests over the course of a semester, so what you want is not a report with numbers that you will have to translate into pass or fail.

Before getting into what you can and cannot do with conditional functions, you need to understand what they are. Once you have a basic understanding of them, we’ll take a look at two of the most common conditional functions: IIf and Switch.

What Is a Conditional Function?

Essentially all databases have the option to use conditional functions. Conditional functions can be as simple as a Boolean value where a person enters data on a form and is only able to check one out of two boxes. The conditional Boolean will automatically

grey out the other value, so that the answer is only Yes or No to the question on the form. It can also make the field required so that the form cannot be submitted until one of the two conditions listed is checked. This is commonly used for official forms to ensure that all of the required information is supplied before a form can be saved. For more advanced users, you can add a conditional function to send an email to certain employees every time a new account is opened (a particular database form is used to create a new account) and the amount of the account exceeds a certain amount, such as $499. To add a conditional function to Access, you will need to have a basic understanding of SQL to create the expressions.

When Would You Use a Conditional Function?

Conditional functions give you the controls to simplify working in Access. There are many things that can be automated, keeping everyone on the same page without requiring a member of staff to constantly monitor the database. Conditional functions give you the power to make work a lot easier if you have the time to learn how to work with them.

The best way to think of conditional functions is as a method of automating a process. If there is an action that is repetitive, conditional functions can take care of it so that you can focus on more the more important elements of the process. Conditional functions can keep users from entering conflicting information. There are many different uses for conditional functions, so the best way to learn how to use conditional functions to is learn about each of them. Once you understand what they do, you can determine how best to apply them to different situations.

There are a number of different conditional functions, but the two most common are IIf and Switch conditional functions.

When and How to Use the IIF Conditional Function

IIF stands for the Immediate If function and it can be combined with other conditional functions, like the Boolean to accomplish an immediate reaction from Access if a certain action is taken by the user. The following is a brief example of what the IIF condition needs.

IIf (Checkbox Condition

      ExecuteIfConditionMet

      ExecuteIfConditionNotMet) As Boolean

When the user’s action meets the first criteria, Access will execute the first condition; otherwise the second condition is triggered. You can set the condition to work with several different values, but IIf only deals with one condition at a time. There are more complex and elaborate expressions that execute actions based on multiple conditions. For now, this should be enough code to work with setting the conditions up is not something that can be done with a couple of clicks of the button.

Since most people will be working with forms when setting up conditions (after all it helps to streamline the data entry process), the following instructions demonstrate how to create an IIf condition on a form. IIf conditions can be set for reports by using the same instructions.

1. Open a form.
2. Double click on the field where you want to add the IIf condition. The Property Sheet menu will appear.
3. Click on the Event tab of the Property Sheet menu.
4. Select the condition when you want to apply the IIf condition. For example, After Update will trigger the action after the user updates the field. There are over a dozen possible conditions that can be applied. You will have time to experiment with them later once you understand how to apply the condition.
5. Click on the ellipses button (to the right of the drop down menu). The Chooser Builder will open.
6. Click on Expression Builder in the Chooser Builder window.
7. Under the Expression Elements menu, click on Functions > Built-In Functions.
8. Under Expression Values, scroll down and click on IIf. The following text will appear in the text box: “IIf (<<expression>>,<<truepart>>,falsepart>>)” This is the text that you will update to meet the IIf condition.
9. Click OK when you are done updating the text.

Take some time to try different scenarios.

When and How to Use the Switch Conditional Function

The switch condition is one that can work with multiple conditions, making it more diverse than the IIf condition, but also much more complex. The following is an example of switch condition text.

Switch (Expression1, Execute if the expression is true,

      Expression2, Execute if the expression is true,

      Expression3, Execute if the expression is true,

      True, Execute if no expressions are true) As Value

You can add as many expressions as you need to add, which can make automation considerably easier for users. The last may not be needed, but it will guarantee that Access does not get hung up if none of the expressions are true.

Again, we will focus on forms, although the same steps apply for reports.

1. Open a form.
2. Double click on the field where you want to add the IIf condition. The Property Sheet menu will appear.
3. Click on the Event tab of the Property Sheet menu.
4. Select the condition when you want to apply the IIf condition. For example, After Update will trigger the action after the user updates the field. There are over a dozen possible conditions that can be applied. You will have time to experiment with them later once you understand how to apply the condition.
5. Click on the ellipses button (to the right of the drop down menu). The Chooser Builder will open.
6. Click on Expression Builder in the Chooser Builder window.
7. Under the Expression Elements menu, click on Functions > Built-In Functions.
8. Under Expression Values, scroll down and click on Switch. The following text will appear in the text box: “Switch(«expr-|0», «value-|0»)” This is the text that you will update to meet the switch condition.
9. Click OK when you are done updating the text.

Take some time to try the different expressions, starting with two, and then adding more as you get comfortable with the switch condition.

As you become more proficient in Microsoft Access, you begin to realize that there is a lot of duplicate effort and repetitive tasks that are well suited for automation. In Microsoft products, automation is done through the creation of macros in a specific type code language called Visual Basic for Applications, or VBA.

Macros are typically buttons that users can press to execute repetitive tasks or

procedures, such as formatting or filling out specific forms for a new customer. End users can simply click a button and Microsoft will execute the VBA code you have programmed to begin the process. The bigger a database becomes and the more reliant a business is on the data it contains, the more you need to automate a process to ensure consistency and accuracy. Macros give you the ability to simplify your processes. From updating field types to indexing tables, there are a lot of actions that can be automated so that your maintenance time is significantly reduced. You can even write macros that will run a regular search and removal of duplicate records.

VBA is a very basic language that can be learned with a mix of recording actions and working in the code. However, like any language, you should continue to work in a sandbox or an experimental area until you are confident that your code works.

Recording a Macro versus Writing a Macro

The best way to start learning VBA is through experience.

Before you even open a book or visit a website to learn about macros, you can get an idea of how the code works by recording a macro. The difference between recording a macro and writing a macro from scratch is that recording a macro will provide all of the possibilities in the code. For example, if you were to format the font of a form, the recorded macro would show you every possible option for the font properties. If you were to write the same macro from scratch, you would probably only include the single element that you want to change, such as changing the font size to 12 or the type to italics.

Benefits of Recording Macros

When you start writing macros, you should try recording a few macros. If you aren’t sure where to start, think about repetitive actions that you do to a report every time you generate that report. Is there something that you want to change in the formatting? Would you like to create a similar report with a few modifications for meetings? Reports will be the easiest starting point because you probably already have to manipulate the reports after they are generated.

For at least the first few months, it is best to stick to recording macros to start them. There is a lot of generic code that goes into VBA, and recording a macro will take care of all of the stuff that has to be there, saving you a lot of time typing it out. Even after you become adept with VBA, you will likely find that you begin with the recording.

Benefits of VBA Coding

There are two distinct advantages to writing code from scratch (or the majority of it after the initial recording): less unnecessary code and comments.

When you are first writing macros, recording your macros is going to be great because it will give you all the code you need. The problem is that you are going to have to comb through a lot of code you don’t need to find the code that you need to change to get what you want. Recording a macro tends to be flawed and limited. For example, if you run a find for the word “Key” during a recording, the macro will only search for the word key. If you code the macro to look for the next word on the list, instead of specifying the word for the find, you will be able to use that macro longer and for more than one project.

Comments are another very important and too often forgotten part of coding in VBA. Because VBA is simpler than many other languages, it is one that many people learn, including people who don’t know how to create comments or see the value in them. Even if you record a macro, you should go back in and add comments so that the macro can be updated later if the situation changes. One of the most frustrating parts of coding is going into spaghetti code and trying to figure out what each call does. It’s time consuming and painful. By adding a brief comment at the beginning of a new process in the code, you can save yourself and coworkers a lot of headaches later when you have to make changes.

Security When Dealing with Macros

Something to keep in mind with macros is that they are a potential security risk. If you receive a file that you open and it warns you that the database has macros, you need to contact the sender to make sure those macros are alright before you enable them. If you add macros to files that you will sending through email or another form of communication, make sure you tell the recipient. Typically, it is best to remove the macros before you send the files as the recipients likely will not need the macros.

Teaching is already challenging enough without adding something that is potentially troublesome and complicated, but databases can actually make the job easier. While there are a number of databases out there, usually it will be easier (and cheaper) to use the tools you already have. Of course you already have to deal with numerous types of databases for school, but those aren’t quite the same. By setting up a simple database in a program like Microsoft Access, you can simplify your daily activities.

Even if you don’t think of yourself as a tech savvy teacher, consider creating a basic database for yourself instead of trying to track information in Excel. If you are less inclined to use technology because it just isn’t your area of expertise, there are a few things that can help you better cope with the information you have to maintain for school. No matter what your skill level or proficiency, you should be aware of the rules and regulations regarding information privacy that dictate what information can be stored on what computers.

When the time comes, you can actually get a good look at how your class is performing through database reports, and you will find that forms can significantly simplify the experience. In fact, most administrative requirements will have forms to make it easier to deal with entering grades and attendance information. You can create a database for yourself,

or you can join together with some of your fellow teachers to streamline your lesson plans, share project ideas, and critique teaching methods.

Creating Lesson Plans

One of the most challenging aspects of teaching is coming up with lesson plans because you have to look at the plans from several different aspects. First you have to make sure that you are able to cover all of the different requirements for both state and federal guidelines (and frequently local requirements). Then you have to come up with several different ways to present information to cover the various ways that children learn. On top of that, you have to make it engaging enough that your students will learn the material that is presented. If you are the kind of teacher who wants to cover several different parts of the curriculum at a time, you also need to track what you have done and what still needs to be covered.

By using Access, you can create a database that identifies everything that needs to be learned over the course of the semester or year. You can record information on all of the lesson plans you have, ones you have found online, lesson plans that are used by your coworkers, and anywhere else that you find a lesson plan you may like to use or modify. Then you can compare what you need to teach with the existing plans to come up with one that hits the points and makes sense. If there is a current event that perfectly exemplifies a current lesson (particularly for science or social studies), you can work that into a lesson plan you have prepared.

You can also track how successful each lesson plan is with the students. If there was a particular problem with the plan, you can take notes and tweak it for next time. If the lesson plan utterly fails, you will know to create a new one before next year.

Tracking Attendance

Tracking students is relatively simple. Create a table that includes all of your students. You can create a form to track transfer students and students who move, making it easy to identify when a student joins or leaves your class. Another form can be used every day to record anyone who is absent that day (that will save you having to go through the table every day). Creating a table for attendance actually sets you up for the next important aspect too: grades. This also means that you need to make sure that you keep this table current.

Tracking and Reporting Grades

This one could be tricky because you have to be careful about putting this confidential information on a computer outside of the school’s network. For these types of databases, it is generally best to use a school computer to save information, or least get prior approval to save this information to a personal computer.

Access is actually ideal for storing this sort of information because it gives you numerous methods of tracking grades and updating information. Of course you will have to maintain the same information in the school database, but you may be able to load the information into the school system’s database. It is worth the time to determine whether you can do a straight conversion or load up a text file with the grades to keep you from having to double enter information.

In terms of actually managing the information, you can create forms that are easy to use and specific to your needs. If you create a single form for grades, you can also include a field for the grade type (such as participation, quiz, daily, test, or presentation), so that Access can include the right percentage for the grade. You can assign names to the forms, and then link it to the table used for attendance to go through all of your current students and add the grade for each of them. This will guarantee that you include everyone.

Generating Progress Reports

One of the best things about Access and grades is that you can examine how each individual is doing in a class, as well as getting an overview of how each class is doing. If it looks as though the students in one class are having trouble with a particular area, you can adjust your lesson plan to cover that area before the next quiz or test.

The reporting area is probably the most useful aspect of Access for teachers because you have to maintain so many different metrics. You can create reports that run metrics from any angle you want, something that can be very difficult to do in a school maintained database. It is considerably easier to work with something that you created because you already know exactly what is recorded and how you have recorded it.

Project Tracking

Projects are one of the things students tend to like the least about school. You can use Access to track how well projects do, the average grade for a project, the kinds of problems students encounter, and information on how to handle the project in the future. You can create a separate form for projects that ties back to the gradebook table (so that the grades still show), but that you can pull other details for future reference.

Meetings, Conferences, and Certification

One of the most troubling parts of teaching is keeping up with all of the different aspects of the job that aren’t directly working with students, like parent conferences, school mandated conferences, various meetings, and certification. You can create an area in your Access database just to track this information. That way you can pull reports that tell you what you need to plan for each month.