Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources]

Practical Examples: Applying These Techniques in Your Application

The following are several practical applications of the advanced techniques learned in this chapter.

NOTE

Archive 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 11.44). The second query, called qdelRemoveFromPayments, is a Delete query that deletes all the data archived from the tblPayments table (see Figure 11.45). The archiving is run from a form called frmArchivePayments, where the date range can be specified by the user at runtime (see Figure 11.46).

Show 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 11.47.

Create State Table

Because you'll regularly be looking up the states and provinces, you want 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 11.48. The query uses the tblClients table to come up with all the unique values for the StateProvince field. The query is a Make Table query that takes the unique list of values and outputs it to a tblStateProvince table.