Excel and Access

This book will show you the possibilities you can use in the Excel environment, and above all, it will introduce you to the database environment - Access. Drop your fears of the mysterious word database and learn to approach information processing professionally, efficiently and conveniently. The explanation in the book applies primarily to the Excel 2003 and Access 2003 versions. However, it is structured in such a way that owners of lower versions will not lose their orientation. The differences are mainly in the user interface, which becomes more intuitive with the higher version. The theoretical explanation is continuously accompanied by a sample example related to the topic of personnel and payroll, which can be found on the website of Grada Publishing (www.grada.cz). The first part of the book is designed as a small reflection and first contact with both environments that will be discussed. The second part will show you how to use Microsoft Excel spreadsheet environment in your daily office practice. First, you will get into the application environment, then you will start to create a file called Payroll step by step, while getting to know the spreadsheet environment more deeply. Sequentially build an ordered sheet with a table containing personnel and payroll data of employees in one department of the company. These data will be supplemented by calculations that will help you design your pay increase. Expressing data using charts is inherent to Excel. We will not neglect this issue either and will discuss the data display in this way in more detail. We'll also touch on an area that facilitates or automates the spreadsheet environment and thus saves you time; we will create a concrete example of the so-called. macro. At the end of the second part, we will explain what a list is and then convert it from a spreadsheet data sheet to a database environment. The third part of the book teaches you how to work easily in the Access database environment. First, you will become familiar with the environment and philosophy of the application. At the same time, you can sit down at your computer and start to create a file called Administration, which can be your daily helper in the future. You can use the resources that the Access database environment offers to help you manage and organize your data. At the heart of it all are the spreadsheets that form the "treasure trove" of your data. You will learn how to insert, update, format, sort or filter data in tables and later join tables in queries. Queries allow you to sort or select data, and you will actively work with the data through simple expressions. At the end you will try the elegance of working with data when summarizing them in queries (sum of values, number of records, minimum value from the data set...). Summarizing all these activities, it can be concluded that you can effectively use the information from the tables through queries. Learn how you can elegantly present data in Access. We will show how effective the cooperation between Access and Excel can be. You will also design a simple main form that appears on the screen whenever you open the database. This form creates a basic area for you to place several buttons that can be used to quickly and efficiently retrieve information stored in tables. At the touch of a button, you can see the results of different views of your data very quickly. One button can display the current number of employees by centre, the other something else - for example, a list of employees who have been working in the company for 30 days or more. The other generates a list of workers sorted according to your wishes, which you have revealed to Access by defining the corresponding query, which is hidden in the core of the button. Each of these buttons acts as a representative of an action - a command you have personally prepared. You will design a simple, basic application that is easy and intuitive to use, and you will learn to use the Access database to make it your assistant, so that you can work with it pleasantly and, above all, efficiently.