Microsoft Access VBA Macro Programming

This again works as before, but theAddNewmethod is used to insert a new record. The

fields within the recordset are provided with values. Notice that the square brackets are used

where there is a space in the field name and that the date is provided in a format that will fit

any locale.

The recordset is then updated and the new record is added.

For this to work, it has to adhere to any rules for the tables within the query such as

required fields, otherwise it will fail with an error.

If you are using a query on a form, then all the methodology to select, browse records,

update, and add new records is already provided (see Chapter 9). However, there are often

cases where you need customization of this kind.

Union Query

The Union query is a variation on the Select query. In this case, you wish to combine the

results of several select queries. It must be written in SQL within the SQL window and you

cannot use the Query Design GUI for this.

In its simplest form it is:

Select * from MyTable 1 union select * from MyTable 2

This combines the two select statements from MyTable1 and MyTable2, which seems

fairly simple. You can also use select statements from other queries here as well. However,

certain points should be kept in mind here.

For this query to work, the data in the first select statement must be identical in structure

to the data in the second select statement. This means that each select statement must return

the same number of columns and each individual column must have the same data type in

both select statements.

Also, the union statement removes duplicate records that it finds, so if there is an identical

record in MyTable1 and MyTable2, it will only appear once in the results. If you want to see

all the duplicates, then use Union All:

Select * from MyTable 1 union all select * from MyTable 2

A Union query can be very useful to combine two data sources where numeric data is

related to one common field but needs to be shown as individual columns. You can require

the numeric data to be shown for two particular dates, where the dates are column headings.

For example, you may have two tables of data, each with a common reference field called

MyRef. One table (MyTable1) has numeric data in with column headings for Monday and

Tuesday, and the other (MyTable2) has numeric data with column headings for Wednesday

and Thursday. You want to combine this into a query where each reference represents a

single record with the data for Monday to Thursday showing in separate columns.

You can initially use a Union query as follows:

Select MyRef,Monday,Tuesday,0 as Wednesday,0 as Thursday from MyTable 1

Union

Select MyRef,0 as Monday,0 as Tuesday, Wednesday, Thursday from MyTable 2

156 Microsoft Access 2010 VBA Macro Programming