Advanced Database – Create, run a crosstab query
A crosstab query involves creating a cross join between two tables, where every record of the left table is combined with every record in the right-hand table.
Create a new query in design mode and add the tables Orders and Parts. Select to display all fields from both tables.
Create a Cross join by dragging the primary key to the foreign key, then right click the connection click Edit and select ‘Cross Join’ from the menu.
Press F5 to run the query, and you’ll see that it returns repeated records from the Orders table, matched with repeated records from the Parts table.
This is because the Crosstab query creates what is known as a Cartesian product.
The best way to think of this is as a co-ordinate grid, containing the primary key and foreign key from the two tables.
Each cell within the co-ordinate (cartesian) grid contains all the records related to both keys.
So, for instance the Order No 202 when crosstab queried will contain the record data for that order, plus the record data for each of the parts A1-D2.
Thus, in this example the query returns 10x9=90 records.
We can verify this by making a simple amendment to the previous query.
In the function row of the query, select ‘Count’.
This will return the value of how many records there are in the query.
Press F5 to run the query
As predicted, the Count, i.e. the number of records present in the query is 90.
Crosstab queries can be used to display summaries of values such as Sum, Avg, Max and Min instead of count.
For instance, we may want a query to display the earliest order date and the average price of parts.
Use a crosstab query (cross join between Orders and Parts tables)
Select to display Date from the Orders table using the Minimum function.
Select to display the Price from the Parts table using the Average function. Then press F5.
