Managing Your Customer Database

By. Syed Ali

Managing your customer relationships is vital to the success of your business; you neglect your customers at your peril. With a small customer base and a simple sales process in a relatively static market a business may operate an effective Customer Relationship Management (CRM) solution based on index cards or a simple Microsoft Excel spreadsheet to keep client data. In such an instance, a small business owner may be able to keep client information such as address and telephone numbers in their heads and know intuitively the value of a particular client to their business.

Once we move away from such a simple scenario, matters become far more complicated and require a more effective tool to control and utilize customer information and Microsoft Dynamics CRM recognizes this. The reality of today's business environment is that we operate in fast moving dynamic markets where customer needs and demands change frequently. Businesses tend to serve a broader clientele while the potential market for products and services has grown geographically as the internet allows businesses to reach out to potential customers and prospects seek a supplier for their needs from greater distances than before.

Microsoft Dynamics CRM software is designed to allow any business to manage customer relationships in today's modern world which requires business managers to be far more organized than before if they are to obtain the most effective results from sales and marketing activities. Arranging contact information into contacts, identifying viable prospects from that database and managing marketing and sales campaigns tailored to those prospects while retaining control over the process is not possible using simple paper based or spreadsheet methods without a great deal of expensive employee time.

Generating new business has always been the most difficult part of the sales cycle, so organizing contact information in a formalized manner allows businesses to target their business generation activities to where they are most likely to maximize sales revenue. Turning a contact into a prospect and from there to a customer is also the most expensive part of the sales cycle. Once a sale has been generated, that customer is far more likely to buy from you in future. Customer relationship management software empowers a business to effectively manage and control the sales cycle as a client base is established and repeat sales are made. Microsoft Dynamics Suite recognizes that customers become a very valuable asset for any business and managing the after sales relationship becomes a major factor in business success.

Microsoft Dynamics Suite provides an effective CRM solution that allows any business to capture and maintain customer data beyond the basic address and telephone information; examples of the information a Microsoft Dynamics CRM system will manage include a client's sales history, particular customer requirements, and when it is to be expected that a customer will order in the future.

The benefits of implementing an effective Microsoft Dynamics Suite CRM system can be readily seen when a business starts planning and forecasting for future sales and business growth as capturing and maintaining customer information is not a static process.

With a Microsoft Dynamics CRM solution your business will be able to manipulate customer information so your business can see what is actually happening in the sales cycle. Taking a step back from the sales process and looking at the "Big Picture" may allow you to assess the overall health of your business, but it does not necessarily explain how you are getting the results. While diving into the detail may mean you can see what is happening with a particular customer, it may also mean you are not going to see the wood for the trees.

Microsoft CRM solutions provide you with the ability to assess at a glance the overall business picture and how that is being produced which becomes an invaluable management tool and is a key benefit of a Microsoft Dynamics CRM solution.

How to Create A Microsoft Excel Database

By. Chris Le Roy

The term Microsoft Excel Database has never really sat very well with me since my primary role in the IT world has been in developing database and what is used by the spreadsheeting fraternity is what I consider an over glorified list. However, for the sake of peace, I want to outline some of the principles you must follow in building what is known as the Microsoft Excel Database.

Let us review ...

A database in essence is what is considered a collection of information that is related in some manner. For example if you were running a company and selling a product you may have a database that simply lists all of the sales you have made over a period of time. Storing this data in a database would make sense as the company information and the selling of a product is related and as such would be appropriate for the database.

There are many different types of databases available such as Microsoft Access Databases, Oracle Databases, MySQL databases and so on but Microsoft Excel also has a form of a database known as a database list. The form of the list is virtually the same as the other databases as the data is under column headings in rows, but after that common point, the Excel database goes in its own direction. See, to look for specific data within a Microsoft Excel Database or Excel List we do not use the common database language of SQL, we actually use specially written functions. These functions are custom written by you and are known as criteria.

So how do we create an excel database...

Well first off, there is one rule we must always follow and that is one excel database per worksheet. Anymore and you just get yourself into lots of trouble. In fact if you need to have multiple excel databases within your workbook simply put each excel database onto a separate worksheet.

The next thing you must follow is that your database lists first row must contain the heading of the list. That is the first row contains your field names. Plus each of the field names must be 100% unique. You cannot have two field names with the same name or again you will have a list that will not work.

The next issue you need to be concerned with is identifying the field names. Excel databases have a simple rule, the field names or column names must be unique. Now the way you identify them is easy, all you have to do is to ensure the field names are many different data types, format, pattern etc to the rest of the database in your list. Generally what I do is to format my field names in bold to satisfy this requirement.

One of the most important rules you must remember when you create an excel database is that around the row and columns of the fields and data there must be a blank row and column. What this means is that you can still have a heading at the top of the fields, but there must be a blank row between the heading and the fields as well as along the last column as well. The blank row rule also applies to the bottom of the list as well.

When you are entering data into your list, every cell in every record must contain some value even if it is simply blank (a blank value is still considered a value) and each record must contain the same number of fields. If there is no specific data for a field you simply leave it blank and move to the next field.

Ensure that when you are entering data into a field that you don't have spaces before the text or at the end of the text in the field. If you do have spaces, then what will happen is that sorting and search for data in the list will be compromised and you will get unexpected results.

Upper case and low case characters in the field do not affect the searches or sort orders unless you specifically tell the Microsoft Excel application it is an issue. You can also use formulas in a cell if required. Formulas can refer to cells within the Excel Database List or outside of the Excel Database.

Note also that you can edit and format the cells just like any other spreadsheet however the issue you must consider as a priority is that the field names must have a different format to the rest of the data in the database list. It is highly recommended that there be no other formatting in the list except for the field headings. This ensures that there are no miscalculations by the application as to what is a field heading in the excel database and what is not.

Now that you have setup your list in this way following these rules, you are now ready to interrogate the list by applying criteria. The easiest way that you can do this is by using the Form dialog box. To get into the Excel Database form you simply choose the Data menu and then choose Form from the drop down menu.

From the Excel Database Form you can simply choose the Criteria button, type the criteria you have for your data and choose the Find Next button and it will take you to the first record that satisfies the criteria you are searching. Excel Databases are particularly useful for summary data that is where the volume of records you have in your database doesn't exceed 65,536 rows.

If you follow these rules to create an excel database you will find that the functions associated with the excel database list will work in an effective and efficient manner.

New Database Synchronization Tool Helps Sql Developers and Dbas Keep Databases in Sync

By. Perpetuum Software Team

New version of Database Restyle - Library includes a software component and visual wizard. Component is integrated directly into your application, so that you can execute database synchronization from your application. This feature provides undoubtful competitive advantage as you don’t need to install additional software on the client side and incur additional costs.

Visual tool provides the ability to save database snapshot, compare two databases and synchronize them. It works in the wizard mode: convenient and intuitive interface allows execution of mentioned tasks with a few mouse clicks. You just need to specify connections to databases, compare them, then the product will compose compare list containing all objects that should be modified, added or removed and generate script for synchronization; you just need to click buttons. The wizard provides clear visual display of schema differences so you understand them at a glance. It's all about saving time, the Database Restyle wizard eliminates the need for you to figure out how things work in the product – you just follow the prompts from one step to the other. No coding or manual comparing or writing scripts!

Thus, all scripts generated for database synchronization will be typified and address corporate standards. Database developers and DBAs won’t waste their time to write scripts at first, and then to find out that they are not acceptable for some reason. Database Restyle – Library is a real lifesaver, allowing developers to focus on optimizing the production database instead of spending all the time making sure it is up-to-date.

Automatic generation of scripts and synchronization of databases doesn’t mean you are unable to control the process. Developers and DBAs can control any stage of database synchronization and force virtually any aspect of the work. Using Database Restyle component they can re-define procedures, add extra entities, execute additional scripts, skip some objects during synchronization, etc. Using the Wizard they can select objects to be synchronized and review generated scripts.

Both visual and non-visual constituents of Database Restyle -Library are easy-to-use: component uses only a few methods to get and synchronize database schemas, and you don’t even need to know how the product works when you use the wizard.

Using the product, you will be able to execute database synchronization in two ways: directly from your application and via a stand-alone tool. Get both features in a single product!

Database Restyle – Library used in the project will significantly increase its ROI, as product will pay for itself during 2-4 billing hours of your developers. Quite a good deal, isn’t it?

Fix and Recover Corrupted Dbf Databases Automatically

By. Afonin Oleg

Heavy-load workstations and busy environments are home to most database applications. A system crash or a power failure can easily damage an open database, causing loss of data and corrupted database structures. Once the problem is fixed and the workstation rebooted, the corrupted database will fail to open.

What would you do when pressured to get the database up and running right away? Reach for a backup just to discover it's a week old, or attempt to recover the database, risking causing irreparable damage to what's still left?

Don't panic! If your database uses DBF files, your chances to get back all of the original data are extremely high if you use the right tool. Even if you don't know anything about the internal structure of DBF files, you still can get it fixed easily and automatically.

DBF Repair tool by www.dbf2002.com/dbf-recovery/ recovers damaged and corrupted DBF files completely automatically. You can be a professional database manager or a complete novice - DBF Recovery will repair the broken database regardless of your experience. With DBF Recovery, you simply select the corrupted file, and the program does the rest completely automatically.

Why paying for DBF Recovery instead of just using any of the numerous recovery tools that claim to repair your database in a matter of minutes? While DBF Recovery may not be as fast and as cheap as its freeware competitors, it does a much better job in correctly repairing the corrupted database structures and data records. Unlike many free database repair tools, DBF Recovery does not limit its operation to just the headers. The newly developed database recovery engine automatically detects the exact file format and database that created a DBF file, and thoroughly analyzes and repairs the structure of the database as well as all data records, resulting in the most comprehensive and quality recovery.

Have more corruption in supporting files? DBF Recovery fixes memo-files such as DBT and FPT in addition to DBF.

While new database administrators will certainly appreciate the unprecedented level of automation and the ease of use provided by DBF Recovery, expert users will enjoy the advanced features that provide even more automation to the database recovery process. Comprehensive command line parameters and batch mode support allow using DBF Recovery to process multiple databases automatically, or to fix certain database files on Windows startup.

Novice database users and advanced system administrators will appreciate the time savings provided by DBF Recovery. Supporting all DBF databases, including Dbase III and IV, FoxPro and Visual FoxPro, DBF Recovery is the perfect choice for a concerned database administrator. Download a free evaluation version of DBF Repair and rescue your DBF databases after corruption!

Introductory Transact-SQL (part 10 - last)

By Addison Wesley

Summary

This concludes Introductory Transact-SQL. You should now be able to create a database, build tables, and populate those tables with data. You should also be familiar with the basic syntax required for querying tables and for making rudimentary changes to them. Be sure you have a good grasp of basic Transact-SQL before proceeding with the rest of the book.
About the Author

Ken Henderson, a nationally recognized consultant and leading DBMS practitioner, consults on high-end client/server projects for such customers as the U.S. Air Force, the U.S. Navy, H&R Block, Travelers Insurance, J.P. Morgan, the CIA, Owens-Corning, and CNA Insurance. He is the author of five previous books on client/server and DBMS development, a frequent magazine contributor to such publications as Software Development Magazine and DBMS Magazine, and a speaker at technical conferences.

Introductory Transact-SQL (part 9)

By Addison Wesley

Column Aliases

You might have noticed that some of the earlier queries in this chapter use logical column names for aggregate functions such as COUNT() and SUM(). Labels such as these are known as column aliases and make the query and its result set more readable. As with joins, Transact-SQL provides two separate syntaxes for establishing column aliases: legacy or classical and ANSI standard. In the classical syntax, the column alias immediately precedes the column and the two are separated with an equal sign, like so:

SELECT TodaysDate=GETDATE()

ANSI syntax, by contrast, places a column alias immediately to the right of its corresponding column and optionally separates the two with the AS keyword, like so:

SELECT GETDATE() AS TodaysDate

or

SELECT GETDATE() TodaysDate

Unlike joins, the column alias syntax you choose won't affect query result sets. This is largely a matter of preference, though it's always advisable to use the ANSI syntax when you can if for no other reason than compatibility with other products.

You can use column aliases for any item in a result set, not just aggregate functions. For example, the following example substitutes the column alias LName for the LastName column in the result set:

SELECT customers.LastName AS LName, COUNT(*) AS NumberWithName
FROM customers
GROUP BY customers.LastName

Note, however, that you cannot use column aliases in other parts of the query except in the ORDER BY clause. In the WHERE, GROUP BY, and HAVING clauses, you must use the actual column name or value. In addition to supporting column aliases, ORDER BY supports a variation on this in which you can specify a sort column by its ordinal position in the SELECT list, like so:

SELECT FirstName, LastName
FROM customers
ORDER BY 2

This syntax has been deprecated and is less clear than simply using a column name or alias.
Table Aliases

Similar to column aliases, you can use table aliases to avoid having to refer to a table's full name. You specify table aliases in the FROM clause of queries. Place the alias to the right of the actual table name (optionally separated with the AS keyword), as illustrated here:

SELECT c.LastName, COUNT(*) AS NumberWithName
FROM customers AS c
GROUP BY c.LastName

Notice that the alias can be used in the field list of the SELECT list before it is even syntactically defined. This is possible because a query's references to database objects are resolved before the query is executed.
Managing Transactions

Transaction management is really outside the scope of introductory T-SQL. Nevertheless, transactions are at the heart of database applications development and a basic understanding of them is key to writing good SQL (see Chapter 14, "Transactions," for in-depth coverage of transactions).

The term transaction refers to a group of changes to a database. Transactions provide for change atomicity—which means that either all the changes within the group occur or none of them do. SQL Server applications use transactions to ensure data integrity and to avoid leaving the database in an interim state if an operation fails.

The COMMIT command writes a transaction permanently to disk (technically speaking, if nested transactions are present, this is true only of the outermost COMMIT, but that's an advanced topic). Think of it as a database save command. ROLLBACK, by contrast, throws away the changes a transaction would have made to the database; it functions like a database undo command. Both of these commands affect only the changes made since the last COMMIT; you cannot roll back changes that have already been committed.

Unless the IMPLICIT_TRANSACTIONS session variable has been enabled, you must explicitly start a transaction in order to commit or roll it back. Transactions can be nested, and you can check the current nesting level by querying the @@TRANCOUNT automatic variable, like so:

SELECT @@TRANCOUNT AS TranNestingLevel

Here's an example of some Transact-SQL code that uses transactions to undo changes to the database:

BEGIN TRAN
DELETE customers
GO
ROLLBACK
SELECT * FROM customers

CustomerNumber LastName FirstName StreetAddress City State Zip
-------------- -------- --------- --------------- ----- ----- -----
1 Doe John 123 Joshua Tree Plano TX 75025
2 Doe Jane 123 Joshua Tree Plano TX 75025
3 Citizen John 57 Riverside Reo CA 90120

As you can see, ROLLBACK reverses the row removals carried out by the DELETE statement.
Caution
Be sure to match BEGIN TRAN with either COMMIT or ROLLBACK. Orphaned transactions can cause serious performance and management problems on the server.

Introductory Transact-SQL (part 8)

By Addison Wesley

Grouping Data

Since SQL is a set-oriented query language, statements that group or summarize data are its bread and butter. In conjunction with aggregate functions, they are the means by which the real work of SQL queries is performed. Developers familiar with DBMS products that lean more toward single-record handling find this peculiar because they are accustomed to working with data one row at a time. Generating summary information by looping through a table is a common technique in older database products—but not in SQL Server. A single SQL statement can perform tasks that used to require an entire COBOL program to complete. This magic is performed using SELECT's GROUP BY clause and Transact-SQL aggregate functions. Here's an example:

SELECT customers.CustomerNumber,
SUM(orders.Amount) AS TotalOrders
FROM customers JOIN orders
ON customers.CustomerNumber=orders.CustomerNumber
GROUP BY customers.CustomerNumber

This query returns a list of all customers and the total amount of each customer's orders.

How do you know which fields to include in the GROUP BY clause? You must include all the items in the SELECT statement's column list that are not aggregate functions or absolute values. Take the following SELECT statement:

-- Bad SQL - don't do this
SELECT customers.CustomerNumber, customers.LastName,
SUM(orders.Amount) AS TotalOrders
FROM customers JOIN orders
ON customers.CustomerNumber=orders.CustomerNumber
GROUP BY customers.CustomerNumber

This query won't execute because it's missing a column in the GROUP BY clause. Instead, it should read:

GROUP BY customers.CustomerNumber, customers.LastName

Note that the addition of the LastName column doesn't really affect the results since CustomerNumber is a unique key. That is, including LastName as a GROUP BY column won't cause any additional grouping levels to be produced since there is only one LastName for each CustomerNumber.
HAVING

The HAVING clause is used to limit the rows returned by a SELECT with GROUP BY. Its relationship to GROUP BY is similar to the relationship between the WHERE clause and the SELECT itself. Like the WHERE clause, it restricts the rows returned by a SELECT statement. Unlike WHERE, it operates on the rows in the result set rather than the rows in the query's tables. Here's the previous query modified to include a HAVING clause:

SELECT customers.CustomerNumber,
customers.LastName, SUM(orders.Amount)
AS TotalOrders
FROM customers JOIN orders
ON customers.CustomerNumber=orders.CustomerNumber
GROUP BY customers.CustomerNumber, customers.LastName
HAVING SUM(orders.Amount) > 700

CustomerNumber LastName TotalOrders
-------------- -------- -----------
3 Citizen 86753.09
1 Doe 802.35

There is often a better way of qualifying a query than by using a HAVING clause. In general, HAVING is less efficient than WHERE because it qualifies the result set after it's been organized into groups; WHERE does so beforehand. Here's an example that improperly uses the HAVING clause:

-- Bad SQL - don't do this
SELECT customers.LastName, COUNT(*) AS NumberWithName
FROM customers
GROUP BY customers.LastName
HAVING customers.LastName<>'Citizen'

Properly written, this query's filter criteria should be in its WHERE clause, like so:

SELECT customers.LastName, COUNT(*) AS NumberWithName
FROM customers
WHERE customers.LastName<> 'Citizen'
GROUP BY customers.LastName

In fact, SQL Server recognizes this type of HAVING misuse and translates HAVING into WHERE during query execution. Regardless of whether SQL Server catches errors like these, it's always better to write optimal code in the first place.
Ordering Data

The ORDER BY clause is used to order the rows returned by a query. It follows the WHERE and GROUP BY clauses (if they exist) and sorts the result set just prior to returning it. Here's an example:

SELECT LastName, State
FROM customers
ORDER BY State

Here's another example:

SELECT FirstName, LastName
FROM customers
ORDER BY LastName DESC

Note the use of the DESC keyword to sort the rows in descending order. If not directed otherwise, ORDER BY always sorts in ascending order.