Search The Web

Today's Headlines

Saturday, January 19, 2013

Why Can't An Employee Be An Employer?

One of the popular stories making the rounds in the past week is about a computer programmer at an unnamed American company.  It turns out that this programmer found a simpler way to get his work done than doing it himself:  he outsourced his work to other programmers in China.  And he managed it all well enough to get excellent annual reviews from his own employer.  If you missed it, you can read the story here.

So, the employee became an employer.  And the writer of the news story (and other versions of this story that I have read on other websites) refers to this as "a scam".  I am afraid I don't agree with that characterization.  And I am not alone in that.  Usually, stories on Yahoo attract a range of commenters, and most commenters do not agree with others.  But, in the case of this story, there is pretty much near-unanimity among the commenters:  none of them consider this employee a scammer, and in fact, most of them consider him clever and enterprising.

Now, what he did was probably not entirely legal according to whatever employment contract he signed with his employer.  And the way he outsourced the work could lead to a security breach (which is why his employer started investigating in the first place).  He could have set it up so that his "employees" could log into just his workstation, and he should have logged his workstation into the company network himself using the correct security measures instead of letting his "employees" log into the network directly with his credentials.  But, a security breach never did occur, the work he was responsible for doing got done, and that is all that should matter.

But, apparently, the employee is no longer employed because of doing what he did.  That got me thinking.  What exactly is illegal about what he did?  Why did he deserve to lose his job over this?

From an economically rational point of view, I don't see anything wrong with what he did.  He took part of his pay, gave it to someone else and had them do his job.  So, he traded in some of his money for more free time.  Seems like a perfectly rational thing to do.  Think about what a company does.  The owners of the company (the shareholders) don't think they want to or are capable of doing what a chief executive needs to do.  So, they take some of the company's money, give it to somebody and ask him to do the CEO's job.

The CEO does not run the company single-handedly either.  He trades in more of the company's money for employees (vice presidents, secretaries, etc.).  He then gives these people some more of the company's money and asks them to trade it in for more employees.  It doesn't exactly happen in the orderly sequence I have outlined above, but that is how companies are run:  money is traded in for employee time.

I am sure this computer programmer's manager did the exact same thing:  he was handed a chunk of the company's change and asked to trade it in for manpower so that everything the manager is tasked with doing would actually get done.  So, he traded some of that money in for this computer programmer.  Most computer programmers stop the chain at that point, and trade in their own work for that money.  The programmer who is the subject of this story went further, and traded in some of his money for somebody else to get the work done.  And we actually don't know whether the chain stopped there or the person who he was dealing with got somebody else to do the actual coding (which is quite possible!).

Think about the differences between his manager and him.  His manager outsourced some of his own work to this computer programmer using the company's money, not his own.  The programmer outsourced some of his own work to somebody in China using his own money, not the company's.  Ultimately, it did not cost the company any more money, the computer programmer got extra free time that he would not have gotten otherwise, and the programmer in China earned some money that he otherwise would not have.  To me, that sounds like a win-win-win.

The real problem may have been that the company fired the programmer for being made to look bad.  It is obvious that they were wasting a lot of money when they could have found programmers to do the same work for much less money.  This employee exposed them, and firing him was their revenge for that.  At least, even if he is not qualified to be a programmer, he seems more than qualified to oversee their outsourcing program (and believe me, I would be shocked if this company did not outsource any of its work to contractors, domestic or international).  And what the programmer did was not easy.  If you have dealt with outsourced resources in an IT environment, you know exactly what I mean.

But the story opens up another train of thought for me:  This programmer worked in an office, so he had to show up for work, look busy, etc. to earn his money.  For people who work from home, whose work is mostly unsupervised except for the couple of weekly meetings they are expected to phone in to, this kind of enterprising spirit can be very useful.  The employee can work at several different jobs with several different employers, and outsource all of the work to several different "employees" with small parts of his incomes.  The more he does it, the more his total income.  He would be limited only by the amount of work he can coordinate.  But not to worry, he can perhaps outsource that work to somebody else for a little money too!  Behold the birth of a body-shop contract programming outfit!!

Sunday, January 6, 2013

The Republican Vision For America

With the Republican party moving more and more to the right of the political spectrum, and the rise of the Tea Party movement pushing it along in that direction forcefully, one has to wonder what the ultimate Republican agenda for America is.  If one analyzes their policy positions and priorities, I think their vision for America becomes quite clear.  Without going into great detail, here are the salient bullet points:

1.    Enable the wealthy to become wealthier while pushing everybody else farther and farther down the economic ladder.  This is done in the name of "Tax Reform".
  • Push for a flatter and flatter tax system, where everyone pays taxes at the same marginal tax rate
  • This is obviously regressive because certain taxes like Social Security contributions are limited by income.  So, the higher your income, the lower your overall average tax rate will be
  • Moreover, when sales taxes are taken into account, the tax system tilts more towards regressiveness because people with lower incomes spend (rather than save) more of their money, and therefore pay a higher percentage of their income in sales taxes
  • The Republicans will also want to eliminate taxes on the sources of income favored by the wealthy, such as capital gains and dividends.  On the other hand, tax breaks that the poor and middle class take advantage of, such as home mortgage deduction, child tax credit, earned income credit etc., will be cut back or eliminated entirely
2.    Eliminate the social safety net.  This is done in the name of "Entitlement Reform", "Welfare Reform", etc.
  • Raise the social security and medicare eligibility ages until they become almost meaningless as retirement programs.  People will be forced to work until they are practically incapable of dragging themselves out of bed to get to work.  And their quality of life in the small window of retirement will be so miserable (more on that in the next bullet point) that people will never want to retire unless forced to by their physical state
  • Manipulate the cost of living adjustment until the payments from these retirement programs are completely incapable of providing for a decent retirement to the recipients
  • Make Medicare a block grant type of program where future retirees get fixed amounts of money from the government to pay for private health insurance.  And when the manipulated cost of living adjustments hit these block grants, retirees will find themselves incapable of maintaining any kind of decent health care coverage in their golden years
  • Introduce "means-testing" to limit payouts under these programs.  The problem with means tests is that wealth as defined in these means tests may not be equivalent to income.  So, your retirement income from these programs may be curtailed or completely cut off based on the fact that you own a home (which does not generate income for your retirement expenses).  Retirees may be forced to sell fixed assets for day to day income, aiding in the concentration of wealth in the hands of the few who can afford to buy up these assets
  • Eliminate or impose strict limits on unemployment insurance.  The Republicans have been vocal in opposing extension of unemployment benefits to the long-term unemployed even in the face of persistently high unemployment and slow economic growth.  They have also opposed retraining programs for unemployed people to learn new skills more suited to the changing economy.  All this will culminate in the elimination of these programs entirely in favor of "private sector participation" (which will never happen because retraining and other programs cannot be run profitably, given that the beneficiaries of these programs are unemployed and are not in a position to pay for them)
  • Eliminate or impose strict limits on poverty mitigation programs such as food assistance.  Republicans will cite "rampant fraud" to make these programs so onerous to qualify for that they will be nothing but programs on paper.  It will suit the Republican agenda much better to have these desperately poor people commit crimes for which they can be incarcerated in private jails run by their wealthy donors than to provide them with life's basic necessities so that they can try to make something of their lives
3.    Unravel and eliminate worker protections.  This will be done in the name of "Job Creation".
  • Weaken unions by passing right to work legislation that makes it impossible for unions to form or be financially viable
  • Get rid of the minimum wage or make sure that it does not keep up with the cost of living, making it largely meaningless
  • Remove worker protections, making it easy for employers to keep wages low by firing anyone for any reason and replacing them with lower-wage workers, either American or foreign
  • Eliminate regulations, making workplaces more unsafe, but cheaper
  • Make the job market a free-for-all where the lowest bidder is the only one who stands a chance of getting a job.  Average wages will grind downwards while corporate profits soar.  The middle class will be eliminated while wealth concentrates in the hands of fewer and fewer
4.    Impose a social agenda that alienates the well-educated.  This will be done in the name "Moral Values".
  • Impose restrictions that make science education in this country a farce.  It is fairly routine for US states to be the only jurisdictions in the entire world where basic science education is hampered by meaningless controversies such as over the teaching of evolution, climate change, etc.
  • Pass socially regressive legislation on civil rights, gay rights, women's rights, etc.
  • Persecute scientists, educators, higher educational institutions, etc. for pursuing research that goes against the Republican agenda (research into climate change, renewable energy, stem cell research, etc.)
  • Starve the higher education system of the country of funds by curtailing grants to the most innovative areas of science, medicine and other high-tech fields
  • Push anti-immigrant legislation that makes it more onerous for foreign students to come to the US for higher education.  Combined with the persecution of scientists and the lack of funds, graduate students will go elsewhere, starving the US of the most innovative ideas that the country has been a disproportionate beneficiary of all these years
  • Well-educated people with the wherewithal to move will leave the country to further their education and research aspirations.  They may also move simply to be in a better social environment where their rights are respected even if they are in the minority (immigrants, LGBT, etc.)  This will leave the US with a poorly educated, practically illiterate population that is easy to manipulate and control
  • Only the wealthy will be able to afford a good education in private institutions or in institutions outside the US.  Their wealth will enable them to start up new companies, but the benefits to the US population will be minimal since most employees of these companies will be based abroad, where the well-educated, qualified employees will be.  Companies will pay less for these employees than they would if they were in the US.  Combined with the lack of worker protections in the US, the US may once again become a country of factories where poorly paid, uneducated masses of laborers toil long hours under unsafe conditions, with no protections for whistle-blowing or recourse in the case of workplace accidents
 5.    Weaken regulations, making the country one in which "caveat emptor" takes on special meaning.  This will be done in the name of "Small Government".
  •  Increase the power of already powerful corporations over the general population by eliminating consumer protections and commonsense regulations
  • Make it easy for companies to pollute and harm consumers in other ways by eliminating the regulatory powers of government agencies like the EPA, FDA, etc.
  • Eliminate protections for natural habitats, endangered species, recreational areas, etc., by eliminating or reducing the need for permits and other scrutiny
  • Make corporations more powerful by giving them rights to spend unlimited amounts of money on elections.  Combined with the weakening of employee groups like unions, this will make it virtually impossible to counter their influence on the nation's politics and policies.  This in turn will lead to more business-friendly legislation, weakening employee rights and protections, consumer rights and protections, and regulations governing corporate conduct
6.    Concentrate wealth in the hands of the few by passing out government money disproportionately to people with good connections.
  • Increase military spending and war-mongering, making companies in the military-industrial complex super-rich and powerful
  • Hire mercenaries with no-bid contracts to run defense operations, making companies with the right contacts (and their owners) wealthy
  • Make more government functions private, and pick the winners to favor based on their political leanings.  Examples of this would include running penitentiaries, security and national defense, etc.
A porous or non-existent social safety net and the lack of meaningful options for retirement income and healthcare during retirement will make employees more dependent on their employers.  Employers will have greater power over employees just because employees are more and more afraid of losing their job or retiring.  When this is combined with weaker worker protections and looser workplace regulations, it will lead to a workforce that is cowed into submission for fear of being pushed into abject poverty.  The lack of education and transferable job skills will also make the position of US workers more tenuous.  The desperation of the workforce will lead to lower wages and benefits as practically anything will be considered better than nothing.

The middle and lower classes has already started feeling the pinch of these policy changes as wages for people outside the top 2% of income-earners has stagnated, and has, in many cases, gone down in real terms from where they were 20 years ago.  Trickle-down is an economic theory that no well-educated, well-respected economist subscribes to.  Making the wealthy wealthier will not improve the conditions of the others.  It will only make wealth and income inequality more acute.  And rising income and wealth inequality is the first sign of a failing society.

So, what would be the ultimate result if this vision comes to pass?  The US would become a 2-class society with a few super-wealthy robber barons, and millions of poor, uneducated laborers.  There would be virtually no middle class to speak of.  Cities will be vast slums with exclusive gated communities for the wealthy.  These communities will be guarded by military-style security outfits that will keep the chaos and riots (which will be commonplace everywhere else) outside these pockets of tranquility.  It will be a country with a high average income, but a tiny median income.  It will be a country in which some people will have everything they want and others will have nothing they need, leave alone want.  It will be a country where most of the land is polluted, stripped of natural beauty or resources, and unfit for human life, while the rich few have their own private lakes, rivers, pristine forests, etc. for their own entertainment and enjoyment.  It will be a land of serfs, controlled by wealthy, dynastic rulers.  It will be a land of opportunity for the few, and a land of despair for the many.

Saturday, October 13, 2012

Digital Roots Revisited: When Not To Get Carried Away With Them

In this earlier post, I talked about three properties of digital roots.  In simple mathematical terms, if the digital root of a number a with respect to base n is expressed as Dn(a), I provided a proof of the following properties of digital roots:
  • Dn(a + b) = Dn(Dn(a) + Dn(b))
  • Dn(a - b) = Dn(Dn(a) - Dn(b))
  • Dn(a * b) = Dn(Dn(a) * Dn(b))
 While working with my children on some brain-teasers, I thought I had come across another property of digital roots that I thought was quite interesting.  To set up the background, the problem I posed to my children as as follows:

Find the digital root with respect to 9 (i.e. the traditional definition of digital roots) of 1777^1777 (where ^ denotes the operation of raising to the power of).  I was not sure my children knew the properties of digital roots, but not only did they know the three above, but they used another one which I was not sure was even true.

The way I would solve the problem I posed, I would calculate D9(1777) as 4.  I know that the digital root of 1777*1777 is 7 because that is the digital root of 4*4, which is 16.  I also know that the digital root of 1777^3 would be 1 (the digital root of both 7*4 or 4*4*4 is 1).  The digital root of 1777^4 would be 4 (the digital root of 1*4 or 4*4*4*4 are both 4).  So, the pattern of digital roots as we raise 1777 to different powers is 4, 7, 1, 4, ... .  The pattern has a period of 3, so I would divide 1777 (the exponent) by 3 to get a remainder of 1.  This would then point me to the first number in my series of digital roots (4) as the answer to my problem.

My children, though, got the better of me.  They arrived at this answer well before I could.  How did they do it?  They took the digital root of both 1777's, and decided to find the digital root of 4^4 as the solution to my problem.  That gave them D9(256), which is 4.

So, is it true that D(a^b) = D(D(a)^D(b))?  Did I miss an important property of digital roots that can make some problems simpler than the method I was using for them all along?  I decided to verify if I could prove the correctness of what my children did, and this post is the result of that effort.

I started working out the proof using n as the base of my digital root for the sake of generality.  If that works, then we can be assured that it will work for the traditional definition of digital root, which uses 9 as the base.  But the general nature of the proof will give us confidence that it can be used with other bases also.

Let a = nw + x
Let b = ny + z

So, Dn(a) = x, and Dn(b) = z.

Let a^b = c.  Thus c = (nw+x)^(ny+z).

Now, we know that p^(q+r) = p^q * p^r.

So, we can write c as (nw+x)^ny * (nw+x)^z.  This is a product of two terms.  We can use the application of digital roots to multiplication to say that Dn(c) = Dn(Dn((nw+x)^ny)*Dn((nw+x)^z)).  Don't let the complexity of the expression throw you off.  It is basically a rewriting of D(a * b) = D(D(a) * D(b)), with a being equal to (nw+x)^ny and b being equal to (nw+x)^z.

Now, let us look at the number (nw+x)^ny.  This is basically (nw+x)*(nw+x)*(nw+x)*..., ny times.  When you expand out this product, you will find that all the terms in the answer except x^ny contain nw in them, and are therefore divisible by n without a remainder.  So, they don't contribute to the digital root at all.  So, Dn((nw+x)^ny) = Dn(x^ny).  By a similar logic, we can easily see that Dn((nw+x)^z) = Dn(x^z).

So, Dn(c) = Dn(Dn(x^ny) * Dn(x^z)).

Now, for my proof to work, I need to get Dn(x^ny) to be equal to 1 so that Dn(c) = Dn(Dn(x^z)).  I actually got stuck at this point.  And it was not from lack of trying.  I tried everything possible to try to prove that Dn(x^ny) = 1.  I was so brainwashed into thinking that the property was true, and it was my ability to prove it that was lacking that I wasted a lot of time mulling over the problem.  Basically, given the generality of x, n and y, this would mean that when you raise any number to a multiple of the digital root base, the digital root of the result should be equal to 1.

This is certainly true for some numbers like 4 (the digital roots with respect to 9 of whose powers cycle through 1 at multiples of 3).  But it is not true for numbers such as 2 (2^9 = 512, whose digital root is 8), 5 (5^9 = 1,953,125, whose digital root is 8) or 8 (8^9 = 134,217,728, whose digital root is 8).

I then used this knowledge to come up with a simple problem which actually disproves the property.  If you try to use this for 2^18 using 9 as a base for your digital roots, you will immediately see that the property does not work.  The digital root of 2 is 2 and that of 18 is 9.  So, the digital root of 2^18 should be the same as the digital root of 2^9 if this property were true.  But, unfortunately, the digital root of 2^9 is 8 and that of 2^18 is 1.

In fact, it turns out that when you use 9 as your base for digital roots, the property is true only when the digital root of the number being raised to powers (a in our case) is either 1, 4, 7 or 9.  It does not work for any other numbers.  You can verify that it does not work by calculating the digital roots of 2^10, 3^10, 5^10, 6^10 and 8^10 (if this property were true, the digital roots of these numbers should be 2, 3, 5, 6 and 8 respectively, and you can easily verify that they are not).

So, my children had gotten lucky because I asked them for 1777^1777, and 1777 has a digital root of 4.  And 4 just happens to be one of the few numbers for which the property actually works (at least as far as digital roots with respect to 9 go.  It does not work even for 4 when you work with digital roots with respect to other bases such as 7, for instance).

So, when you are asked to work out the digital root of a^b, where a and b are large numbers, you have to work it out the traditional way by figuring out the series of digital roots of powers of the digital root of a, find their period, divide b by that period to find the remainder, and then figure out what digital root that corresponds to.  You cannot short-circuit the process by taking the digital roots of a and b, and using them in an exponentiation operation to come up with the answer.  Yes, it will work if the digital root of a is 4 and you are working with a base of 9, but won't work with a lot of other numbers!

What is the point of all this then?  The point is that, sometimes, things work because they are special cases, not because they are true in general.  When your first encounter with something is that special case, you may be tempted to conclude that it works all the time and draw a generalization from it.  The point of this post is that it pays to be cautious.  If possible, work the math out and convince yourself with a proof that the case you encountered is not special in some way.  Don't get carried away with a generalization that is not justified.  Good luck!

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.

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