Access 2007 Pure SQL: Real, Power-Packed Solutions for Business Users, Developers, and the Rest of UsThis book provides the database professional and power user with 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 tasksPindar 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 operationsYou 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 contentsYou 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 referenceFinally, 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. |
Contents
CREATE EDIT AND DELETE TABLES USING SQL Create a customer table using pure | 1 |
CREATE EDIT AND DELETE TABLES USING SQL CHAPTER 2 CREATE EDIT AND DELETE INDEXES USING | 18 |
8 | 52 |
CREATE EDIT AND DELETE INDEXES USING | 58 |
Change the size of the lastname field from 50 to 80 characters Delete the table customer1 from the database | 63 |
CHAPTER 11 THE IN OPERATOR | 88 |
THE DISTINCT PREDICATE | 96 |
14 | 112 |
CONDITIONAL DATA MANIPULATION | 192 |
UNION OPERATIONS | 208 |
198 | 221 |
DUPLICATE ORPHANED and UNRELATED RECORDS | 223 |
WORKING WITH NULLS | 237 |
The CStr function CHAPTER 25 WORKING WITH STRINGS 226 Use the Ucase function to capitalize field values | 244 |
TYPE CONVERSION | 247 |
WORKING WITH STRINGS | 255 |
Use a calculated field with an aggregate function and WHERE | 122 |
15 | 126 |
16 | 137 |
The GROUP BY clause on one column and one aggregated field | 138 |
AGGREGATE FUNCTIONS 138 The count function | 146 |
17 | 148 |
18 | 162 |
154 | 172 |
224 | 176 |
158 | 178 |
PARAMETER QUERIES | 184 |
Use the DateAdd function to add date intervals CHAPTER 27 UPDATE SQL STATEMENTS 258 What are cascade updates how to use them and wh... | 257 |
WORKING WITH DATES | 275 |
Use the datepart or format functions to extract the week from a date field283 | 283 |
255 | 289 |
DELETE STATEMENTS | 306 |
273 | 318 |
WORKING WITH SUBQUERIES | 332 |
291 | 344 |
| 350 | |
Common terms and phrases
Access aggregate functions Boston calculated fields chapter city field columns criteria crosstab queries current sale CustID customer orders Customer1 CustomerID customers Discussion customers table data type database delete DESC Result Field Name Filter Search Find customers Find orders foreign key GROUP BY clause GROUP BY lastname indLastName inequality predicate INNER JOIN keyword last name lastname field lastname firstname lastname Result multiple name field null values number of records offer a 25 one-to-many operator ORDER BY clause ORDER BY lastname order totals Orders table OrderTotal orphaned records primary key products table productunitprice Qry_Crosstab_Base Query1 lastname recordset referential integrity result set retrieve sale we offer sales volumes scenario SELECT lastname SELECT OrderID SELECT productname SELECT statement ShippedDate ShippingCost SKU field SQL code SQL statement subquery sum(unitprice*quantity suppliers switch tblCustomer1 tbls_CustomerOrders tbls_customersag TOP predicate total order amounts type conversion unique index unitsinstock update wildcard character Year([OrderDate York zip codes