- database solutions and downloads for microsoft access

Click here to view Access 2007 Pure SQL

Access 2007 Pure SQL

Real, power-packed solutions for business users, developers and the rest of us

Access 2007 Pure SQL - Click here to buy the book.

This book provides the database professional and power user with more than 300 working solutions for daily business tasks. The goal has been to reduce needless writing and concentrate on the daily needs of database usage and development. An efficient database professional does not need a book to tell him or her how to execute a query or how many types of queries Access 2007 supports; the answers are a click away in the help file or online.

What power users and developers need is thought-out solutions to show them the way to achieve their difficult tasks without having to look around for hours, days, or sometimes weeks. In addition, they need a book to show them when something is possible, when it is not, how many ways exist to achieve a task, and which one is the most efficient. Furthermore, the table of contents is not arranged by topic (tables, queries, reports, etc) but by solution. The content of the book should be practical and the layout should help the professional find what he or she needs in seconds.

Learn how to use your databases for real business tasks: Pindar has worked on hundreds of business databases and operational systems for the last 18 years. In this book, he provides actual scenarios and code you can use in your daily business situations. Actually, you will get many ideas of how to employ Access 2007 to get data in ways you were not aware it was possible. Some examples, especially in the beginning of each chapter are quite simple so that readers with less Access experience can follow and learn but they are definitely not simplistic.

Leave superfluous theory on the side and focus on the essence of your operations: You might be taught a thousand pieces of theory and politically correct techniques on databases. In the end, what you will need is a way to accomplish your task. This book will show you exactly the concepts you should learn and expand on them in detail. Theory is present but only to support a practical technique; not for the sake of it.

Concentrate on holistic solutions and not clustered technical skills: This book leaves behind the classical format of texts. Instead of providing multiple and isolated concepts, it combines the necessary techniques to arrive to a real world solution. For example, instead of just showing what a date function is, it demonstrates how it can be used in combination with clauses and other functions to obtain order processing cycle times or order fulfillment goals for your corporation. At the end of the day, when you read a book, you need to be able to use your knowledge to achieve a task.

The business table of contents: You will find a novelty in this book which is its business table of contents. There are two tables of contents in this book. There is the classical one to find what you need on database concepts. However, there is also a business table of contents you can consult to find the business solution you need. For example, how to conditionally update product prices from multiple suppliers and by various percentages.

Use this book as a handy reference: Finally, this book has been written with the idea of using it as a reference. You might need to flip its pages to check something simple like the correct use of quotes in criteria expressions or concatenated fields. Or you might need to check something more elaborate like how to use a subquery to manipulate data in one table based on the values of another table.

Sample Chapter 25: Working With Strings

It is now time to enter the amazing world of string functions and text manipulation. String functions produce output that is difficult or impossible to generate without them. They should be in the toolbox of every database user since lack of such knowledge has direct effects on database and table design. In other words, extra fields might be inserted in tables when not needed. In this chapter, we will explore more than 25 examples of how to use string functions.

1. Use the Ucase() function to capitalize field values

Capitalize the first and last names of customers using ucase()


The general syntax of the ucase() function is shown below. It takes just one argument, and it will capitalize the contents of the field on which it is applied. Ucase() will capitalize all of the lowercase characters in the field, leaving the existing capital characters unchanged.

ucase(field name)

In this example, notice the [Last] and [First] field titles in brackets because they are reserved words in Access 2007.


SELECT UCase(lastname) AS [Last], UCase(firstname) AS [First], city, state, zip
FROM customers

For more please:

Download the Sample Chapter - Access 2007 Pure SQL. Chapter 25, Working With Strings

You can also purchase the book - Access 2007 Pure SQL

About the Author

Pindar's exciting relationship with databases started with DBase III back in 1991, continuing with all versions of Access since early 1993, and working with MS SQL Server, MySQL, Oracle, and IBM DB2 for a number of years. From then on, he is still in love with all of them. After almost twenty years, he still works with data, information processing, integration, and dissemination.

Pindar is currently a Clinical Assistant Professor of Information Systems at the business school of Rensselaer Polytechnic Institute in Troy, New York where he is teaching databases for the last ten years. Pindar also completed and collaborated on a myriad of database projects for organizations or in collaborative efforts between the University and various corporations.

Pindar’s interests in information science, transactional systems, and analytics focus on creating more efficient and flexible organizations. The idea is to accomplish more with fewer resources and in less time leaving a small footprint on the environment. Pindar’s education includes a BS from the American College in Thessaloniki Greece, an MS, MBA, and a PhD in the United States. He received national and international distinctions for his work in the field and faculty awards for his teaching methods.

Nevertheless, the majority of the author’s experience came from participating in a multitude of industry projects. There, everything has to work efficiently, reliably and above all be acceptable by the people of the corporation. Theoretical knowledge, though useful, takes a second place in these cases. A solid application and strong promotion within the organization are the primary success factors. This is the main reason for which theory takes a secondary part in this book and when it does it is only to support a business task.

No matter what the restrictions of time, Pindar will find the time to grow his tomatoes, eggplants, peppers, squash, onions, and in general anything he can find for his vegetable garden.