If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries, crosstab queries, working with system objects, listing table fields,finding unmatched rows, calculating statistics with grouping, job-candidate matching, job-candidate matching with skill levels, great circle distances, great circle initial headings, using Excel functions in Access, using the windows file-picker, using the Access date-picker, setting tab indexes quickly and correctly, pre-filling forms based on previous entries, highlighting form controls, conditional formatting, performing date manipulations, counting letters, words, sentences and paragraphs, calculating age, propercasing text entries, flattening a table (using SQL), flattening a table (using VBA), cascading comboboxes, parsing file names, opening files from within Access, and identifying runs of data.
Microsoft Access has a built-in query wizard for crosstab queries that makes creating such queries for conventional purposes quite easy. But this wizard has some limitations. In particular, the wizard will only work with tables that have 3 or more fields. It needs you to select at least one of these fields as row headers, another as column headers and the third field as the data to summarize at the row/column intersections. Technically, the wizard is supposed to work with queries as well as tables, but I have sometimes had trouble choosing a query rather than a table to work with when using the wizard. So, I have gotten used to writing crosstab queries from scratch using the SQL view of the query design window. I prefer the SQL view for all kinds of queries, but I find it particularly useful for writing unconventional queries that the wizards or design view do not handle well or at all. All the queries in this post are actual SQL that you would enter into the SQL view of the query design window. You will not have much luck creating them using the built-in wizards in Access.
Addition, Multiplication and Other Tables: The first unconventional use of crosstab queries I am going to present is their use for the presentation of multiplication, addition and other "tables". Everyone should be familiar with multiplication tables where a set of numbers is multiplied by another set of numbers, and the results are presented as successive rows for memorization by children (or anybody else, for that matter) who need to learn the results by heart.
Now, it is easy to create a non-crosstab query to produce multiplication tables in Access. For instance, let us say you need to create multiplication tables for multiplicands from 1 through 11. You could create a table called Numbers, which has a numerical field called Multiplicand, and fill it with the 11 numbers from 1 through 11. You would then use the query below to create a conventional-looking multiplication table:
select N1.multiplicand, " X ", N2.multiplicand, " = ", N1.multiplicand*N2.multiplicand as Product from Numbers as N1, Numbers as N2
This would produce output that looks as below:
1 X 1 = 1 1 X 2 = 2
And so on. You can use an ORDER BY clause in the query above to change the order in which the multiplicands change if you have a preference for which multiplicand changes first in a multiplication table.
Now, note that I have used a cartesian join in the query above. There is no ON clause in the join in that query: I just put the tables I want to pull records from in the FROM clause of the query, separated by commas. This causes every record in the first table to be joined with every record in the second table. Be very careful when you do this because you can inadvertently end up creating millions or billions of records in the result set if you join two large tables using a cartesian join by mistake.
The other thing to note is that I joined the Numbers table with itself in the query. I used aliases for each version of the table so that I could refer to their fields unambiguously. This is called a self-join. So, I created this basic multiplication table using a cartesian self-join. You can use the same principle to create other kinds of tables which use the two numbers to come up with a result, not necessarily just the product.
If you use a crosstab query to create your multiplication table, you can get a much more compact representation of the results in the form of a square grid in which there are rows and columns that contain the multiplicands, and the product is in the cells of the grid. The crosstab query that will allow you to do this is presented below:
TRANSFORM N.Multiplicand*N1.Multiplicand AS Product SELECT N.Multiplicand FROM Numbers AS N, Numbers AS N1 GROUP BY N.Multiplicand PIVOT N1.Multiplicand
The results of running the query would look like the picture on the left. The important thing to note is the complete absence of any aggregate function like avg(), count(), etc., in the crosstab query above. The standard syntax of a crosstab query requires that you use an aggregate function to fill in the grid created by the columns and rows. But you can apparently flout the rules, and create non-standard crosstab queries like this in Access without Access complaining or producing a syntax error. That is why this post is titled "Fun With Crosstab Queries"!
You will notice that in a commutative operation like multiplication, you get a symmetric matrix where the upper triangle reflects the lower triangle of the matrix. You can get rid of the redundant elements of the matrix and make it an upper or lower triangular matrix by trying a variation of the query like below:
TRANSFORM N.Multiplicand*N1.Multiplicand AS Product SELECT N.Multiplicand FROM Numbers AS N, Numbers AS N1 WHERE N.Multiplicand>=N1.Multiplicand GROUP BY N.Multiplicand PIVOT N1.Multiplicand
The results would then look like the figure on the right.
Obviously, if you are using this technique to create a table for a non-commutative operation (such as N1 raised to the power N2, or N1 divided by N2), then you should not use a WHERE clause to limit the results of the cartesian join.
Also, instead of using a self-join, you can use two tables of multiplicands that contain totally different sets of numbers. So, if you wanted a multiplication table of the numbers 25 through 34 multiplied by 1 through 10, you would join a table that contains the numbers 25 through 34 with a table that contains the numbers 1 through 10. Either that, or you can put the numbers 1 through 34 in one table, and use a cartesian self-join as before, but use the WHERE clause to limit the values of the two sets of multiplicands.
You can use this technique to create and keep handy a table that lists the decimal values of quarters, eighths, sixteenths, thirtyseconds, sixtyfourths, etc. Part of such a table is illustrated on the left.
Create A Calendar: Here is another fun application for crosstab queries that you can customize as you see fit. For the purpose of this query, you can use one table that contains the numbers from 1 through 31 (this table will then be used for both dates and month numbers), or you can use a table with the 12 month names in a separate table in addition to the table with the dates from 1 through 31.
Now, as we all know, not all dates occur in all months. In particular, February does not have a 30 or 31, and has a 29 only in one out of four years. Similarly, there is no 31st of April, June, September or November. When we join the table of dates with the table of months (or use a self-join of the date and month numbers table with itself), how do we limit the results to just valid dates? We can use the Access built-in function called IsDate() in the WHERE clause of the query to limit results to just valid dates.
We can then use the Format() function to derive the day of week name for each of the valid dates and use that to populate the grid. If you use DatePart() instead, you will get day of week numbers rather than day of week names. Once again, no aggregate functions at all! If you want to use a single table with both dates and month numbers, use the query below:
TRANSFORM iif(isdate(Months.DateNum & "/" & Dates.DateNum & "/2012"), format(Months.DateNum & "/" & Dates.DateNum & "/2012","ddd"), "") AS DayOfWeek SELECT Dates.DateNum FROM DatesOfMonth Dates, DatesOfMonth Months WHERE isdate(Months.DateNum & "/" & Dates.DateNum & "/2012") GROUP BY Dates.DateNum PIVOT Months.DateNum
If you want to create a separate table with month names in some format you prefer, then use the query below. This creates the calendar as shown on the left.
TRANSFORM iif(isdate([DateNum] & " " & [MonthName] & " 2012"), format([DateNum] & " " & [MonthName] & " 2012","ddd"), "") AS DayOfWeek SELECT DatesOfMonth.DateNum FROM DatesOfMonth, MonthsOfYear WHERE isdate([DateNum] & " " & [MonthName] & " 2012") GROUP BY DatesOfMonth.DateNum PIVOT MonthsOfYear.MonthName In ("January","February","March","April","May","June", "July","August","September","October","November","December")
Notice that in the second query, I use the IN sub-clause in the PIVOT clause to order the months from January to December rather than alphabetically, which is the default sort order for the column headers in a crosstab query. Also notice that I use an IIF() to limit the results to only valid dates. For some reason, the grid produces a day of Tuesday for all invalid dates even though the WHERE clause of the query already limits the dates produced by the join to just valid ones. It is one of those bugs/features of Access you just have to work around!
You can change the year (which is hard-coded to 2012 in both queries) to any year you want to see what a calendar for that year looks like. Have fun, and check out a calendar for the year 5783 if you are curious!
Hope you found this post useful and fun. SQL is very versatile, and this post explored some unconventional uses of crosstab queries. Have you used a crosstab query to do something it was not designed to do? Have you used any other SQL construct to achieve something that it was not meant to? Let me know in the comments section. If you have any problems or concerns with the SQL in this post, please feel free to let me know by posting a comment. Let me also know if you want me to address some other aspect of Microsoft Access in future posts.