Search The Web

Today's Headlines

Monday, January 31, 2011

Quick Comparison Of Different Types Of Snowblowers

My research into options for snow removal has progressed well. I decided to get myself a snowblower rather than continue working with a shovel or hire a contractor. I then started looking at different types of snowblowers and comparing them against each other. So, here is what I could glean from the comparison. There are three major types of snowblowers out there: two-stage gas blowers, single-stage gas blowers, and electric blowers.

Two-stage gas-powered snowblowers are the biggest and most powerful snowblowers out there. They are typically powered by 7 to 12 hp 4-stroke gas engines. They are called 2-stage blowers because they have a separate auger to collect snow and a separate impeller that propels the snow out through the chute and throws it clear of the area being cleared. They are very large and heavy machines, but they are also very powerful. They can clear wide paths and handle higher snow amounts than other types of snowblowers. They are expensive, and they cost a lot to maintain since they require all the maintenance that a regular car engine might require – oil changes, tune-ups, preparation for storage during the summer months, etc.

They require a lot of space to store (might require a separate shed rather than being squeezed into the garage along with the family car). Their size and weight also means that they are very difficult to transport and typically require a pickup truck with a loading ramp or a separate trailer. Keep this in mind if you plan on getting someone else to do the required maintenance such as oil changes on your snowblower.

Large, two-stage snowblowers are ideal for clearing large areas. They can be used on long, extra-wide or hilly driveways, moderate-sized parking lots, etc. Their throwing distance is very high, and the stream of snow that comes out of the chute is very high-powered to achieve this high throwing distance. One has to be careful not to direct the snowstream at things like cars, garage doors, etc., because the high speed of the discharge may cause damage. The augers of two-stage snowblowers do not come in contact with the ground, so they are ideal for gravel driveways and other areas where there is loose material under the snow that needs to be cleared.

Here are some features to look for in these monster-machines.

  • Driven wheels: Most two-stage snowblowers are self-propelled to make it easier to clear the large amounts of snow they can handle. Imagine pushing your snowblower (that already weights perhaps 200 to 300 lbs by itself) into a foot-high pile of snow!
  • Maneuverability: Most two-stage snowblowers allow direction changes by allowing the operator to disengage individual drive wheels from the engine. Some of them require the operator to stop the machine and disengage and reengage the wheels by using pins and other mechanisms. But the more expensive ones have handlebar triggers that enable the operator to maneuver the snowblower easily without stopping the machine. The ability to disengage the wheels from the engine is also essential to move the machine around without the engine running (such as inside your garage, etc.).
  • Heated handles: Many large snowblowers have handles that are heated by the engine. This makes operating the snowblower a little more comfortable though a good pair of gloves is still recommended.
  • Easy start: Many big snowblowers have an onboard battery for easy engine starts. It is difficult to pull-start a large 4-stroke gasoline engine, especially in cold weather, so this makes the snowblower easier to use. Some snowblowers have a plug-in starter instead of using their own battery. You need to be near an electric outlet to use this kind of starter. It can be inconvenient if you need to stop the snowblower far away from an electrical outlet.
  • Adjustable chute: Both the height as well as direction of the chute are usually adjustable without having to stop and restart the snowblower.
  • Extra wide auger: Two-stage snowblowers are typcially large machines which clear wide paths. The typical clearing width of a two-stage snowblower ranges from 22 to 30 inches or more.
  • High intake: The auger housing is typically more than a foot or more in height, allowing these machines to easily deal with heavy snowfalls and deep drifts. Most of these machines can easily handle snow depths of up to 2 feet without having to make multiple passes.
  • Headlights: Help when you have to clear snow in darkness or low-light conditions.
  • High cost: Two-stage snowblowers cost quite a bit. Expect to pay anywhere from $600 to over $2000 depending on features included, construction materials and construction quality.

Single-stage gas-powered snowblowers are smaller and less powerful than two-stage machines. They don’t have a separate impeller to throw snow, instead the auger both scoops up the snow and powers it out of the chute. These are typically powered by 4 to 7 hp gas engines. Some of the smaller engines are two-stroke engines and the larger ones may be four-stroke. They can not be used to clear areas with loose material under the snow (such as gravel driveways) because the auger comes in contact with the ground, and will pick up and fling the loose material along with the snow.

Two-stroke gas engines require the user to mix 2-stroke engine oil with the gasoline before adding it to the gas tank. Such engines typically require the gas tank and fuel lines to be cleaned thoroughly before long periods of storage so that the fuel-oil mixture does not separate and clog carburetors, fuel lines etc. Two-stroke engines are also noisy, less efficient, and more polluting than 4-stroke engines. But 2-stroke engines do not require regular oil changes.

Four-stroke engines are usually more powerful, more efficient, less noisy and not as polluting as their two-stroke counterparts. They do not require a gas-oil mixture in the gas tank, and typically do not require as much care before storage for long periods. But they do require regular oil changes to increase the longevity of the engine. Both types of engines may also require periodic tune-ups and other maintenance work. These mid-size machines are typically used for regular-sized driveways and walkways. They can be used on concrete as well as asphalt surfaces, but not on gravel. It helps if the terrain is level without steep ups and downs (since they are seldom self-propelled).

Single-stage snowblowers are not as heavy or large as two-stage ones. They can be stored in a garage alongside a car. Also, their handles can be collapsed so that they can be loaded into the trunk of a car to be transported if necessary (this may be necessary if you want someone else to do oil changes and other maintenance on these machines).

Most single-stage snowblowers are not self-propelled. They rely on the operator to push them across the area to be cleared of snow. The auger helps at times by pulling the machine forward, but it is not reliable and directional control is difficult. Typically these medium-sized machines are maneuvered around turns by lifting the front of the blower off the ground and then pushing one side forward while pulling the other side back. Here are some features you will find in single-stage gas-powered snowblowers.

  • Easy start: Many single-stage gas snowblowers come with a plug-in starter. They require a nearby outlet to use. Many others require the user to pull-start the engine. This can be quite difficult in sub-freezing temperatures. Almost no single-stage gas-powered snowblower comes with an onboard battery-powered starting system.
  • Adjustable chute: Usually, the direction of the chute can be adjusted by the operator while the machine is running by turning a crank in the appropriate direction. Adjustments to the height of the discharge chute typically require the operator to stop the machine and perform adjustments at the chute.
  • Medium-sized auger: The clearing width of a single-stage machine is usually in the range of 18 to 24 inches.
  • Medium intake height: Single stage snowblowers can usually handle snow up to about a foot deep. Heavier amounts of snow will require multiple passes to clear.
  • Headlights: Many single-stage snowblowers come with headlights to assist the operator in darkness or low-light conditions.
  • Medium cost: Single-stage snowblowers usually cost between $400 and $1000 depending on construction materials and features.

Electric snowblowers are usually the smallest snowblowers around. They are almost exclusively single-stage snowblowers with no separate impeller. In many other respects they are similar to single-stage gas-powered snowblowers. They are best suited for paved, medium-sized driveways and walkways, and not suited for gravel and other surfaces with loose objects under the snow.

But the differences between gas-powered snowblowers and electric snowblowers are quite significant. For one, they are virtually maintenance-free. They do not require oil changes or tune-ups. And they do not require any special care before long periods of storage. They are much quieter than the quietest gas-powered snowblowers, and are completely pollution-free (at least at the point of usage). They do not put out any fumes, so running them in enclosed areas is never a problem. Moreover, they are much lighter than even single-stage snowblowers, and can easily be carried around by a single person without any problems.

However, electric snowblowers are limited in their power because they can not draw more than 10 to 15 amperes from a regular household outlet. Even if they could draw 20 amperes (it may require a special circuit with extra heavy duty circuit breakers to install such an outlet), that would give them barely 3 hp of power. Most electric snowblowers draw between 10 and 15 amperes and therefore generate 1.5 to 2 hp of power at the motor. If the US had 220V or 240V power supply like in many other countries, you could get much more powerful electric snowblowers (in the range of 3 to 6 hp), but as it stands, the power of the snowblowers is limited by the power supply infrastructure and standards in place. I know of no electric snowblowers that can take advantage of the kind of outlet that an electric clothes-dryer uses (which typically provide 220V at 15 to 20 amperes, which can generate around 6 hp at a motor).

Moreover, almost all electric snowblowers require a cord to operate them. Their range is therefore limited to the length of the cord. The length of the cord is limited by the thickness of the wire (gauge), with higher thickness (lower gauges) required to carry the same amount of current over longer distances. To keep the cords from becoming monstrously thick and heavy, most extension cords are limited to about 12 or 10 gauge over a 100 foot length. A 12 gauge extension cord is required for any application that requires carrying more than 13 amperes over a distance of over 50 feet.

The cord not only limits the range of electric snowblowers, but can also cause other problems. The operator has to be forever cognizant of where the cord is and make sure that the snowblower does not run over the cord at any time. This requires a methodical and well-thought-out plan for covering the area to be cleared with not much room for free movement of the snowblower. Experienced operators develop a plan and stick to it, and consider the cord no more than an afterthought.

Electric snowblowers are never self-propelled. They rely on the operator to push them across the area to be cleared of snow. The auger helps at times by pulling the machine forward, but it is not reliable and directional control is difficult. Typically these machines are maneuvered around turns by lifting the front of the blower off the ground and then pushing one side forward while pulling the other side back. Here are some features you will find in electric snowblowers.

  • Maintenance-free: Electric snowblowers are completely maintenance free. They require no lubrication, no oil changes, tune-ups, etc. No special care is required for long-term storage during the summer. Just keep the electric cord tangle-free, and you are good to go!
  • Easy start: All electric snowblowers start with the push of a button. There is never any pulling of cords involved.
  • Adjustable chute: Usually, the direction of the chute can be adjusted by the operator while the machine is running by turning a crank in the appropriate direction. Adjustments to the height of the discharge chute typically require the operator to stop the machine and perform adjustments at the chute. Many electric snowblowers do not have a chute at all, instead just discharging the snow farther ahead of the snowthrower. That works for short driveways, but can cause problems for longer ones.
  • Medium-sized auger: The clearing width of an electric snowblower is usually in the range of 18 to 20 inches. They typically clear narrower paths than single-stage gas-powered snowblowers.
  • Medium intake height: Electric snowblowers are usually limited to snow up to about 8 inches deep. Heavier amounts of snow will require multiple passes to clear.
  • Headlights: Some electric snowblowers come with headlights to assist the operator in darkness or low-light conditions.
  • Low cost: Electric snowblowers are inexpensive compared to gas-powered snowblowers. They usually cost between $150 and $400 depending on construction materials and features.

It would be logical to ask why electric snowblowers are not battery powered. That way one could get rid of the limitations of the cord that practically every electric snowblower requires. There are a couple of problems with battery-powered snowblowers. First, to provide the required power and operating time, you need several batteries arranged into a battery pack. This can make such a snowblower very large and heavy. Moreover, batteries lose power rapidly as temperatures drop, so they are not the best choice in a cold-weather application like snowblowers.

There is actually one battery-powered snowblower on the market, but I have not been able to see one of them in person. It is available in a few online stores, but none of my local big box home improvement stores had the machine or any plans to stock it! The machine is a two-stage electric snowblower (probably another distinction unique to it), and looks quite large and heavy, as would be expected.

Based on these comparisons, I have made up my mind to get an electric snowblower. Where I live, we typically never get more than 6 inches of snow at a time, so a smaller machine makes the most sense. My driveway is not super-long, so I can live within the limitations of an electric cord. The freedom from having to perform regular maintenance on the snowblower is another big factor which pushed me away from gas-powered snowblowers. In fact, based on online reviews, I have settled on a Snowjoe SJ 620 snowblower. I will be sure to let you know whether my choice was good after I get the machine and have a chance to use it a couple of times!

Friday, January 28, 2011

Microsoft Access Tips & Tricks: Flattening A Table (Using VBA)

We saw in this earlier post how to flatten a table using purely SQL. In this post, I am going to tackle the same problem by writing a bit of VBA code to tackle the same problem. Once again, I will use the original table from that earlier post in this post also. Towards the end of the post I will talk about how the VBA version of the solution has some advantages over the SQL version of the solution.

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, and flattening a table (using SQL).

To refresh our memory, here is the “input” table for our task:

BookID AuthorName AuthorNumber
1 A 1
1 B 2
2 C 1
2 D 2
2 E 3
2 F 4
3 G 1

And, here is the output we want from it:

BookID Authors
1 A,B
2 C,D,E,F
3 G

The VBA I am going to present in this post is in the form of a function call that will do the aggregation of author names for each book ID. I have tried to make the function call have arguments similar to a function that exists in the MySQL implementation of SQL. MySQL actually has an aggregate function for concatenating items in a field in a group by query. That function is called group_concat(), and you can read technical details about how to invoke it and what arguments its takes here. My VBA function is going to take similar arguments, but not the same ones, and I am not going to spend my time dealing with all the options that group_concat() is capable of dealing with. What this means is that if you need something as flexible as group_concat(), you have to enhance the VBA posted here yourself. It also means that if your needs for flattening a table are not covered by the VBA posted here, you have to modify the VBA to fit your needs.

The version of group_concat() I have created for Microsoft Access is posted below:

Function group_concat(strSQL As String, groupField As Integer, orderBy As String, separator as String) As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQLFinal As String
Dim groupingValue As Integer
Dim groupedValues As String

group_concat = ""

strSQLFinal = strSQL & " where BookID = " & groupField & " " & orderBy

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQLFinal, dbOpenSnapshot)

If Not rst.BOF And Not rst.EOF Then
rst.MoveFirst
groupingValue = rst!BookID
groupedValues = rst!AuthorName
rst.MoveNext

Do Until rst.EOF
groupedValues = groupedValues & separator & rst!AuthorName
rst.MoveNext
Loop
End If

Set rst = Nothing
Set db = Nothing

group_concat = groupedValues

End Function

The basic way this function works is quite simple. As the first argument, you pass it the SQL statement that selects the necessary columns from the table. You can pass it any SQL statement that will have both the field you want grouped and the field you want to group by in it. The advantage of doing it this way is so that you have the option to use joins and other complicated SQL syntax if that is what you need to get what you want into the function (for instance, if the field you want to order by is in another table, you can join the original table with the other table in the SQL so that the field you want to order by is available in the final query).

The next argument is the actual value of the field that you want to group. In this case, it is the bookID value for which you want to collect authors. Notice that in this implementation, I have used an integer value in the argument declaration. If your ID is not an integer, you have to make appropriate modifications to the function declaration as well as how the value is used in constructing the final SQL (strSQLFinal). Also, if you are grouping by multiple fields, you have to change the VBA appropriately.

The third argument is essentially the order by clause of the final query that is built inside the VBA function. Here you can use any fields you have access to in the SQL statement (the first argument), and use standard SQL syntax regarding ascending sorts, descending sorts, etc. This flexibility to sort the concatenated values by a third field is a big advantage of this approach over the pure SQL approach. The fourth argument is simply the separator that you specify should be used between the concatenated values. You can pass it any string including a space (“ “), a comma (“,”) or even a tab (chr(9)). The passed string will be used verbatim between multiple values that are concatenated. The string does not have to be a single character long though most separators are.

Notice that the function does not take the column names of the fields you want to group by or the field you want to group as arguments. Those field names (bookID, and AuthorName in this example) are hard-coded into the function. As I mentioned earlier, this is not a direct one-to-one replacement for the group_concat() function in Access. Much of the functionality of the function is hard-coded rather than passed in as arguments, so you will need to get your hands dirty and change the VBA code in your application to suit your needs!

The function puts the first three arguments together to build the final SQL statement that is used to open the required recordset. As you can see, the second argument is used to construct the WHERE clause of the final SQL statement so that the recordset contains only rows that need to be grouped together. The third argument puts the rows in the order in which you want the values concatenated (concatenation is not commutative, like addition is. So, we need this extra step).

The rest of the function should be pretty straightforward to read and understand. All we do is open the recordset, and set the value of group_concat to the first value of the field that needs to be grouped. Then inside the loop (as long as there are more values to concatenate), we add a separator and then the next value of the field, and so on until there are no more values to concatenate. The final concatenated string is then passed out as the value returned by the function.

Posted below is some SQL that uses the group_concat() function. Notice that we have now used the authorNumber field to sort the concatenated values, so the final output actually makes sense in this application. After all, it is not often that you need a list of authors for a book arranged in alphabetic order!

select bookID,
group_concat("select bookID, authorName from myTable", bookID, "order by authorNumber",",")
as Authors
The output produced by this query is shown in the table below:
BookIDAuthors
1A,B
1A,B
2C,D,E,F
2C,D,E,F
2C,D,E,F
2C,D,E,F
3G

As you can see, since we are selecting all rows of the original table, we still get the same number of rows as in the original table in the output. Each bookID is repeated as many times as it is present in the original table, with the same concatenated set of authors in each row for that bookID. But getting to the final output from this query is quite simple: you can either use a DISTINCT clause to throw out the duplicates that we don’t need, or you can group by the BookID and take the minimum, maximum or average of the Authors field (since the authors field is identical for all rows that contain a given bookID, the result from all three aggregate functions will be identical too).

So, here is the final SQL that gives us the output we want:

select DISTINCT bookID,
group_concat("select bookID, authorName from myTable", bookID, "order by authorNumber",",") as Authors
FROM myTable
And here is the final output:
BookIDAuthors
1A,B
2C,D,E,F
3G

The advantages of this approach over the pure SQL approach are as follows:

  • Much more flexibility in terms of what fields you can use for sorting the concatenated values
  • No multi-self-joins that can cause resource issues and other problems with large tables
  • No restriction on how many values can be concatenated, and no need to determine in advance how many values to consider as “sufficient”
  • No extra separators when the number of concatenated values is less than the maximum number pre-determined in the SQL query

Hope this post has been helpful in solving any problems you might have had with flattening a table using VBA in Access. The VBA code in this post has been tested in Access 2003 and should work without any problems in all versions of Access from Access 97 on up. If you have any problems or concerns, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future posts, please feel free to let me know through your comments too. Good luck!

Wednesday, January 26, 2011

State Of The Union Address 2011: 15 Interesting Statistics

I loaded the official transcript of the State Of The Union address delivered by Barack Obama on 25 January, 2011 into Microsoft Word to do some research on it. Here are some interesting statistics I was able to produce from this analysis (the length of the speech, and start and end times are obviously from media sources, not from MS Word).

By the way, the White House does not seem to have much of an idea what a transcript of a speech is. The file they released contains not only the President's remarks, but the audience's reactions (such as Applause, Applause and laughter, etc.) in parentheses. I had to painstakingly search through the document and remove all such additions from the document before analyzing it for what the President said. The buffoon who released this doctored version of the state of the union speech instead of a true transcript should be sent straight back to school (preferably elementary school)!

The Word help files recommend aiming for a Flesch Reading Ease score of between 60 and 70 (the score goes from 0 to 100, with higher numbers denoting higher reading ease). So, Barack Obama made it comfortably into the recommended range with this speech. The Word help files also recommend aiming for a Flesch-Kincaid Grade Level score of between 7.0 and 8.0. On this score, our President's speech was too erudite and scholarly, with his speech scoring well over the high end of the recommendation. By the way, both measures of readability use the number of syllables per word in their calculations, but Word does not provide any statistics on syllables at all!

Interesting Statistics

Length of the speech: 1:01
Start time: 9:12 PM EST, 25 January, 2011
End time: 10:13 PM EST, 25 January, 2011

Words: 6873
Non-space characters: 32,456
Characters including spaces: 39,360
Paragraphs: 112
Sentences: 414
Sentences per paragraph: 3.6964
Words per sentence: 16.6014
Characters per word: 4.7222
Passive sentences: 7%

Flesch Reading Ease: 64.4
Flesch-Kincaid Grade Level: 8.2

Total number of unique words in the speech: 1676

Comparison of Statistics Between 2010 and 2011

Statistic

2010

2011

Length Of Speech 1:08 1:01
Start Time 21:11 21:12
End Time 22:20 22:13
Words 7076 6873
Non-space characters 33737 32456
Characters, including spaces 40767 39360
Paragraphs 105 112
Sentences 409 414
Sentences per paragraph 3.8952 3.6964
Words per sentence 17.3007 16.6014
Characters per word 4.7678 4.7222
Passive sentences 6% 7%
Flesch Reading Ease 61.8 64.4
Flesch-Kincaid Grade Level 8.6 8.2
Number of unique words 1671 1676

Two years don’t make a trend, but it looks like Obama’s speech pattern is trending towards shorter speeches, and using shorter words and sentences. So, even as the speech time itself has contracted, the number of sentences has increased. Shorter sentences with shorter words have made the speech more readable and reduced the grade-level of the material significantly.

Frequency of usage of words

Most frequently used word: "The". Used 355 times.
The words "and" and "to" came in second and third, used 243 and 220 times respectively.

Longest words used: A tie between Counterterrorism and Transcontinental (16 letters each)

People References
I: 67
Me, My: 10
We: 189
Us: 33
Our, Ours, Ourselves: 126
You: 35
Your: 7
They: 38
Them: 18
Their: 34
He, Him, His: 17
She, Her: 7
It, Itself: 68
Its: 4
That: 142
This: 55
These: 11
Those: 8
People, People's: 33
Family, Families: 7
Community, Communities: 7
All: 28
John Boehner: 1
Gabrielle Giffords: 1
Dwight Eisenhower: 1
Robert Kennedy: 1
Edison: 1
Wright Brothers: 1

There was no mention of the vice-president or the first lady in this speech.

Geographic References
America, America's: 27
American(s): 29
United States Of America: 2
Country, Countries: 16
Nation(s): 20
World: 18
Globe, Global: 3
Shores: 1
Skies: 1
Border(s): 4
Abroad: 3
Foreign: 1
Overseas: 1
Afghanistan, Afghan(s): 8
Alabama: 1
Arabian: 1
Asia, Asian: 3
Berlin (Pennsylvania): 1
Brazil: 1
California: 2
Chile(an): 3
China: 4
Cincinnati: 1
Colombia: 1
Denver: 1
El Salvador: 1
Europe(an): 2
India: 3
Iowa: 1
Iran(ian): 2
Iraq(i): 4
Korean Peninsula: 1
Michigan: 1
Midwest: 1
North Carolina: 1
North Korea: 1
Oregon: 1
Pakistan: 1
Panama: 1
Pennsylvania: 1
Russia: 2
Scranton: 1
South Korea(n): 5
Soviets: 2
Sudan: 1
Texas: 1
Tunisia: 2
Washington: 1

There was no mention of the words "International", or "Domestic" in this speech.

Religious references
Religion: 1
God: 2
Christian: 1
Hindu: 1
Jewish: 1
Muslim(s): 2

Policy references
Policy: 3
Health care: 4
Peace: 1
War(s): 10
Tech, Technology, Technologies: 11
Science, Scientists: 7

Economic, Fiscal references
Home, Homes: 9
Economy, Economic, Economist(s): 9
Employees: 4
Recession: 4
Stimulus: 1
Grow, Growing, Growth: 5
Business(es): 20
Company and Companies: 15
Job(s): 31
Investment(s): 7
Deficit(s): 11
Work(ed), Working, Workers: 33
Tax(es), Taxpayer: 14

There was no mention of the words "Recovery", "Employment" or "Unemployment" in this speech.

Political references
Political, Politics, Politicians: 3
Bipartisan, Nonpartisan: 3
Election, Elected: 3
Democrat(s) and Democratic: 10
Republican(s): 10
Party, Parties: 5
Congress: 10
Administration: 1
Government: 18
Democracy: 2
Freedom: 1

There was no use of the words "Partisan", "Tea Party", or "Rhetoric" in this speech. Notable in their absence also were loaded words like "Conservative", "Liberal", and "Progressive".

Safety and security references
Al Qaeda, Al Qaeda's: 4
Taliban: 2
Terrorists: 1
Secure, Securing and Security: 5
Defense: 3
Protect, Protections: 4
Counterterrorism: 1
Intelligence: 1
Law Enforcement: 1
Justice: 1

Question words
How: 12
What: 36
When: 12
Where(ver): 14
Who: 30
Why: 11
Which: 3

Answer words
Because: 17
By: 30

Positive words
Can: 37
Change(s,d) and Changing: 13
Reform(s): 7
New(er): 37
More: 34
Most: 10
Yes: 2
Solution: 1
Success(ful): 10
Succeed(ed): 2
Winner, Winning, Wins: 6
Lead: 3
Better, Best: 18
Hope(s), Hopeful: 3

Negative words
Less: 1
Least: 2
But: 47
Can not and Can't: 3
Will not and Won't: 6
No: 18
Problem(s): 2
Worst: 4
Lost: 1

Notable words entirely missing from speech
Liberty
Happiness
Audacity

Transcript of the speech (this is a true transcript, with the audience reactions stripped out. If you want the "transcript" as released by the White House, you can get that here).

Mr. Speaker, Mr. Vice President, members of Congress, distinguished guests, and fellow Americans:

Tonight I want to begin by congratulating the men and women of the 112th Congress, as well as your new Speaker, John Boehner. And as we mark this occasion, we're also mindful of the empty chair in this chamber, and we pray for the health of our colleague — and our friend — Gabby Giffords.

It's no secret that those of us here tonight have had our differences over the last two years. The debates have been contentious; we have fought fiercely for our beliefs. And that's a good thing. That's what a robust democracy demands. That's what helps set us apart as a nation.

But there's a reason the tragedy in Tucson gave us pause. Amid all the noise and passion and rancor of our public debate, Tucson reminded us that no matter who we are or where we come from, each of us is a part of something greater — something more consequential than party or political preference.

We are part of the American family. We believe that in a country where every race and faith and point of view can be found, we are still bound together as one people; that we share common hopes and a common creed; that the dreams of a little girl in Tucson are not so different than those of our own children, and that they all deserve the chance to be fulfilled.

That, too, is what sets us apart as a nation.

Now, by itself, this simple recognition won't usher in a new era of cooperation. What comes of this moment is up to us. What comes of this moment will be determined not by whether we can sit together tonight, but whether we can work together tomorrow.

I believe we can. And I believe we must. That's what the people who sent us here expect of us. With their votes, they've determined that governing will now be a shared responsibility between parties. New laws will only pass with support from Democrats and Republicans. We will move forward together, or not at all — for the challenges we face are bigger than party, and bigger than politics.

At stake right now is not who wins the next election — after all, we just had an election. At stake is whether new jobs and industries take root in this country, or somewhere else. It's whether the hard work and industry of our people is rewarded. It's whether we sustain the leadership that has made America not just a place on a map, but the light to the world.

We are poised for progress. Two years after the worst recession most of us have ever known, the stock market has come roaring back. Corporate profits are up. The economy is growing again.

But we have never measured progress by these yardsticks alone. We measure progress by the success of our people. By the jobs they can find and the quality of life those jobs offer. By the prospects of a small business owner who dreams of turning a good idea into a thriving enterprise. By the opportunities for a better life that we pass on to our children.

That's the project the American people want us to work on. Together.

We did that in December. Thanks to the tax cuts we passed, Americans' paychecks are a little bigger today. Every business can write off the full cost of new investments that they make this year. And these steps, taken by Democrats and Republicans, will grow the economy and add to the more than one million private sector jobs created last year.

But we have to do more. These steps we've taken over the last two years may have broken the back of this recession, but to win the future, we'll need to take on challenges that have been decades in the making.

Many people watching tonight can probably remember a time when finding a good job meant showing up at a nearby factory or a business downtown. You didn't always need a degree, and your competition was pretty much limited to your neighbors. If you worked hard, chances are you'd have a job for life, with a decent paycheck and good benefits and the occasional promotion. Maybe you'd even have the pride of seeing your kids work at the same company.

That world has changed. And for many, the change has been painful. I've seen it in the shuttered windows of once booming factories, and the vacant storefronts on once busy Main Streets. I've heard it in the frustrations of Americans who've seen their paychecks dwindle or their jobs disappear — proud men and women who feel like the rules have been changed in the middle of the game.

They're right. The rules have changed. In a single generation, revolutions in technology have transformed the way we live, work and do business. Steel mills that once needed 1,000 workers can now do the same work with 100. Today, just about any company can set up shop, hire workers, and sell their products wherever there's an Internet connection.

Meanwhile, nations like China and India realized that with some changes of their own, they could compete in this new world. And so they started educating their children earlier and longer, with greater emphasis on math and science. They're investing in research and new technologies. Just recently, China became the home to the world's largest private solar research facility, and the world's fastest computer.

So, yes, the world has changed. The competition for jobs is real. But this shouldn't discourage us. It should challenge us. Remember — for all the hits we've taken these last few years, for all the naysayers predicting our decline, America still has the largest, most prosperous economy in the world. No workers — no workers are more productive than ours. No country has more successful companies, or grants more patents to inventors and entrepreneurs. We're the home to the world's best colleges and universities, where more students come to study than any place on Earth.

What's more, we are the first nation to be founded for the sake of an idea — the idea that each of us deserves the chance to shape our own destiny. That's why centuries of pioneers and immigrants have risked everything to come here. It's why our students don't just memorize equations, but answer questions like "What do you think of that idea? What would you change about the world? What do you want to be when you grow up?"

The future is ours to win. But to get there, we can't just stand still. As Robert Kennedy told us, "The future is not a gift. It is an achievement." Sustaining the American Dream has never been about standing pat. It has required each generation to sacrifice, and struggle, and meet the demands of a new age.

And now it's our turn. We know what it takes to compete for the jobs and industries of our time. We need to out-innovate, out-educate, and out-build the rest of the world. We have to make America the best place on Earth to do business. We need to take responsibility for our deficit and reform our government. That's how our people will prosper. That's how we'll win the future. And tonight, I'd like to talk about how we get there.

The first step in winning the future is encouraging American innovation. None of us can predict with certainty what the next big industry will be or where the new jobs will come from. Thirty years ago, we couldn't know that something called the Internet would lead to an economic revolution. What we can do — what America does better than anyone else — is spark the creativity and imagination of our people. We're the nation that put cars in driveways and computers in offices; the nation of Edison and the Wright brothers; of Google and Facebook. In America, innovation doesn't just change our lives. It is how we make our living.

Our free enterprise system is what drives innovation. But because it's not always profitable for companies to invest in basic research, throughout our history, our government has provided cutting-edge scientists and inventors with the support that they need. That's what planted the seeds for the Internet. That's what helped make possible things like computer chips and GPS. Just think of all the good jobs — from manufacturing to retail — that have come from these breakthroughs.

Half a century ago, when the Soviets beat us into space with the launch of a satellite called Sputnik, we had no idea how we would beat them to the moon. The science wasn't even there yet. NASA didn't exist. But after investing in better research and education, we didn't just surpass the Soviets; we unleashed a wave of innovation that created new industries and millions of new jobs.

This is our generation's Sputnik moment. Two years ago, I said that we needed to reach a level of research and development we haven't seen since the height of the Space Race. And in a few weeks, I will be sending a budget to Congress that helps us meet that goal. We'll invest in biomedical research, information technology, and especially clean energy technology — — an investment that will strengthen our security, protect our planet, and create countless new jobs for our people.

Already, we're seeing the promise of renewable energy. Robert and Gary Allen are brothers who run a small Michigan roofing company. After September 11th, they volunteered their best roofers to help repair the Pentagon. But half of their factory went unused, and the recession hit them hard. Today, with the help of a government loan, that empty space is being used to manufacture solar shingles that are being sold all across the country. In Robert's words, "We reinvented ourselves."

That's what Americans have done for over 200 years: reinvented ourselves. And to spur on more success stories like the Allen Brothers, we've begun to reinvent our energy policy. We're not just handing out money. We're issuing a challenge. We're telling America's scientists and engineers that if they assemble teams of the best minds in their fields, and focus on the hardest problems in clean energy, we'll fund the Apollo projects of our time.

At the California Institute of Technology, they're developing a way to turn sunlight and water into fuel for our cars. At Oak Ridge National Laboratory, they're using supercomputers to get a lot more power out of our nuclear facilities. With more research and incentives, we can break our dependence on oil with biofuels, and become the first country to have a million electric vehicles on the road by 2015.

We need to get behind this innovation. And to help pay for it, I'm asking Congress to eliminate the billions in taxpayer dollars we currently give to oil companies. I don't know if — I don't know if you've noticed, but they're doing just fine on their own. So instead of subsidizing yesterday's energy, let's invest in tomorrow's.

Now, clean energy breakthroughs will only translate into clean energy jobs if businesses know there will be a market for what they're selling. So tonight, I challenge you to join me in setting a new goal: By 2035, 80 percent of America's electricity will come from clean energy sources.

Some folks want wind and solar. Others want nuclear, clean coal and natural gas. To meet this goal, we will need them all — and I urge Democrats and Republicans to work together to make it happen.

Maintaining our leadership in research and technology is crucial to America's success. But if we want to win the future — if we want innovation to produce jobs in America and not overseas — then we also have to win the race to educate our kids.

Think about it. Over the next 10 years, nearly half of all new jobs will require education that goes beyond a high school education. And yet, as many as a quarter of our students aren't even finishing high school. The quality of our math and science education lags behind many other nations. America has fallen to ninth in the proportion of young people with a college degree. And so the question is whether all of us — as citizens, and as parents — are willing to do what's necessary to give every child a chance to succeed.

That responsibility begins not in our classrooms, but in our homes and communities. It's family that first instills the love of learning in a child. Only parents can make sure the TV is turned off and homework gets done. We need to teach our kids that it's not just the winner of the Super Bowl who deserves to be celebrated, but the winner of the science fair. We need to teach them that success is not a function of fame or PR, but of hard work and discipline.

Our schools share this responsibility. When a child walks into a classroom, it should be a place of high expectations and high performance. But too many schools don't meet this test. That's why instead of just pouring money into a system that's not working, we launched a competition called Race to the Top. To all 50 states, we said, "If you show us the most innovative plans to improve teacher quality and student achievement, we'll show you the money."

Race to the Top is the most meaningful reform of our public schools in a generation. For less than 1 percent of what we spend on education each year, it has led over 40 states to raise their standards for teaching and learning. And these standards were developed, by the way, not by Washington, but by Republican and Democratic governors throughout the country. And Race to the Top should be the approach we follow this year as we replace No Child Left Behind with a law that's more flexible and focused on what's best for our kids.

You see, we know what's possible from our children when reform isn't just a top-down mandate, but the work of local teachers and principals, school boards and communities. Take a school like Bruce Randolph in Denver. Three years ago, it was rated one of the worst schools in Colorado — located on turf between two rival gangs. But last May, 97 percent of the seniors received their diploma. Most will be the first in their families to go to college. And after the first year of the school's transformation, the principal who made it possible wiped away tears when a student said, "Thank you, Ms. Waters, for showing that we are smart and we can make it." That's what good schools can do, and we want good schools all across the country.

Let's also remember that after parents, the biggest impact on a child's success comes from the man or woman at the front of the classroom. In South Korea, teachers are known as "nation builders." Here in America, it's time we treated the people who educate our children with the same level of respect. We want to reward good teachers and stop making excuses for bad ones. And over the next 10 years, with so many baby boomers retiring from our classrooms, we want to prepare 100,000 new teachers in the fields of science and technology and engineering and math.

In fact, to every young person listening tonight who's contemplating their career choice: If you want to make a difference in the life of our nation; if you want to make a difference in the life of a child — become a teacher. Your country needs you.

Of course, the education race doesn't end with a high school diploma. To compete, higher education must be within the reach of every American. That's why we've ended the unwarranted taxpayer subsidies that went to banks, and used the savings to make college affordable for millions of students. And this year, I ask Congress to go further, and make permanent our tuition tax credit — worth $10,000 for four years of college. It's the right thing to do.

Because people need to be able to train for new jobs and careers in today's fast-changing economy, we're also revitalizing America's community colleges. Last month, I saw the promise of these schools at Forsyth Tech in North Carolina. Many of the students there used to work in the surrounding factories that have since left town. One mother of two, a woman named Kathy Proctor, had worked in the furniture industry since she was 18 years old. And she told me she's earning her degree in biotechnology now, at 55 years old, not just because the furniture jobs are gone, but because she wants to inspire her children to pursue their dreams, too. As Kathy said, "I hope it tells them to never give up."

If we take these steps — if we raise expectations for every child, and give them the best possible chance at an education, from the day they are born until the last job they take — we will reach the goal that I set two years ago: By the end of the decade, America will once again have the highest proportion of college graduates in the world.

One last point about education. Today, there are hundreds of thousands of students excelling in our schools who are not American citizens. Some are the children of undocumented workers, who had nothing to do with the actions of their parents. They grew up as Americans and pledge allegiance to our flag, and yet they live every day with the threat of deportation. Others come here from abroad to study in our colleges and universities. But as soon as they obtain advanced degrees, we send them back home to compete against us. It makes no sense.

Now, I strongly believe that we should take on, once and for all, the issue of illegal immigration. And I am prepared to work with Republicans and Democrats to protect our borders, enforce our laws and address the millions of undocumented workers who are now living in the shadows. I know that debate will be difficult. I know it will take time. But tonight, let's agree to make that effort. And let's stop expelling talented, responsible young people who could be staffing our research labs or starting a new business, who could be further enriching this nation.

The third step in winning the future is rebuilding America. To attract new businesses to our shores, we need the fastest, most reliable ways to move people, goods, and information — from high-speed rail to high-speed Internet.

Our infrastructure used to be the best, but our lead has slipped. South Korean homes now have greater Internet access than we do. Countries in Europe and Russia invest more in their roads and railways than we do. China is building faster trains and newer airports. Meanwhile, when our own engineers graded our nation's infrastructure, they gave us a "D."

We have to do better. America is the nation that built the transcontinental railroad, brought electricity to rural communities, constructed the Interstate Highway System. The jobs created by these projects didn't just come from laying down track or pavement. They came from businesses that opened near a town's new train station or the new off-ramp.

So over the last two years, we've begun rebuilding for the 21st century, a project that has meant thousands of good jobs for the hard-hit construction industry. And tonight, I'm proposing that we redouble those efforts.

We'll put more Americans to work repairing crumbling roads and bridges. We'll make sure this is fully paid for, attract private investment, and pick projects based [on] what's best for the economy, not politicians.

Within 25 years, our goal is to give 80 percent of Americans access to high-speed rail. This could allow you to go places in half the time it takes to travel by car. For some trips, it will be faster than flying — without the pat-down. As we speak, routes in California and the Midwest are already underway.

Within the next five years, we'll make it possible for businesses to deploy the next generation of high-speed wireless coverage to 98 percent of all Americans. This isn't just about — — this isn't about faster Internet or fewer dropped calls. It's about connecting every part of America to the digital age. It's about a rural community in Iowa or Alabama where farmers and small business owners will be able to sell their products all over the world. It's about a firefighter who can download the design of a burning building onto a handheld device; a student who can take classes with a digital textbook; or a patient who can have face-to-face video chats with her doctor.

All these investments — in innovation, education, and infrastructure — will make America a better place to do business and create jobs. But to help our companies compete, we also have to knock down barriers that stand in the way of their success.

For example, over the years, a parade of lobbyists has rigged the tax code to benefit particular companies and industries. Those with accountants or lawyers to work the system can end up paying no taxes at all. But all the rest are hit with one of the highest corporate tax rates in the world. It makes no sense, and it has to change.

So tonight, I'm asking Democrats and Republicans to simplify the system. Get rid of the loopholes. Level the playing field. And use the savings to lower the corporate tax rate for the first time in 25 years — without adding to our deficit. It can be done.

To help businesses sell more products abroad, we set a goal of doubling our exports by 2014 — because the more we export, the more jobs we create here at home. Already, our exports are up. Recently, we signed agreements with India and China that will support more than 250,000 jobs here in the United States. And last month, we finalized a trade agreement with South Korea that will support at least 70,000 American jobs. This agreement has unprecedented support from business and labor, Democrats and Republicans — and I ask this Congress to pass it as soon as possible.

Now, before I took office, I made it clear that we would enforce our trade agreements, and that I would only sign deals that keep faith with American workers and promote American jobs. That's what we did with Korea, and that's what I intend to do as we pursue agreements with Panama and Colombia and continue our Asia Pacific and global trade talks.

To reduce barriers to growth and investment, I've ordered a review of government regulations. When we find rules that put an unnecessary burden on businesses, we will fix them. But I will not hesitate to create or enforce common-sense safeguards to protect the American people. That's what we've done in this country for more than a century. It's why our food is safe to eat, our water is safe to drink, and our air is safe to breathe. It's why we have speed limits and child labor laws. It's why last year, we put in place consumer protections against hidden fees and penalties by credit card companies and new rules to prevent another financial crisis. And it's why we passed reform that finally prevents the health insurance industry from exploiting patients.

Now, I have heard rumors that a few of you still have concerns about our new health care law. So let me be the first to say that anything can be improved. If you have ideas about how to improve this law by making care better or more affordable, I am eager to work with you. We can start right now by correcting a flaw in the legislation that has placed an unnecessary bookkeeping burden on small businesses.

What I'm not willing to do — what I'm not willing to do is go back to the days when insurance companies could deny someone coverage because of a preexisting condition.

I'm not willing to tell James Howard, a brain cancer patient from Texas, that his treatment might not be covered. I'm not willing to tell Jim Houser, a small business man from Oregon, that he has to go back to paying $5,000 more to cover his employees. As we speak, this law is making prescription drugs cheaper for seniors and giving uninsured students a chance to stay on their patients' — parents' coverage.

So I say to this chamber tonight, instead of re-fighting the battles of the last two years, let's fix what needs fixing and let's move forward.

Now, the final critical step in winning the future is to make sure we aren't buried under a mountain of debt.

We are living with a legacy of deficit spending that began almost a decade ago. And in the wake of the financial crisis, some of that was necessary to keep credit flowing, save jobs, and put money in people's pockets.

But now that the worst of the recession is over, we have to confront the fact that our government spends more than it takes in. That is not sustainable. Every day, families sacrifice to live within their means. They deserve a government that does the same.

So tonight, I am proposing that starting this year, we freeze annual domestic spending for the next five years. Now, this would reduce the deficit by more than $400 billion over the next decade, and will bring discretionary spending to the lowest share of our economy since Dwight Eisenhower was President.

This freeze will require painful cuts. Already, we've frozen the salaries of hardworking federal employees for the next two years. I've proposed cuts to things I care deeply about, like community action programs. The Secretary of Defense has also agreed to cut tens of billions of dollars in spending that he and his generals believe our military can do without.

I recognize that some in this chamber have already proposed deeper cuts, and I'm willing to eliminate whatever we can honestly afford to do without. But let's make sure that we're not doing it on the backs of our most vulnerable citizens. And let's make sure that what we're cutting is really excess weight. Cutting the deficit by gutting our investments in innovation and education is like lightening an overloaded airplane by removing its engine. It may make you feel like you're flying high at first, but it won't take long before you feel the impact.

Now, most of the cuts and savings I've proposed only address annual domestic spending, which represents a little more than 12 percent of our budget. To make further progress, we have to stop pretending that cutting this kind of spending alone will be enough. It won't.

The bipartisan fiscal commission I created last year made this crystal clear. I don't agree with all their proposals, but they made important progress. And their conclusion is that the only way to tackle our deficit is to cut excessive spending wherever we find it — in domestic spending, defense spending, health care spending, and spending through tax breaks and loopholes.

This means further reducing health care costs, including programs like Medicare and Medicaid, which are the single biggest contributor to our long-term deficit. The health insurance law we passed last year will slow these rising costs, which is part of the reason that nonpartisan economists have said that repealing the health care law would add a quarter of a trillion dollars to our deficit. Still, I'm willing to look at other ideas to bring down costs, including one that Republicans suggested last year — medical malpractice reform to rein in frivolous lawsuits.

To put us on solid ground, we should also find a bipartisan solution to strengthen Social Security for future generations. We must do it without putting at risk current retirees, the most vulnerable, or people with disabilities; without slashing benefits for future generations; and without subjecting Americans' guaranteed retirement income to the whims of the stock market.

And if we truly care about our deficit, we simply can't afford a permanent extension of the tax cuts for the wealthiest 2 percent of Americans. Before we take money away from our schools or scholarships away from our students, we should ask millionaires to give up their tax break. It's not a matter of punishing their success. It's about promoting America's success.

In fact, the best thing we could do on taxes for all Americans is to simplify the individual tax code. This will be a tough job, but members of both parties have expressed an interest in doing this, and I am prepared to join them.

So now is the time to act. Now is the time for both sides and both houses of Congress — Democrats and Republicans — to forge a principled compromise that gets the job done. If we make the hard choices now to rein in our deficits, we can make the investments we need to win the future.

Let me take this one step further. We shouldn't just give our people a government that's more affordable. We should give them a government that's more competent and more efficient. We can't win the future with a government of the past.

We live and do business in the Information Age, but the last major reorganization of the government happened in the age of black-and-white TV. There are 12 different agencies that deal with exports. There are at least five different agencies that deal with housing policy. Then there's my favorite example: The Interior Department is in charge of salmon while they're in fresh water, but the Commerce Department handles them when they're in saltwater. I hear it gets even more complicated once they're smoked.

Now, we've made great strides over the last two years in using technology and getting rid of waste. Veterans can now download their electronic medical records with a click of the mouse. We're selling acres of federal office space that hasn't been used in years, and we'll cut through red tape to get rid of more. But we need to think bigger. In the coming months, my administration will develop a proposal to merge, consolidate, and reorganize the federal government in a way that best serves the goal of a more competitive America. I will submit that proposal to Congress for a vote — and we will push to get it passed.

In the coming year, we'll also work to rebuild people's faith in the institution of government. Because you deserve to know exactly how and where your tax dollars are being spent, you'll be able to go to a website and get that information for the very first time in history. Because you deserve to know when your elected officials are meeting with lobbyists, I ask Congress to do what the White House has already done — put that information online. And because the American people deserve to know that special interests aren't larding up legislation with pet projects, both parties in Congress should know this: If a bill comes to my desk with earmarks inside, I will veto it. I will veto it.

The 21st century government that's open and competent. A government that lives within its means. An economy that's driven by new skills and new ideas. Our success in this new and changing world will require reform, responsibility, and innovation. It will also require us to approach that world with a new level of engagement in our foreign affairs.

Just as jobs and businesses can now race across borders, so can new threats and new challenges. No single wall separates East and West. No one rival superpower is aligned against us.

And so we must defeat determined enemies, wherever they are, and build coalitions that cut across lines of region and race and religion. And America's moral example must always shine for all who yearn for freedom and justice and dignity. And because we've begun this work, tonight we can say that American leadership has been renewed and America's standing has been restored.

Look to Iraq, where nearly 100,000 of our brave men and women have left with their heads held high. American combat patrols have ended, violence is down, and a new government has been formed. This year, our civilians will forge a lasting partnership with the Iraqi people, while we finish the job of bringing our troops out of Iraq. America's commitment has been kept. The Iraq war is coming to an end.

Of course, as we speak, al Qaeda and their affiliates continue to plan attacks against us. Thanks to our intelligence and law enforcement professionals, we're disrupting plots and securing our cities and skies. And as extremists try to inspire acts of violence within our borders, we are responding with the strength of our communities, with respect for the rule of law, and with the conviction that American Muslims are a part of our American family.

We've also taken the fight to al Qaeda and their allies abroad. In Afghanistan, our troops have taken Taliban strongholds and trained Afghan security forces. Our purpose is clear: By preventing the Taliban from reestablishing a stranglehold over the Afghan people, we will deny al Qaeda the safe haven that served as a launching pad for 9/11.

Thanks to our heroic troops and civilians, fewer Afghans are under the control of the insurgency. There will be tough fighting ahead, and the Afghan government will need to deliver better governance. But we are strengthening the capacity of the Afghan people and building an enduring partnership with them. This year, we will work with nearly 50 countries to begin a transition to an Afghan lead. And this July, we will begin to bring our troops home.

In Pakistan, al Qaeda's leadership is under more pressure than at any point since 2001. Their leaders and operatives are being removed from the battlefield. Their safe havens are shrinking. And we've sent a message from the Afghan border to the Arabian Peninsula to all parts of the globe: We will not relent, we will not waver, and we will defeat you.

American leadership can also be seen in the effort to secure the worst weapons of war. Because Republicans and Democrats approved the New START treaty, far fewer nuclear weapons and launchers will be deployed. Because we rallied the world, nuclear materials are being locked down on every continent so they never fall into the hands of terrorists.

Because of a diplomatic effort to insist that Iran meet its obligations, the Iranian government now faces tougher sanctions, tighter sanctions than ever before. And on the Korean Peninsula, we stand with our ally South Korea, and insist that North Korea keeps its commitment to abandon nuclear weapons.

This is just a part of how we're shaping a world that favors peace and prosperity. With our European allies, we revitalized NATO and increased our cooperation on everything from counterterrorism to missile defense. We've reset our relationship with Russia, strengthened Asian alliances, built new partnerships with nations like India.

This March, I will travel to Brazil, Chile, and El Salvador to forge new alliances across the Americas. Around the globe, we're standing with those who take responsibility — helping farmers grow more food, supporting doctors who care for the sick, and combating the corruption that can rot a society and rob people of opportunity.

Recent events have shown us that what sets us apart must not just be our power — it must also be the purpose behind it. In south Sudan — with our assistance — the people were finally able to vote for independence after years of war. Thousands lined up before dawn. People danced in the streets. One man who lost four of his brothers at war summed up the scene around him: "This was a battlefield for most of my life," he said. "Now we want to be free."

And we saw that same desire to be free in Tunisia, where the will of the people proved more powerful than the writ of a dictator. And tonight, let us be clear: The United States of America stands with the people of Tunisia, and supports the democratic aspirations of all people.

We must never forget that the things we've struggled for, and fought for, live in the hearts of people everywhere. And we must always remember that the Americans who have borne the greatest burden in this struggle are the men and women who serve our country.

Tonight, let us speak with one voice in reaffirming that our nation is united in support of our troops and their families. Let us serve them as well as they've served us — by giving them the equipment they need, by providing them with the care and benefits that they have earned, and by enlisting our veterans in the great task of building our own nation.

Our troops come from every corner of this country — they're black, white, Latino, Asian, Native American. They are Christian and Hindu, Jewish and Muslim. And, yes, we know that some of them are gay. Starting this year, no American will be forbidden from serving the country they love because of who they love. And with that change, I call on all our college campuses to open their doors to our military recruiters and ROTC. It is time to leave behind the divisive battles of the past. It is time to move forward as one nation.

We should have no illusions about the work ahead of us. Reforming our schools, changing the way we use energy, reducing our deficit — none of this will be easy. All of it will take time. And it will be harder because we will argue about everything. The costs. The details. The letter of every law.

Of course, some countries don't have this problem. If the central government wants a railroad, they build a railroad, no matter how many homes get bulldozed. If they don't want a bad story in the newspaper, it doesn't get written.

And yet, as contentious and frustrating and messy as our democracy can sometimes be, I know there isn't a person here who would trade places with any other nation on Earth.

We may have differences in policy, but we all believe in the rights enshrined in our Constitution. We may have different opinions, but we believe in the same promise that says this is a place where you can make it if you try. We may have different backgrounds, but we believe in the same dream that says this is a country where anything is possible. No matter who you are. No matter where you come from.

That dream is why I can stand here before you tonight. That dream is why a working-class kid from Scranton can sit behind me. That dream is why someone who began by sweeping the floors of his father's Cincinnati bar can preside as Speaker of the House in the greatest nation on Earth.

That dream — that American Dream — is what drove the Allen Brothers to reinvent their roofing company for a new era. It's what drove those students at Forsyth Tech to learn a new skill and work towards the future. And that dream is the story of a small business owner named Brandon Fisher.

Brandon started a company in Berlin, Pennsylvania, that specializes in a new kind of drilling technology. And one day last summer, he saw the news that halfway across the world, 33 men were trapped in a Chilean mine, and no one knew how to save them.

But Brandon thought his company could help. And so he designed a rescue that would come to be known as Plan B. His employees worked around the clock to manufacture the necessary drilling equipment. And Brandon left for Chile.

Along with others, he began drilling a 2,000-foot hole into the ground, working three- or four-hour — three or four days at a time without any sleep. Thirty-seven days later, Plan B succeeded, and the miners were rescued. But because he didn't want all of the attention, Brandon wasn't there when the miners emerged. He'd already gone back home, back to work on his next project.

And later, one of his employees said of the rescue, "We proved that Center Rock is a little company, but we do big things."

We do big things.

From the earliest days of our founding, America has been the story of ordinary people who dare to dream. That's how we win the future.

We're a nation that says, "I might not have a lot of money, but I have this great idea for a new company." "I might not come from a family of college graduates, but I will be the first to get my degree." "I might not know those people in trouble, but I think I can help them, and I need to try." "I'm not sure how we'll reach that better place beyond the horizon, but I know we'll get there. I know we will."

We do big things.

The idea of America endures. Our destiny remains our choice. And tonight, more than two centuries later, it's because of our people that our future is hopeful, our journey goes forward, and the state of our union is strong.

Thank you. God bless you, and may God bless the United States of America.

Friday, January 21, 2011

Microsoft Access Tips & Tricks: Flattening A Table

OK, I admit, the title is a little cryptic and esoteric. What exactly does “flattening a table” really mean? In the context of this post, it is going to mean a process by which we are going to reduce the number of rows in the table while increasing the width. Hence the comparison to flattening something. The problem has been referred to using other, more wordier descriptions such as concatenating column values from multiple rows into a single column, concatenating values from within the same column, grouping with concatenation, aggregate function for concatenation, etc. I will give a concrete example of what I plan on doing in just a bit.

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, and propercasing text entries.

As promised, let me start with a concrete example of what I mean by “flattening a table”. Then we will look at how exactly to do it. The example I am going to pick up has to do with the the database I use for documenting and cataloging my large collection of electronic books. As you are aware, many books have multiple authors. I have an authors table that has three fields: a book ID field (numerical), an author field (text) and an author number field (numerical). Let us say this table has the following entries:

Book ID Author Name Author Number
1 A 1
1 B 2
2 C 1
2 D 2
2 E 3
2 F 4
3 G 1

What this table contains is details about three books. Their bookID’s are 1, 2 and 3. The book with bookID of 1 has 2 authors: A and B. A is the first author, and B is the second author. The book with bookID of 2 has 4 authors (C, D, E, and F). And the book with bookID of 3 has just one author (G).

Note that this table structure is considered the correct table structure for storing details such as this. It is normalized correctly rather than using repeating columns such as Author1, Author2, Author3, etc. If you have table structure like that, it is wrong and should be fixed. In addition to many other disadvantages, it makes it very difficult to deal with books that have more authors than the number of columns you have guessed at during table design as being adequate. Also, think about the difficulty of finding out all books with a particular author when you have a denormalized table structure!

Anyways, even though the table structure above is correct, normalized and all that good stuff, we still have a minor problem: if we want to see all the authors of a book on one line for any reason (maybe we want to show such a list in a report or form), then it becomes a little tricky. In essence, we need a query to produce output like the one below, if possible:

Book ID Author Names
1 A, B
2 C, D, E, F
3 G

This is what we mean by “flattening a table”. We have taken a table with 7 rows, and created a table with 3 rows out of it. And we have made the second column of the new table a multi-value column (once again, note that this is not a normalized table structure. You may create something like this for the express purpose of feeding a form or report, but you should not store data permanently in your database with multi-valued columns. Every field in a database table should contain atomic data). You can also call this “denormalizing a table”.

The question is how to go from the first table to the second table. Most people will tell you that this can not be done using SQL. They will insist that this has to be done only using VBA. The truth of the matter is, SQL is a very versatile language, and it is difficult to think of something that can not be done using SQL if you apply your mind to it hard enough! And no, that is not an open invitation to challenge me to create a linux kernel using SQL!! So, in this post, I am going to talk about a purely SQL solution to this problem. I will then talk about the disadvantages of the pure SQL approach to the problem. These disadvantages may make it better to use a little VBA to tackle the problem. I will explain the VBA solution to the problem in my next post. But for now, let us focus on the SQL solution.

Note that the second table is tantalizingly similar to the output from a Group By query. We group by Book ID, and instead of using a normal aggregate function like sum(), avg(), etc., we use a concatenate() function. Unfortunately, for some reason known only to the designers and implementers of SQL, the language does not have an aggregate function that performs concatenation. In fact, if such a function existed, the problem would be trivial, and I wouldn’t be writing about it here!

So, in the absence of such an aggregate function, how are we to write SQL to create the second table from the first? The answer lies in a series of self-joins. Self-joins are just like joins between two tables, only the two tables are the same. A table is joined with itself to create a self-join. Let us see how to use a series of self-joins to create a flattened table.

Consider the SQL below:

select myTable.BookID, myTable.AuthorName & ", " & mytable1.AuthorName as Authors
From myTable inner join myTable as myTable1 on myTable.BookID = myTable1.BookID
This is a self-join, because myTable is joined with itself (it is given an alias during the join so that we can refer to the fields in the two tables unambiguously). This gives us the output below:

BookIDAuthors
1A, A
1A, B
1B, A
1B, B
2C, C
2C, D
2C, E
2C, F
2D, C
2D, D
2D, E
2 D, F
2 E, C
2 E, D
2 E, E,
2 E, F
2 F, C
2 F, D
2 F, E
2 F, F
3 G, G

Obviously, not exactly what we want, but we are making progress. The first task is to eliminate duplicates like (A, A) from the Authors column. In fact, we should also eliminate multiple permutations of the same combination such (A, B) and (B, A). The simplest way to do this is to change the join condition to eliminate such rows from the join. Take a look at the SQL below:

select myTable.BookID, myTable.AuthorName & ", " & mytable1.AuthorName as Authors
From myTable inner join myTable as myTable1 on myTable.BookID = myTable1.BookID
AND myTable.AuthorName < myTable1.AuthorName
Here we use the AuthorName field to eliminate duplicates, and also to order the resulting combination in increasing order of AuthorName (thus an alphabetical listing of authors). We can create a list of authors by AuthorNumber (which might make more sense in this application), but it will be a little more involved. Why it will be more involved will become apparent as you read through the rest of this post.

Let us look at the output from the above query.

BookID Authors
1 A, B
2 C, D
2 C, E
2 C, F
2 D, E
2 D, F
2 E, F

This is a lot of progress! But we lost the row corresponding to BookID 3 because that book has only one author. The self-join fails for that BookID because it can not find another row in the table with the same BookID and an author name greater than G. The simplest way to fix this is to make the inner joins left joins. Take a look at the SQL below:

select myTable.BookID, myTable.AuthorName & ", " & mytable1.AuthorName as Authors
From myTable LEFT join myTable as myTable1 on myTable.BookID = myTable1.BookID
AND myTable.AuthorName < myTable1.AuthorName
The output from the query above is shown in the table below:

BookID Authors
1 A, B
1 B,
2 C, D
2 C, E
2 C, F
2 D, E
2 D, F
2 E, F
2 F,
3 G,

Because of the left join, in addition to the combinations, we also get the last author of each book on a separate row since the left join of that row in the original table fails to find a suitable row in the table to join with, resulting in that row being produced by itself. We are still making progress whether you believe it or not!

Actually, the results of the query above contain the results we are looking for (in addition to extraneous rows which we need to get rid of). Obviously, our SQL can only create combinations of at most two authors for each book because we have only one self-join in our SQL. But first, let us eliminate the extra rows in the above query, and then see how to get all the authors for books like Book 2, which have 4 authors.

Eliminating the unwanted rows is simply a matter of wrapping the query that generated the results above in an outer query that groups on BookID and takes the minimum of the Authors field (because we used a “<” comparison in our join condition, we take the minimum. If we had used a “>” comparison in our join condition, we would take the maximum). And using authorNumber instead of authorName in the inner query can create complications at this step because you can not tell in advance whether to take the minimum or maximum of authors in the outer query. That is why I have made a compromise and satisfied myself with getting an alphabetical listing of authors. The SQL below does exactly that:

select BookID, min(authors) From
(select myTable.BookID, myTable.AuthorName & ", " & mytable1.AuthorName as Authors
From myTable left join myTable as myTable1 on myTable.BookID = myTable1.BookID
AND myTable.AuthorName < myTable1.AuthorName)
group by BookID
Now, we get the output below:

BookID Authors
1 A, B
2 C, D
3 G,

Now, it is just a matter of getting the full list of authors for each book, rather than just the first two. Here is where the disadvantages of the pure SQL approach start to show themselves. You have to decide up front the maximum number of authors you are going to show for any book on your list. If you are satisfied with showing 5 authors, and think that the loss of information for books with more than 5 authors (which are extremely rare) is tolerable, you would just do 4 self-joins instead of the 1 self-join we have done so far. Remember that the number of self-joins will be one less than the maximum number of authors you can get in your output.

So, the final SQL for flattening the given table, and giving you up to 5 authors per book in the output is presented below:
select BookID, min(authors) from
(select myTable.BookID, myTable.AuthorName & ", " & mytable1.AuthorName & ", " &
myTable2.AuthorName & ", " & myTable3.AuthorName & ", " & myTable4.AuthorName as Authors
From ((((myTable left join myTable as myTable1 on myTable.BookID = myTable1.BookID
AND myTable.AuthorName < myTable1.AuthorName)
left join myTable as myTable2 on myTable1.BookID = myTable2.BookID AND myTable1.AuthorName < myTable2.AuthorName)
left join myTable as myTable3 on myTable2.BookID = myTable3.BookID AND myTable2.AuthorName < myTable3.AuthorName)
left join myTable as myTable4 on myTable3.BookID = myTable4.BookID AND myTable3.AuthorName < myTable4.AuthorName))
group by BookID
The output from the above SQL is presented below:

BookID Authors
1 A, B, , ,
2 C, D, E, F,
3 G, , , ,

Notice that there are extra commas in the output because of the way we concatenated the author names. If we omitted the commas between the names in the concatenation, we would still end up with extra spaces, but they would be less noticeable in the output.

So, you can flatten a table using pure SQL. But there are several problems with the approach that might preclude its use in your application:
  • You may not be able to tell in advance how many values you want to concatenate in the final output for any given row
  • You may not be able to live with any loss of information (in the example above, we stopped at 4 left joins with the full knowledge that if a book had more than 5 authors, we would see only the first 5 in the output)
  • If you use a separator except spaces during the concatenation, extra separators will appear in the output for rows which have fewer values to concatenate
  • If your concatenation is of one column, but you want to use a different column in the table to determine the order in which the values need to be concatenated, it gets complicated (that is why I did not tackle the problem of ordering the authors by author number in this post)
  • If your table is very large, the self-joins create more and more rows exponentially. You could run out of memory and other resources when you run the subquery or when you run the outer query to group by BookID
So, take this solution as a proof of concept rather than something that will work for you all the time. This proof of concept proves that SQL is a lot more powerful than many naysayers would have you believe! However, many production databases contain millions of rows, and a series of self-joins like this can make your database administrator very unhappy and/or angry with you! In the next post in this series, I will go into solution of this problem using a few lines of VBA code.

Hope this post has been helpful in solving any problems you might have had with flattening a table in Access. If you have any problems or concerns, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future posts, please feel free to let me know through your comments too. Good luck!

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