Search The Web

Today's Headlines

Thursday, June 28, 2012

Microsoft Access Tips & Tricks: Fun With Crosstab Queries

In this earlier post on crosstab queries, I explained the basic syntax of crosstab queries in Access.  I also provided several examples to illustrate how to use crosstab queries for the purpose it is commonly used for:  create summary tables of data on two dimensions at row and column intersections.  However, crosstab queries can also be used for some unconventional and downright fun purposes.  In this post, I am going to talk about a few such uses and provide some examples.

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

Full Multiplication TableThe 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

Lower Triangle Multiplication TableThe 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.
Fractions As Decimals
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")

CalendarNotice 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.

9 comments:

hamilton electricians said...

Thank you for this valuable information, I hope it is okay that I bookmarked your website for further references.

John Lee said...

I had been just browsing in some places and got to learn this post. I have to admit that we're from the hand of luck today if not getting this excellent post to see wouldn’t happen to be achievable in my opinion, at the least. Really appreciate your articles.
outlook free trial

Anca Loredana Stolojescu said...

It's a very good article.I've a problem with a crosstab query which is using an aggregate function with pivot and in Access works just fine,but when I link the Access query in Excel to put the results in Excel worksheet is giving me an error.

Blogannath said...

From your description, I am not sure whether the error is in Excel or Access or the operating system. If the query works in Access, you can directly export the query to Excel from within Access by going to the "External Data" ribbon, and clicking on the appropriate icon.

Anca Loredana Stolojescu said...

The error happens when I run the Excel file which is using the query from Access with a VB code to put the result in a worksheet .The query in Access was changed and uses aggregate function without having double values(if it's a solution to change the sintax without using pivot) and before the change the query was working just fine in excel.Can this be the problem?Or maybe I need to update the sheet in Excel to recognize the change?

Blogannath said...

Certainly sounds like the problem is on the Excel side. Either update the sheet or start with a fresh Excel file that has no knowledge of the previous Access query. Good luck.

Tips Simple said...

Certainly sounds like the problem is on the Excel side. Either update the sheet or start with a fresh Excel file that has no knowledge of the previous Access query

Foto Memek Bugil Abg tante Ngentot said...

Koleksi Bokep Indo
Foto Toket Abg
Foto Ngentot Memek
Foto Memek Perawan
Foto Bugil Montok
Foto Memek Abg
Foto Memek Tante
Foto Bugil
Foto Ngentot
Memek Semok
Memek Tembem
Foto Abg Bugil Mesum
Cerita Sex
Foto Tante Nungging Di Ranjang Pengen Di Sodok Kontol
Foto ABG Binal Nakal Pamer Toket Gede Terbaru
Foto ABG Cabe Cabean Pamer Tubuh Mulus Masih Perawan
Foto Tante Girang Telanjang Di Kamar Sambil Nonton Bokep
Foto Payudara Montok Mulus Enak Untuk Di Remas
Koleksi Foto Memek Abg Perawan Dengan Klitoris Pelontos Mengumbar Nafsu
Foto Cewek Bispak Pamer Memek Super Tembem
Foto Tante Girang Imut Lagi Ngentot Berdesah
Foto Cewek Telanjang Di Atas Kasur Habis Di Perkosa
Foto Telanjang Tante Girang Cantik Lagi Mandi Sebelum ML
Foto Memek Bugil Abg tante Ngentot

Joseph Smith Smith said...

Looking For Direct Source Unlock IPhone Code Services In USA, Then You Are Right Place To Get Served by Us.
Direct Source Wholesale Unlock USA IPHONE NCK codes

Visitors Country Map

Free counters!

Content From TheFreeDictionary.com

In the News

Article of the Day

This Day in History

Today's Birthday

Quote of the Day

Word of the Day

Match Up
Match each word in the left column with its synonym on the right. When finished, click Answer to see the results. Good luck!

 

Hangman

Spelling Bee
difficulty level:
score: -
please wait...
 
spell the word:

Search The Web