Additionally, you can use the Sort Ascending and Sort Descending buttons on the Form toolbar in form view to sort by a field in ascending or descending order, or the Filter by Selection button to filter by the selected value.

However, if you need to filter or sort by two or more fields, you’ll need to create a query. Apart from filtering and sorting, though, there is an excellent reason to create a select query for use as a record source. Typically, for each main data table in an application, you will need several standard calculated fields—for example, fields that arrange the person’s name either last name first or first name first, a concatenated City/State/Zip field, or a full address field composed from several address fields. If you create these expressions in a select query, you don’t have to create them separately in each form or report that needs them, and if you need to make a change in an expression, you only have to make the change once (in the query), not in all the forms and/or reports that use these expressions.

The next section describes a number of standard query field expressions that can be used to either concatenate data from several fields into useful expressions, or split up data in one field into separate fields.

Query Expressions That Split or Concatenate Name and Address Data

Sometimes you have a table (possibly imported from a flat-file database or a text file) that has a whole name in a single field (either last name first, or first name first), and you need to separate the first name, middle name (if any), and last name into individual fields. Similarly, you may have a whole address of one to three lines in a single field, or the city, state, and zip in a single field, and need to create separate fields for each address component.

At other times, you may need to do the reverse, usually when exporting to a nonrelational database, or to another Office application, such as Word. For example, you may want to put a person’s complete address into a variable for export to a Word doc property, to use as an address block in a Word letter, or you may need to put a person’s whole name into one field, last name first, to export to a comma-delimited text file for import into a mainframe database.

The QueryExpressions sample database has two tables of sample data. tblContactsWhole has the following fields:

• ContactID

• LastNameFirst

• FirstNameFirst

• Address

• CityStateZip

All the fields except ContactID contain data that you might need to split into separate fields to make it possible to sort and filter by data such as the person’s last name or the city or state. This table is shown in Figure 4.1.

Figure 4.1

The tblContactsSeparate table illustrates the opposite scenario—a properly normalized table (like those in the Toy Workshop sample database) with name and address components stored in separate fields:

• ContactID

• FirstName

• MiddleName

• LastName

• Address1

• Address2

• Address3

• City

• StateOrProvince

• PostalCode

This table has three separate address fields, which is generally not necessary in Access since multiline address data can be stored in a single field (see the tables in the Toy Workshop sample database for examples of multiline address data). In some cases, however, it may be a good idea to store address data in several different fields, for example to separate a mailing address from a physical address—or perhaps the table was constructed by someone who didn’t know that multiline addresses can be stored in one field. tblContactsSeparate is shown in Figure 4.2.

Figure 4.2

The Query Expressions sample database contains two empty target tables: tblTargetSeparate and tblTargetWhole, and four select queries with expressions that split or concatenate data from the raw data tables.

The target tables are cleared by a function run from the AutoExec macro every time the database is opened—one of the few remaining uses for macros in Access.

Two append queries demonstrate appending split or concatenated data from the raw data tables to the target tables. To use the expressions in these queries in a real-life application, you will need to create queries based on the tables in your database and copy in the query expressions you need to use to concatenate or split data names in the queries. Edit the field names in the expressions as needed to match your tables, and (for append queries), select the appropriate target table for appending.

qrySplitAddressComponents and qrySplitNameComponents are select queries that split the address and name components into separate fields; these queries can be used as is or converted into make-table or append queries as needed. qryConcatenateAddressComponents and qryConcatenateNameComponents do the opposite: they concatenate data in separate fields into one field, for export to flat-file databases or other applications.

In the select queries qrySplitAddressComponents and qrySplitNameComponents, there are intermediate fields (the ones ending with a “Plus” and the CityStateZip field), which are used to break out a portion of a name or address that in turn needs to be further broken down. These expression names are used in turn in other query column expressions. This technique works fine in a select query, but sometimes fails in an append query (more so in older versions of Access), so in the append query, I took the entire “Plus” expression and pasted it into the other expressions that referenced it. Examine the select queries to get an idea of how the field breakdown works.

The name in a whole name field might be arranged either last name first or first name first, so I made different expressions to extract name components from each type of field; the sample append query uses the last name first version.

Here are the query column expressions used in the select queries:

qryConcatenateAddressComponents

CityStateZip: IIf([City],[City] & “, “,””) & [StateOrProvince] & “ “ & [PostalCode] Address: IIf(Nz([Address2])<>””,[Address1] & Chr(13) & Chr(10) & [Address2],[Address1]) & IIf(Nz([Address3])<>””,Chr(13) & Chr(10) & [Address3]) WholeAddress: [Address] & Chr(13) & Chr(10) & [CityStateZip]

qryConcatenateNameComponents

LastNameFirst: [LastName] & IIf([FirstName],”, “ & [FirstName],””) & IIf([MiddleName],” “ & [MiddleName]) FirstNameFirst: IIf([MiddleName],[FirstName] & “ “ & [MiddleName],[FirstName]) & IIf([LastName],” “ & [LastName])

qrySplitAddressComponents

City: IIf(InStr([CityStateZip],Chr$(44))>0,Mid([CityStateZip],1,InStr([CityStateZip],Chr$( 44))-1),[CityStateZip]) StatePlus: Mid([CityStateZip],InStr([CityStateZip],Chr$(44))+2) State: IIf(InStr([StatePlus],Chr$(32))>0,Mid([StatePlus],1,InStr([StatePlus], Chr$(32))-1),[StatePlus]) Zip: IIf(InStr([StatePlus],Chr$(32))>0,Mid([StatePlus],InStr([StatePlus],Chr$(32))+1),””) Address1: IIf(InStr([Address],Chr(13)),Left([Address],InStr([Address], Chr(13))-1),[Address]) Address2: IIf(InStr([AddressPlus],Chr(13)),Left([AddressPlus],InStr([AddressPlus], Chr(13))-1),[AddressPlus]) Address3: IIf(InStr([AddressPlus],Chr(10)),Mid([AddressPlus],InStr([AddressPlus],Chr(10))+1)) AddressPlus: IIf(InStr([Address],Chr(10)),Mid([Address],InStr([Address],Chr(10))+1))

qrySplitNameComponents

FirstNameL: IIf(InStr([FirstNamePlusL],Chr$(32))>0,Mid([FirstNamePlusL],1,InStr([FirstNamePlusL] ,Chr$(32))-1),[FirstNamePlusL]) FirstNamePlusL: Mid([LastNameFirst],InStr([LastNameFirst],Chr$(44))+2) MiddleNameL: IIf(InStr([FirstNamePlusL],Chr$(32))>0,Mid([FirstNamePlusL],InStr([FirstNamePlusL], Chr$(32))+1),"") LastNameL: IIf(InStr([LastNameFirst],Chr$(32))>0,Mid([LastNameFirst],1,InStr([LastNameFirst], Chr$(32))-2),[LastNameFirst]) FirstNameF: IIf(InStr([FirstNameFirst],Chr$(32))>0,Mid([FirstNameFirst],1,InStr( [FirstNameFirst],Chr$(32))-1),[FirstNameFirst]) MiddleNamePlusF: IIf(InStr([FirstNameFirst],Chr$(32))>0,Mid([FirstNameFirst],InStr([FirstNameFirst], Chr$(32))+1),"") MiddleNameF: IIf(InStr([MiddleNamePlusF],Chr$(32))>0,Mid([MiddleNamePlusF],1,InStr([MiddleName PlusF],Chr$(32))-1),"") LastNameF: IIf(InStr([MiddleNamePlusF],Chr$(32))>0,Mid([MiddleNamePlusF],InStr([MiddleName PlusF],Chr$(32))+1),[MiddleNamePlusF])

After you run qappConcatenateNameAndAddressComponents, tblTargetWhole’s fields are filled with data concatenated from separate fields in the original table (tblContactsSeparate), as shown in Figure 4.3.

Figure 4.3

Running qappSplitNameAndAddressComponents does the reverse: it splits data that was in single fields in tblContactsWhole into separate fields in the target table, tblTargetSeparate. This table is shown in Figure 4.4.

Figure 4.4

The expressions used to split or concatenate data depend on commas and spaces to parse out name and address components. If the fields in your source table don’t have commas after city names, or have a comma but no space after a last name in a LastNameFirst name field, you’ll need to modify the query expressions to work with the data in the source table. If (worst case) the source table data uses commas and spaces inconsistently in names and addresses, some fields will need to be cleaned up manually after appending.

You can adapt the query expressions for use in VBA code; in that case you can replace Chr(13) & Chr(10) with the newer (and more compact) VB named constant vbCrLf . Both of these represent a carriage return plus linefeed (CR + LF), with terminology harkening back to the old days of manual typewriters.

Totals Queries

Totals queries are not a separate query type—they have the same icon as regular select queries in the database window. They are simply select queries with the Total line in the query grid turned on by clicking the Totals button on the toolbar (it’s the one with the big sigma). The Total line offers a choice of a set of predefined calculations using aggregate functions. The most familiar (and widely used) are Sum, Count, and Avg, but you can also use a number of other more advanced aggregate functions, as listed in the table below.

If you want to count customers in tblCustomers, all you need is the CustomerID field, with Count selected in the Total row of that field. Since CustomerID is a key field, and thus can’t be blank, the count will be the same as the number of records in the table. If you do a count on the ContactTitle field, the result will be the number of records that have a value in the ContactTitle field. See Figures 4.5 and 4.6 for this query in design and Datasheet view.

Figure 4.5

Figure 4.6

A totals query can give you different counts or totals depending on which fields are selected in the query grid. When creating a totals query, it’s important to trim down the field list to remove any unnecessary fields, so the selected aggregate function will return the correct value. For example, if you base a totals query on tblOrders, and place ToyID and an ExtendedPrice calculated field on the query grid, with the Sum function selected for ExtendedPrice, you will get a sum of ExtendedPrice for each toy—that is, the total price of toys (ToyPrice * ToyQuantity) for all the orders for that toy. This query is shown in Datasheet view in Figure 4.7.

Figure 4.7

If you then remove ToyID from the query grid and replace it with CustomerID, you will get different sums, adding up the extended toy price by customer, as shown in Figure 4.8 (I added tblCustomers to the query too, so I could show the customer name and not just the CustomerID).

Figure 4.8

If you find that a totals query isn’t returning the correct values, remove any extraneous fields from the query grid, so you get the totals you intended.

tblToys contains several currency fields that you might want to add up, using the Sum function. You can also create a sum on a calculated field, for example a TotalCost field that multiples the UnitsInStock field by the SellPrice field, as shown in Figure 4.9 (Design view) and Figure 4.10 (Datasheet view).

Figure 4.9

Figure 4.10

You can use the qtot tag to identify all totals queries, or use separate tags corresponding to the different functions, such as qsum , qmin , or qmax .

When you select an aggregate function for a field, the aggregate field name in Datasheet view will be SumOfFieldName, CountOfFieldName, and so forth. If you want a less cumbersome name, you can alias the field name with another name, such as CustomerCount.

If you want to count the number of customers who have a specific job title, you can use a criterion on the JobTitle field plus a Count function to count just the customers with the title Marketing Director. However, you can’t do this in one field—if you do, you’ll get a “Data type mismatch in criteria expression” error message. Instead, drag the ContactTitle field to the query grid twice. Place the criterion on one of the columns , and select the Count function for the other column, as shown in Figure 4.11.

Figure 4.11

Figure 4.12 shows the same query in Datasheet view.

Figure 4.12

The Min and Max functions are useful for determining the lowest and highest values in a field. I use the Max function in a totals query that determines the highest value used so far in the EmployeeID field, which is not an AutoNumber field, but a text numeric field whose value is filled in by VBA code that adds 1 to the highest value. This query is shown in Design view in Figure 4.13.

Figure 4.13