Catálogo de publicaciones - libros

Despite all that this chapter has covered, it still isn’t a full list of controls; we’ll cover some more in Chapter 18. As we said earlier, controls are a very important part of Access!

In this chapter we’ve looked at putting sophisticated controls onto your forms. Choosing the right one for the job should be looked at both from the point of view of the data it lets into the table and from that of the user. The ease with which controls can be added, experimented with and removed lets you try several different approaches before deciding upon the one that’s most suitable.

An Access report is an immensely flexible method of preparing data for printing, adding value to the raw data by offering many ways of presenting it and illustrating it with charts. The same data can tell different stories depending on how a report is structured, so matching the report to the intended audience or to the point you are hoping to carry is worthwhile. This is often the hardest aspect of creating reports.

The bigger the database, the more important it is to design it from the outset with a multi-table structure. Databases have a habit of growing and becoming more complex. Often what happens is that users find a database application easy to use and want it to do more and to store details of other aspects of the business. As the complexity increases, it becomes more likely that you’ll encounter the four problems outlined at the start of this chapter. The time and effort spent in redesigning the contents of a single table into a series of tables will almost invariably be less than that taken to work around the problems.

If the Table Analyzer doesn’t produce the results you hope for, designing from scratch is the next step. Creating a database from the ground up also gives you a much greater insight into the way a multi-table database works.

Once you’ve identified the various objects about which data is stored and constructed tables to contain those objects, the next stage is to determine how the tables should act together to form the whole database. No prize is offered for guessing that this happens in Chapter 15.

The bigger the database, the more important it is to design it from the outset with a multi-table structure. Databases have a habit of growing and becoming more complex. Often what happens is that users find a database application easy to use and want it to do more and to store details of other aspects of the business. As the complexity increases, it becomes more likely that you’ll encounter the four problems outlined at the start of this chapter. The time and effort spent in redesigning the contents of a single table into a series of tables will almost invariably be less than that taken to work around the problems.

If the Table Analyzer doesn’t produce the results you hope for, designing from scratch is the next step. Creating a database from the ground up also gives you a much greater insight into the way a multi-table database works.

Once you’ve identified the various objects about which data is stored and constructed tables to contain those objects, the next stage is to determine how the tables should act together to form the whole database. No prize is offered for guessing that this happens in Chapter 15.

In Chapter 14 we discussed the reasons why a multi-table database can be a better tool than a single table database and illustrated how a table can be subdivided. In this chapter you’ve learned how to join the new tables so that they can be used together by Access to provide the same level of functionality as the old single table. The joins are in place for inspection in the microbrewery database in the chap15end.mdb file.

Next we’ll construct a complete example to bring together these elements into a working database.

The basic structure of the book database is now complete; the tables have been built and the joins are in place between them. All it lacks is some data. Being the amiable types we are, you’ll find a file called chap16end.mdb containing a database of an identical structure to the one described in this chapter and, what’s more, it contains data. When you look at the table view of this data, you’ll see that expand buttons appear alongside records in the Category, Publisher and Author tables, placed there automatically by Access.

Queries in multi-table databases are even more flexible than they are in single table databases because you can ‘mix and match’ data from the various tables to reach precisely the information you want. All the different types of queries covered in Chapter 9 (range, update, crosstab and so on) are, of course, still available to you.

A query can either take all the records that have matching records in the tables to which they are joined or, using outer joins, you can insist that all records from a specific table are included in the answer table. Queries can be based on one table, all tables or a subset of tables and, by recycling an existing query as the basis for another, you can work even more efficiently.

The queries described above are to be found in the chap17end.mdb file.

With a grasp of the extensive armory of controls for Access forms, you can design and build forms for any purpose. Forms give users a helpful interface for entering data and for displaying it, either all records or a subset determined by an underlying query.

Try to think about a form from the users’ point of view; keep things simple but don’t hesitate to add helpful labels and captions. Use lines and rectangles to draw the eye towards groups of controls. Use the most appropriate control: a check box for a filling in a Yes/No field is quicker than selecting from a list. Use tab controls to give a logical flow to progress through the data entry process. Use list and combo boxes to control data entry and improve the integrity of your data.

The examples covered in this chapter all appear in chap18end.mdb.

Having now completed a third pass through the four main elements of a database, you should have a clear picture of how Access can help you control the data that’s stored in the database and also help you extract it in ways you find helpful. In the next chapter, we’ll look at the database as a complete application.

Using these wizard-built buttons and the technique of opening and closing forms of carefully chosen dimensions, it’s possible to put together an attractive and useable front-end to your database, tailored to the needs of your users. The user interface described above, so far as it goes and with all its imperfections, is in the chap20end.mdb file.