Notice that zero values have been inserted into each select statement so the number of
columns in each select statement is the same. All the select statements have a column for
Monday, Tuesday, Wednesday, and Thursday, and although some of these carry the value of
zero, when they are combined they create a set of fields that are the same.
When this query is run it will combine the results from the two tables, but you will see
two records for each reference, one with the Monday and Tuesday figures and one with the
Wednesday and Thursday figures.
The next task is to make these appear as one row in the result. You can do this by nesting
your original query and using a Group By clause:
Select MyRef,sum(Monday),sum(Tuesday),sum(Wednesday),sum(Thursday) from (
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
) group by MyRef
This can start looking quite messy when the query statements are more complicated, but
the use of the Sum function and the Group By clause makes each reference generate only one
row in the result.
To make things easier, you can use your Union query as a subquery and then use this as
the source for the Group By query.
Select MyRef,sum(Monday),sum(Tuesday),sum(Wednesday),sum(Thursday) from
MyUnionQuery group by MyRef
The Delete Query
The Delete query simply deletes records from a table. It can either delete all of them or use a
criterion. Because it does not return records, it is run as a command:
In its simplest form it appears this way:
Delete * from MyTable
This clears out all records from MyTable. You can also use one or more criteria:Delete * from MyTable where CustomerName="Richard"
This assumes that MyTable has a field called CutomerName. This will delete all records
where the CustomerName field is equal to Richard.
You can use the Query Design window to build a Delete query. It is the same as the earlier
Select query, but you must click the Delete query icon in the Query Type group of the ribbon.
It is a good idea to look at the results in a data sheet view (by clicking the View icon in the
Results group of the ribbon) before you run the query. This will show you what will be
deleted without actually enacting the query. If anything is wrong at this point, you have not
damaged the database.
Chapter 12: SQL Queries 157