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 ",
" = ",
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 

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.

Tuesday, June 19, 2012

Why The US Should Spend Its Way Out Of This Recession . . . And Why It Won't

So, the US Congress and the President are locked in a battle over the budget priorities that should take precedence right now.  President Obama and the Democrats want to stimulate the economy with more government spending to create more growth and jobs so that the economy can grow vigorously and unemployment can be brought down.  The Republicans want to hold the line on spending, and rein it in even further so that debt levels go down or at least stay where they are, regardless of such austerity's effects on the economy.  Their argument is that if the government gets its debt house in order, that will encourage private investment, resulting in economic growth and job creation.

But, is it really such a stark choice?  What if there is a way to spend money to goose the economy without worrying about the debt spiraling out of control?  Maybe, there is actually such a way, and it is getting lost in the ideological battle going on at the highest levels of the government.

Last I checked, the US government-issued 10-year treasury notes (T-Notes) is 1.62%.  Also, last I checked, the inflation rate of the US in the last 12 months was 1.7%.  It was actually an annualized 2.3% in April, but because of a sudden steep fall in fuel prices, the May inflation numbers came in much lower than expected.  The average US inflation rate over the past 100 years has been about 3.4% per year.

What does all this mean?  It means that the real interest rate of a 10-year T-Note right now (actual interest rate - inflation rate) is actually negative.  So, investors are paying the US government money for the privilege of lending the US government money!  For every dollar the US government lends out at this rate, the government actually makes money!!

What would you personally do if people from around the world lined up and offered to pay you money to borrow from them?  Would you not be raking in the money hand over fist as fast as you possibly could?  Unfortunately, it does not happen to you or me.  Ever.  In fact, it almost never happens for governments either.  But the US is in an extremely sweet spot right now.  It is large and stable.  There are no systemic risks like there are with its only global competitor, the Euro-Zone (which, by the way, could probably take advantage of such interest rates if they decided to issue Euro-Bonds instead of individual country-backed bonds they have stuck with so far).

Obviously, once the US does start taking advantage of these negative real interest rates, the interest rates will start moving up slowly.  That is just the way the law of supply and demand works.  Right now, there seems to be a severe shortage of US 10-year T-Notes in the world, so people are willing to pay a very high price for every one issued, making the absolute interest rate miniscule, and the real interest rate negative.  Once the supply of US 10-year T-Notes goes up, its price will fall making the absolute interest rate higher.  Eventually, the real interest rate will become zero and then positive.

But, right now, the window of opportunity is still open.  This is what I would do if I were in the enviable position the US government seems to be in right now:  I would issue as much debt as I could at negative interest rates.  I would stop issuing debt only when the real interest rate on the debt becomes positive.  How much debt could the US issue at a negative real interest rate?  Who knows?  But it will probably be in the tens or hundreds of billions of dollars.

That money can then be used to stimulate the economy and create jobs.  Rebuild infrastructure.  Spend money on education, job-training, and research and development.  The effects on the economy and the unemployment rate can be quite immediate and dramatic.  Just the knowledge that the government is prepared to spend money on the economy is enough to spur private businesses that are sitting on the fence into growing and hiring.  Businesses that do not grow when the economy grows will be left behind, and businesses know that.

Obviously, fiscal hawks find all this talk of government spending quite distasteful.  But there are two reasons it should not be distasteful.  Firstly, as mentioned previously, the government is going to be making money on every dollar it lends out.  In fact, as the economy picks up steam inflation is likely to go up too, so the government can afford to borrow money at higher absolute rates and still make money in the process!

Secondly, this course of action should be embarked upon with the full knowledge that it is not going to go on for ever and ever.  In fact, there should be strict limits on when the government can engage in this.  First, the real interest rate has to be less than or equal to zero.  Secondly, the spending should be wound down as the growth rate of GDP exceeds a certain number and the unemployment rate falls below a certain number.

When the second condition is achieved, the economy is already on solid footing and the government does not have to support it with spending anyway.  At the same time as the government spending on these stimulus programs is winding down, government receipts increase because of increased tax revenues.  These extra revenues should then be used to pay down the debt that was accumulated during the economic weakness.

Ideally, the process should have worked in reverse:  the government should have accumulated a reserve of money in the form of fiscal surpluses during economic good times.  These reserves can then be used to stimulate the economy when such stimulus is needed, with borrowing being a last resort only if the reserves are found to be inadequate.  And the reserves should be rebuilt once the economy recovers.

After all, this is what most people with common sense do.  It is not rocket science.  When you are in a good job and can afford to save money, you are supposed to lay away some money for a rainy day.  When you hit a bump in the road in the form of unexpected expenses or a job loss, you are supposed to access your savings to get over the rough spot.

Unfortunately, just like the US government, people have been ignoring this for the longest time.  They have lived on borrowed money (credit cards, home equity loans and the like) instead of saving money for a rainy day.  They have hit their individual debt ceilings, and then broken through them with new credit cards and more borrowing.  Now, they have no cushion to fall back on when they face a real need, such as a job loss or unexpected medical expense.  Bankruptcy and foreclosure result.  Unfortunately, the average Joe cannot borrow money at a negative real interest rate to tide over tough economic times.

But, the government still has the wherewithal to weather this without further pain.  It can take advantage of record-low interest rates to finance a robust recovery spurred by government spending.  The question is not whether the economy can be stimulated in the short term at practically no cost right now.  The question is whether they will get fiscal religion, and start doing what needs to be done to clean their fiscal house for the long term.  Will they start paying down the debt and putting down reserves in advance of the next economic downturn?

The Democrats have tended to break the bank in the past by not curtailing government spending when the need for it no longer exists.  Short-term problems tend to get saddled with long-term solutions that are wasteful and create government spending long after the need for such spending is gone.  Will they have enough discipline to adhere to strict conditions on when the borrowing and spending needs to stop and the paying down of debt needs to begin?

The Republicans have tended to break the bank in the past by doling out tax breaks (and getting into unnecessary and expensive wars).  There is nothing wrong with tax breaks as long as the government's needs are taken care of before the tax breaks happen.  Has the debt been paid off and a sufficient reserve built up?  At that point there is nothing wrong with tax breaks.  But short-changing the government to give tax breaks to the wealthy is not a sound idea.  Government reserves are not a waste of money.  They are necessary to tide over the troughs in the economic cycle that are inevitable.  Hamstringing the government from being able to provide such stimulus will only make these troughs longer and deeper.  Will they have enough discipline to adhere to strict conditions on the size of the reserves required before tax cuts can begin?

Assuming the two sides are mature enough to make the commitments above in the spirit of true cooperation, the way out of this long recession is clear, and could be quite an easy path.  Unfortunately, the maturity of both sides is questionable.  Even if they could make the commitments required, their ability to live up to those commitments is suspect.  So, perhaps the US is doomed to muddle its way out of this recession much more slowly than necessary.  What a shame.

But I have always believed that in a democracy, the people always get exactly the government they deserve.  Different people can blame different parts of the government for their plight, but ultimately, it is a government of them, by them and for them.  If they cannot agree on who to blame, is it any wonder their representatives cannot agree on how to get the economy moving again?

Monday, June 11, 2012

Unclear On The Concept

I was reading a news article over the weekend about how nutritionists are changing the popular view that you might suffer gross bodily harm if you don't drink 2 liters of water a day every day of your life.  I was reading it primarily because I have never followed what seemed like bad advice from the beginning.  I was a strong believer in responding to signals that a healthy body produces, such as thirst and hunger, before blindly stuffing myself with excessive amounts of food or water or anything else for that matter.  So, I was happy that this article vindicated my approach to taking care of the fluid needs of my body.

What I was surprised by was the number of comments beneath the article that not only seemed to demonstrate that people were ignorant about how the human body works (which is understandable in that not everybody wants to go into great technical depth about how their body works), but more importantly, about how science in general works.

There were several comments complaining about how scientists keep changing their views and recommendations, and how new research seems to invalidate a lot of older research results.  To me, all this seems completely natural and the way it should be.  But this seems to make a large number of people very uncomfortable.  People want absolute certainty in life, and science does not seem to want to oblige!

To me, the ability to change and evolve constantly is what makes science valuable.  Science advances only when old "truths" are refined or modified or completely set aside by new scientific findings.  The advancement of science does not mean that scientists in times past were wrong or stupid.  They did the best they could with the tools and techniques of their times.  New tools enable scientists to observe and quantify new things that may invalidate older observations.  Scientific techniques also evolve, making observations and measurements more accurate and reliable.  And, last but not least, scientists are only human:  so, sometimes they make mistakes that are not caught right away.

And let us not even get into the arena of pseudo-science, where corporations and other interested parties buy "scientists" to produce spurious results that favor their viewpoints.  The best known example of this kind of "science" is the effort that companies like Exxon-Mobil undertook as part of their corporate policy to create fear, uncertainty and doubt (FUD) in climate-change research.  Other examples of this are the large number of websites spouting absurd hypotheses about the health effects of whatever they are touting (usually some miracle supplement that makes you rich, handsome and healthy while creating world peace and solving world hunger), or dissing (genetically modified crops, food additives, vaccination, or whatever else catches their diseased imaginations).

Because of this, I can understand why some non-scientific people find the whole scientific process suspect and unsettling.  Answers change all the time.  It is difficult, if you don't have a good scientific background, to know what to believe and what not to believe.  But instead of making an effort to understand science so that they can appreciate the progress science has made, or evaluate "scientific" claims in a more balanced way, many people seem to want to vent their anger and frustration at their own ignorance on science and scientists.

Perhaps, this is one of the reasons for the popularity of religion compared to science.  After all, religion is the exact anti-thesis of science:  nothing changes, everything is certain.  The old is never replaced by new (either in thought or action).  There is no need or attempt to verify that what is presented as truth is actually true.  There is no need for painstaking research.  There is a well-organized hierarchy of religious figures (pastor, priest, bishop, cardinal, pope, etc.), and when there is a conflict of views, you always know who is correct (the one higher up in the hierarchy) and who is wrong (the one lower down in the hierarchy).  And you can safely ignore religious figures who are not part of your religion, so that makes it even easier to find and follow "the truth".

Well, here is my attempt at putting in words my thought-process when it comes to evaluating scientific claims, whether it be about your health or the health of the planet or the state of the universe:

  •  Learn a little about how science works.  Science is all about making valid connections that are true regardless of who tries to make the connection.  Science is repeatable.  Scientists publish their methods and results, and other scientists have to verify that when the method is repeated, the results also repeat (heard of cold fusion lately?)
  • Learn a little about how the world works.  The internet has made science so much more accessible than it used to be.  You don't have to go hunting for books at a library and wait for years before the latest science is available in print form.  Websites like wikipedia, howstuffworks, etc. make scientific concepts easy to understand.  They also make it easy for everyone who has the inclination to understand the broad principles behind any field of science, whether it is human physiology or geology or astronomy.
  • Who is the scientist making the claim?  What are his/her qualifications?  Does he/she have a track record of publishing peer-reviewed scientific papers in famous scientific journals in the field?
  • Who is paying for the research?  Is there a hidden agenda?  This can be hard to find out.  If the claim is published in a famous scientific journal, usually such financial ties must be disclosed.  But if the "scientist" just sets up a website to broadcast his agenda, he/she need not disclose any such ties.  So, I always take non-peer-reviewed "scientific" discoveries and findings with a big bag of salt.
  • Does the scientific claim seem plausible and common-sensical?  If the finding is from a famous scientific establishment with a long track record, and the finding has been peer-reviewed and found sound, then it is quite possible it is true and correct even if it sounds implausible at first (who would have believed that the earth revolved around the sun when it was clearly obvious that the earth was flat and the sun revolved around it from east to west?).  However, if the previous two filters raise questions about reliability, then the bar is pretty high for a claim to pass the smell test, as far as I am concerned.  So, when a former electrician "discovers" an amazing health supplement, and chooses to set up a website to tout it rather than publishing his "findings" in a good journal, it is time to move on!
Maybe, this post will help someone who is on the fence about science appreciate it for what it is.  Yes, it comes with warts, but it is still beautiful!  More importantly, maybe it will people appreciate science for what it is not:  it is not dogma.  It is not static and unchanging.  It is not magical or miraculous.  It makes no promises that it cannot keep.  And it is not evil any more than any other inanimate object in the universe such as electrons and protons, or stars and planets, are evil.  Most importantly, I hope it reduces the number of people who are unclear on the concept of science.  Religion is religion and science is science, and there is nothing that prevents anybody from being religious about certain aspects of their life and scientific about other aspects of it.

Visitors Country Map

Free counters!

Content From

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!



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

Search The Web