Microsoft Access Programmers Rochester New York

Microsoft Access Programmer Information

Call MS Access Solutions at (323) 285-0939 For Complimentary Consultation

Archiving Payments

After a while, you might need to archive some of the data in the tblPayment table. Two queries archive the payment data. The first, called qappAppendToPaymentArchive, is an append query that sends all data in a specified date range to an archive table called tblPaymentsArchive (see Figure 12.43). The second query, called qdelRemoveFromPayments, is a delete query that deletes all the data archived from the tblPayments table (see Figure 12.44). The archiving is run from a form called frmArchivePayments, where the date range can be specified by the user at runtime (see Figure 12.45).

Showing All Payments

At times, you might want to combine data from both tables. To do this, you'll need to create a union query that joins tblPayments to tblPaymentsArchive. The query's design is shown in Figure 12.46.

Creating a State Table

Because you'll regularly be looking up the states and provinces, you need to build a unique list of all the states and provinces in which your clients are currently located. The query needed to do this is shown in Figure 12.47. The query uses the tblClients table to come up with all the unique values for the StateProvince field. Here, you use a make table query that takes the unique list of values and outputs it to a tblStateProvince table.

Summary

As you can see, Microsoft gives you a sophisticated query builder for constructing complex and powerful queries. Action queries let you modify table data without writing code; you can use these queries to add, edit, or delete table data. The Unique Values and Top Values properties of a query offer you flexibility in determining exactly what data is returned in your query result.

You can do many things to improve your queries' efficiency. A little attention to the details covered in this chapter can give you dramatic improvements in your application's performance.

Other special types of queries covered in this chapter include crosstab queries, outer joins, and self-joins. Whatever you can't do by using the graphical QBE grid, you can accomplish by typing the required SQL statement directly into the SQL View window. In this window, you can type Access SQL statements or use SQL Pass-Through to type SQL statements in the SQL dialect that's specific to your back-end database. After you harness the power of the SQL language, you can perform powerful tasks such as modifying the record source of a form or report at runtime.

This material orginally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Development. Reprinted here by author's permission.

When you need a Microsoft Access programmer for your Anaheim CA business, call MS Access Solutions at (323) 285-0939. We have over 25 years experience in Microsoft Access programmer solutions. We create Access database applications for all sectors, consisting of hospitals, government agencies, the U.S. military, universities, agriculture, workers services, and insurance provider. We can take care of the most advanced as well as complicated Access and also SQL Server database programming for your business as well as smaller projects, like fixing damaged Access database forms, MS Access reports, Access macros, and VBA code.

Access programmer cities we serve: Access Programmers Jamaica New York