Search The Web

Today's Headlines

Wednesday, March 31, 2010

Vedic Mathematics Lesson 46: Square Roots 1

A square root of a number x is a number r such that r^2 = x, or, in other words, a number r whose square (the result of multiplying the number by itself) is x. Every non-negative real number x has a unique non-negative square root, called the principal square root. In addition to using the radical sign (√), one can also denote the square root of x in exponential form as x^(1/2). The negative of the principal square root of a number is also a square root of the given number. Thus, any given positive number has two square roots, the principal square root, and the negative of the principal square root.

You can find all my previous posts about Vedic Mathematics below:

Introduction to Vedic Mathematics
A Spectacular Illustration of Vedic Mathematics
10's Complements
Multiplication Part 1
Multiplication Part 2
Multiplication Part 3
Multiplication Part 4
Multiplication Part 5
Multiplication Special Case 1
Multiplication Special Case 2
Multiplication Special Case 3
Vertically And Crosswise I
Vertically And Crosswise II
Squaring, Cubing, Etc.
Subtraction
Division By The Nikhilam Method I
Division By The Nikhilam Method II
Division By The Nikhilam Method III
Division By The Paravartya Method
Digital Roots
Straight Division I
Straight Division II
Vinculums
Divisibility Rules
Simple Osculation
Multiplex Osculation
Solving Equations 1
Solving Equations 2
Solving Equations 3
Solving Equations 4
Mergers 1
Mergers 2
Mergers 3
Multiple Mergers
Complex Mergers
Simultaneous Equations 1
Simultaneous Equations 2
Quadratic Equations 1
Quadratic Equations 2
Quadratic Equations 3
Quadratic Equations 4
Cubic Equations
Quartic Equations
Polynomial Division 1
Polynomial Division 2
Polynomial Division 3

Before we delve into the arithmetic of calculating square roots, we make the following observations regarding square roots:

  • If a number consists of n digits before the decimal point, where n is even, then its square root will contain n/2 digits before the decimal point. Thus, if the number has 6 digits before the decimal point, its square root will contain 3 digits before the decimal point
  • If a number consists of n digits before the decimal point, where n is odd, then its square root will contain (n+1)/2 digits before the decimal point. Thus, if the number has 3 digits before the decimal point, its square root will contain 2 digits before the decimal point
  • If a whole number is not an exact square of another whole number, its square root is always irrational (that is, the decimal expression of the square root of the number will contain an infinite number of digits after the decimal point, and there will be no repeating pattern in the digits after the decimal point)
  • If a number with n digits after the decimal point is squared, the resulting answer will contain 2n digits after the decimal point. Thus, if a number contains 2n digits after the decimal point, and is the result of squaring a rational number, its square root will contain n digits after the decimal point
  • If a number contains n digits after the decimal point, where n is odd, then it can not be the result of squaring a rational number. Therefore, the square root of such a number will always be an irrational number
  • An exact square never ends in 2, 3, 7 or 8
  • If a number ends in 2, 3, 7 or 8, its square root will always be an irrational number
  • If an exact square ends in 1, its square root ends in 1 or 9
  • If an exact square ends in 4, its square root ends in 2 or 8
  • If an exact square ends in 5, its square root ends in 5
  • If an exact square ends in 6, its square root ends in 4 or 6
  • If an exact square ends in 9, its square root ends in 3 or 7
  • Notice in the above observations, how the ending digits of the square root are 10's complements of each other (thus 1 and 9 are 10's complements of each other, 2 and 8 are 10's complements of each other, and so on)
  • If a perfect square is an odd number, the square root is also an odd number
  • If a perfect square is an even number, the square root is also an even number
  • A whole number that ends with an odd number of 0's is never the square of a whole number
  • An exact square never ends in a 6 if the penultimate digit is even (thus, exact squares can not end in 26, 46, 86, etc.)
  • An exact square never has an odd penultimate digit unless the final digit is a 6 (thus, exact squares can not end in 39, 71, etc.)
  • An exact square never ends with an even number when the last two digits taken together are not divisible by 4 (thus, no exact square can end in 22, 34, and other non-multiples of 4 if the last digit is even)

That is quite a lot of observations there!

There are lots of different ways to find the square root of a given number. None of them are easy or trivial. In most cases, they require at least some messy calculations. The vedic method of finding a square root is called the Vedic Duplex method. As the name implies, the method involves a concept called the duplex of a number. So, we will start with explaining what the duplex is first.

The duplex of a number is also called the Dvandva Yoga of a number. The duplex of a number is calculated as below:

  • For a single digit number, the duplex is simply the square of the number. Thus the duplex of 2 is 4, the duplex of 6 is 36 and so on
  • For a 2-digit number, the duplex is simply twice the product of the 2 digits of the number. Thus, the duplex of 16 is 2x1x6 = 12, the duplex of 90 is 2x9x0 = 0, the duplex of 43 is 2x4x3 = 24, and so on
  • For n-digit numbers, the duplex is calculated as the sum of several individual duplexes. Pair up the first digit with the nth digit of the number and find the duplex of the resulting 2-digit number. Similarly pair up the second digit with the (n-1)th digit and find the duplex of the resulting 2-digit number. Continue this process until no more 2-digit pairs can be formed. If a middle digit (that could not be paired with anything else) exists at the end of the process, find its duplex also individually. Then add up all the duplexes found. The resulting sum of the duplex of the n-digit number

Some duplexes are shown below for illustration and to make sure the calculation of the duplex is fully understood.

2: 2^2 = 4
5: 5^2 = 25
48: 2x4x8 = 64
91: 2x9x1 = 18
314: 2x3x4 + 1^2 = 25
725: 2x7x5 + 2^2 = 74
350: 2x3x0 + 5^2 = 25
4466: 2x4x6 + 2x4x6 = 96
1398: 2x1x8 + 2x3x9 = 70
9357: 2x9x7 + 2x3x5 = 156
35832: 2x3x2 + 2x5x3 + 8^2 = 106
62787: 2x6x7 + 2x2x8 + 7^2 = 165
8947548937: 2x8x7 + 2x9x3 + 2x4x9 + 2x7x8 + 2x5x4 = 112 + 54 + 72 + 112 + 40 = 390
84738932833: 2x8x3 + 2x4x3 + 2x7x8 + 2x3x2 + 2x8x3 + 9^2 = 48 + 24 + 112 + 12 + 48 + 81 = 325

Notice the following facts about duplexes:

  • The duplex of a number is the same when the number is reversed from front to back
  • The duplex of an n-digit number is always even when n is even
  • The duplex of an n-digit number is odd when n is odd, and the middle digit of the number is odd

Now that we know how to calculate the duplex of a number, how do we use it in calculating the square root of a number? We will explain using a simple example. Let us take the number 144, for instance.

Now, almost everyone knows, without any need for calculations, that the square root of 144 is 12. But, let us try to compute that using the vedic duplex method. The method we use can be extended to numbers much larger than 144.

The first step to doing this is to count the number of digits in the given number. If the number of digits is odd, separate the first digit from the rest of the digits. If the number of digits is even, separate the first two digits from the rest of the digits. In the case of 144, the number contains 3 digits, which is an odd number of digits. Therefore, we separate the first digit, 1, from the rest of the digits. We then create an initial figure that looks like the below:
•|1: 4 4
•|______
•|_:____

We have separated the 1 from the rest of the digits with a colon. We have also provided spaces in front of each of the remaining digits of the number just as if we want to perform straight division.

Now, identify the single-digit number whose square is less than or equal to the digit(s) before the colon on the first line of the figure we have above. Because of the procedure we have used, the number of digits before the ":" is at most 2, and the highest number whose square is less than or equal to a 2-digit number is always going to be a single-digit number.

In this problem, the digit before the ":" is 1, so the highest single-digit number whose square is less than or equal to this is 1. We put the 1 before the ":" on the answer line of the figure above. We also calculate twice of this number (in this case, twice of 1 is 2), and put that number to the left of the "|" on the second line of the figure above. Now, calculate the square of the first digit of our answer, and subtract it from the number before the ":" on the first line. Write the difference on the second line, just before the next digit of the square. The resulting figure is shown below:
•|1: 4 4
2|1:0
---------
•|1:

The 2 to the left of our figure is called the divisor in this procedure. In each subsequent step of the procedure, we will get a gross dividend and a net dividend. What are these quantities?

Our gross dividend is the number we have written after the colon on the second line appended in front of the next digit of the square. Thus, our gross dividend is 04. The net dividend is equal to the gross dividend at this point since there are no digits to the right of the ":" on the answer line. If there are any digits on the answer line to the right of the ":", then the net dividend is equal to the gross dividend - the duplex of the digits to the right of the ":" on the answer line. Note that we calculate duplexes only for digits to the right of the ":" on the answer line. We never include the first digit of the answer line in the duplex calculations.

The figure below has been expanded with lines that denote the gross dividend (in the line labeled G), and the net dividend (in the line labeled N), but these are not necessary when doing the procedure mentally (one can calculate them mentally and proceed with the calculation without writing them down as shown).
•|1: 4 4
2|1:0
G| :04
N| :04
---------
•|1:

At this point, we divide the net dividend by our divisor and write the quotient in the answer line. Write the remainder of such a division in front of the next digit of the square on the second line of the figure. In our case, the net dividend is 4, and dividing that by our divisor, 2, gives us a quotient of 2 and a remainder of 0. The resulting figure is shown below:
•|1: 4 4
2|1:0 0
G| :04
N| :04
---------
•|1: 2

Our next gross dividend is 04 (the remainder from the division we just performed appended in front of the next digit of the square). The next net dividend is 4 - the duplex of 2 (the number to the right of the ":" on the answer line). Since the duplex of 2 is 4, the next dividend becomes 4 - 4 = 0. This is shown in the figure below:
•|1: 4 4
2|1:0 0
G| : 04
N| : 00
---------
•|1: 2

Dividing the net dividend by our divisor, 2, gives us a quotient of 0, and a remainder of 0. Coincidentally, we have also run out of digits in the square. This signifies that we are done with finding the square root (if the remainder had not been zero, we would have added zeroes to the square line and continued the process until we get a remainder of zero or we get tired of continuing the procedure. We will deal with this situation when we try to find the square root of a number that is not an exact square). The final figure we have is below:
•|1: 4 4
2|1:0 0 0
G| :0404
N| :0400
---------
•|1: 2 0

Now, take the number on the answer line. It is 120. Based on our observations from earlier, since the square is 3 digits long before the decimal point, we know that the square root has to be 2 digits long. Thus we take the number in our answer line and put a decimal point after 2 digits, giving us the final square root of 12.0. Obviously, this is correct, and therefore we get some confidence that the method is correct too.

Now, let us tackle a more challenging problem. Let us try to find the square root of 1521. Since the number of digits in the square is 4, which is even, we separate out the first 2 digits of the square for our figure below:
•|15: 2 1
•|
G|
N|
-----------
•| :

Now, we find that the largest number whose square is less than 15 is 3 since 3^2 = 9 (less than 15), and 4^2 = 16 (more than 15). So we put 3 down on the answer line, and twice of 3, which is 6, as our divisor. The difference between 15 and the square of 3 is 15 - 9 = 6. This is put down on the second line of our figure, giving us our gross dividend of 62 and net dividend of 62 (since we don't have any digits on the answer line to the right of the ":", there is no duplex to calculate and deduct from the gross dividend). This is shown in the figure below:
•|15: 2 1
6|09:6
G| :62
N| :62
-----------
•| 3:

When we divide the net dividend, 62, by our divisor, 6, we get a quotient of 10 and a remainder of 2. In this procedure, we find the square root one digit at a time, so instead of using 10 as the quotient (which would add 2 digits to the answer line), we use 9 as the quotient, and 8 as the remainder (since 9*6 + 8 = 62). So, the first digit after the ":" on the answer line would be 9. We now get a gross dividend of 81 in our next step. We calculate the duplex of the digit to the right of the ":" on the answer line (9^2 = 81), and subtract it from the gross dividend to get our net dividend. Dividing the net dividend by the divisor gives us a quotient of 0 and remainder of 0. We also run out of digits in the square, signifying that our procedure is complete. We get the final figure below:
•|15: 2 1
6|09:6 8 0
G| :6281
N| :6200
-----------
•| 3: 9 0

We take the number in the answer line, 390, and put a decimal point after the second digit since we know that the square root of a 4-digit number has to have 2 digits before the decimal point. Thus, we get a final answer of 39.0. One can verify that this is correct by checking to make sure that 39x39 = 1521.

Now, let us try to find the square root of 179776. Since the number contains an even number of digits, we separate out the first two digits. We also find that the first digit of the answer is 4 since 4^2 is 16 (less than 17), and 5^2 is 25 (more than 17). Thus, our initial figure looks like this:
•|17: 9 7 7 6
8|16:1
G| :19
N| :19
------------
•| 4:

The next digit on the answer line becomes 2 since 19 divided by 8 gives us a quotient of 2 and a remainder of 3. Our next gross dividend is therefore 37, and our next net divisor is 37 - the duplex of 2 = 37 - 4 = 33. This is illustrated below:
•|17: 9 7 7 6
8|16:1 3
G| :1937
N| :1933
------------
•| 4: 2

Dividing 33 by 8 gives us a quotient of 4 and a remainder of 1. This leads to the next digit on the answer line being 4. The next gross dividend becomes 17, and the next net dividend is 17 - the duplex of 24, which is 17 - 16 = 01. Dividing 01 by 8 gives us a quotient of 0 and a remainder of 1. This then gives us a gross dividend of 16 and a net dividend of 0 (because the duplex of 240 is 16). The next quotient digit and remainder both become 0. At this time, we have exhausted all the digits of the square, so we are done with the procedure. We get a final figure as below:
•|17: 9 7 7 6
8|16:1 3 1 1
G| :19371716
N| :19330100
-------------
•| 4: 2 4 0 0

We place a decimal point after third digit of the answer line to get our final answer of 424.0. We can verify that 424^2 is 179776, so we have performed the calculations correctly.

We will end this lesson with one more example. Let us try to find the square root of 326041 using our method. I will write down how the final figure looks without the intermediate steps since those steps, by now, would be familiar to all.
••|32:  6  0  4  1
10|25:07 06 01 00
•G| :076060014001
•N| :076011000000
------------------
••| 5: 7 1 0 0

The final answer is 571.00 (since we need to place a decimal point after the 3rd digit of the answer row. We can verify that 571^2 = 326041.

In this lesson, we have not dealt with any special cases that we are likely to encounter when we start dealing with larger numbers. We also found square roots only of perfect squares.

In the next lesson, we will expand on the basics we have covered in this lesson to deal with complications that are likely to result when using this method. We will also apply this method to some numbers that are not perfect squares and see how the method can be used to derive the square root to arbitrary precision. Until then, good luck, and happy computing!

Monday, March 29, 2010

Microsoft Access Tips & Tricks: Job Candidate Matching

You may be wondering what job candidates have to do with Microsoft Access. What I am going to talk about in this post is a general type of SQL query. Selecting suitable candidates for a job is a specific application of such a query. This kind of query is also referred to as "Select all X having Y", and other similar names. It matches two sets of entities based on their properties, but the number of properties of the entities is not fixed, and there is no particular order to the properties either.

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, and calculating statistics with grouping.

The idea behind a Job Candiate Matching query is quite simple. Let us assume that your database contains two tables. One is a table of job candidates called CandidateSkills. The table has the fields "CandidateID", and "SkillID". The table's data contains a list of candidate ID's and ID's corresponding to their various skills.

Note that this table is completely normalized, with separate rows for each candidate's individual skills rather than having a repeating field which limits the number of skills a candidate can have to whatever number of skills fields the table is designed with. Normalization is a separate topic, worthy of several posts to explain how it is done, but avoiding repeating fields is one of the first rules in normalization. For now, we just have to understand that the CandidateSkills table is designed correctly, according to normalization rules.

Obviously, the database would contain a separate table of candidates that would use CandidateID as the primary key, and store details about the candidates such as name, address, etc. Similarly, the database would also have a table of skills that would use SkillID as its primary key and contain data pertaining to each skill (such as the name of the skill, etc.). These extra tables are not important for our discussion in this post.

The data in CandidateSkills may look something like the following:

CandidateID SkillID
1 1
1 2
1 4
1 6
2 1
2 3
2 5
2 7
3 2
3 4
3 6
4 1
4 2
4 3
4 5
4 6
4 7

What the data means is that there are 4 candidates, with ID's 1, 2, 3 and 4. Moreover, candidate 1 has the skills represented by ID's 1, 2, 4 and 6. Similarly, candidate 2 has skills 1, 3, 5 and 7, candidate 3 has skills 2, 4, and 6, and candidate 4 has skills 1, 2, 3, 5, 6 and 7. Even though the data is presented, ordered in ascending order of CandidateID and within that by ascending order of SkillID, there is no such requirement for how the data is entered in the table. The table simply links various candidates with a set of skills, in no particular order.

The other table in our database is a JobSkills table. This table is very similar in structure to the CandidateSkills table. It lists the skills that are required for a list of jobs or job openings. It contains a JobID field and a SkillID field. The SkillID's mean the same in this table as they do in the CandidateSkills table - that is, a SkillID of 1 means the same whether it is a skill possessed by a candidate or a skill required for a job (obviously, SkillID in both tables is a foreign key field that is related to the primary key of a Skills table that has other details about that skill).

The JobID field is a foreign key field that is related to the primary key of a Jobs table that has other details about each job (such as designation, department, etc.). Again, note that the JobSkills table is also designed correctly with no repeating fields, but a separate row for every skill required to do a job.

The contents of the JobSkills Table might look as below:

JobID SkillID
1 1
1 2
1 5
1 6
2 1
2 2
2 3
2 4
3 4
3 5
3 6
3 7
4 1
4 3
4 5
5 2
5 4
5 6
6 1
6 4
6 7

What the data means is that there are 6 jobs, with ID's 1 through 6. Moreover, job 1 requires the skills represented by ID's 1, 2, 5 and 6. Similarly, job 2 requires skills 1, 2, 3 and 4, job 3 requires skills 4, 5, 6, and 7, job 4 requires skills 1, 3, and 5, job 5 requires skills 2, 4, and 6 and job 6 requires skills 1, 4 and 7. Even though the data is presented, ordered in ascending order of JobID and within that by ascending order of SkillID, there is no such requirement for how the data is entered in the table. The table simply links various jobs with a set of skills, in no particular order.

So, the two entities in our matching problem here are jobs and candidates. Candidates have skills, and jobs have requirements. We need to find candidates for each job such that every matching candidate for a job has all the skills required for the job. The candidate may have additional skills, but the candidate should not have any skills missing from the list required for doing the job.

As you can imagine, this kind of problem is quite common in Human Resources departments of companies, for instance. They usually have a database that lists candidates and jobs, and whenever they want to hire for a particular job, they may search for candidates that match the job's requirements. Other applications of this type of application are manufacturing facilities. Such facilities may have a set of parts required for each item they manufacture, and an inventory system that shows them which factory has which parts. They can then match the two and determine which factory should manufacture which items. Such matching problems are quite common, and we solve such problems without thinking about them very often (for instance, selecting a TV to buy based on the features we want in a TV is a similar exercise even though we may not think about using Access to solve the problem using a matching query!).

Since the problem, as presented here, is quite limited in scope because of the numbers of jobs and candidates involved, we will be able to do the matching mentally without any automation. The key to doing this is to take each job, list out its required skills, and then identify candidates that have all of those skills. Thus, for instance JobID 1 requires skills 1, 2, 5 and 6. CandidateID 1 does not match this job because he lacks skill 5. Candiates 2 and 3 are also eliminated because candidate 2 does not have skills 2 and 6, while candidate 3 does not have skills 1 and 5. We find that candidate 4 has all the skills required by job 1, and is therefore a match. Our final output would therefore have one row with JobID of 1 and CandidateID of 4. What we want is a full table that lists all such matches.

Even though it easy to do the matching easily when the number of jobs and candidates, and skills is small, the problem can become very messy when these numbers become higher. Imagine a typical company that may have a dozen or more job openings, thousands of candidates, and perhaps a list of 20 or 30 different skills to match against. That is the prime motivation behind the need for automation of such a system.

The basic idea behind an SQL query to solve the problem is quite simple. This is simply an application of unmatched queries. For each job-candidate combination, we want the unmatched query to find out if there are job requirements that the candidate does not have. If so, that combination of job and candidate is not a good match. If no such "unmatched" requirements exist, that means that the candidate has all the skills required for the job. Therefore, that combination is a good match.

Using the insight above, let us build the query step by step. The unmatched query that we need to verify a good match is shown below:

select SkillID from JobSkills where JobID = 1 and SkillID not in
(select SkillID from CandidateSkill where CandidateID = 1)

This query is obviously for checking whether candidate 1 is a good match for job 1. If this query returns no rows, then candidate 1 is a good match for job 1. If it returns rows, then the match is not good.

This query should now be used as a subquery inside an outer query that selects each combination of job and candidate and checks each for a match. A query that selects all possible combinations from two tables is a simple cartesian join between the two tables. Thus, we could write the outer query as below:

select CandidateID, JobID from CandidateSkills, JobSkills

Now, we have to combine this outer query with the previously written subquery. Moreover, we need to write out only those rows from the outer query where the subquery returns zero rows. One way to check whether a query returns rows is to return a count() from it and check for a value of zero. However, a more elegant way to do this is to use an EXISTS clause for the subquery and check whether any rows exist in the subquery. This technique is shown below:

select CandidateID, JobID from CandidateSkills as CS, JobSkills as JS
where NOT EXISTS
(select SkillID from JobSkills where JobID = JS.JobID and SkillID not in
(select SkillID from CandidateSkill where CandidateID = CS.CandidateID))

As you can see, we have aliased the tables in the outer query, and then used the aliases in the subquery to replace the hard-coded JobID of 1 and CandidateID of 1. We selected only SkillID in the subquery, but we can select any fields we want. An EXISTS subquery only cares about whether the query returns any rows or not. It does not care about the fields returned or the number of fields returned. So, we could write the above query with the subquery being a "select *" instead of just being a "select SkillID".

So, we now have an outer query that creates every possible combination of jobs and candidates using a cartesian join between the two tables. Each combination of job and candidate is then passed on to the subquery that checks if the job has any "unmatched" requirements that the candidate's skills do not cover. If no such skills exist, then the subquery returns no rows, resulting in the EXISTS clause becoming false. Since we use a "NOT EXISTS" as the WHERE clause of the outer query, whenever the EXISTS clause becomes false, the WHERE clause becomes true, and the row is printed out. Thus, we get a final result set that consists of one row for each candidate-job match. All the candidate-job combinations that are not good matches get eliminated by the WHERE clause because the EXISTS clause is true for each such combination. I hope I haven't confused more in the process of trying to explain more clearly, but I am sure you get the idea!

One can always modify the query easily if we need candidate matches for only one job or a subset of jobs, or alternatively, job matches for one or a subset of candidates. The simplest way to do this is to add a WHERE clause to the outer query. But if you know the CandidateID of a single candidate for whom you are looking for job matches, the following query would work too (in the query below, we will assume that the CandidateID of the given candidate is X):

select X as CandidateID, JobID from JobSkills as JS
where NOT EXISTS
(select SkillID from JobSkills where JobID = JS.JobID and SkillID not in
(select SkillID from CandidateSkill where CandidateID = X))

Similarly, if the JobID is X, and we are interested in finding candidates who are a good match for this single job, we could use a query like the one below:

select CandidateID, X as JobID from CandidateSkills as CS
where NOT EXISTS
(select SkillID from JobSkills where JobID = X and SkillID not in
(select SkillID from CandidateSkill where CandidateID = CS.CandidateID))

Now, as mentioned earlier, the subquery in the above queries is an unmatched query. It simply finds elements in JobSkills that don't have a match in CandidateSkills. However, as we saw in the post on unmatched queries, unmatched queries can be written the way we wrote it here, or by using a left or right join. If you are in the mood to experiment, you might want to try rewriting the subqueries in the above queries using a left or right join.

I will warn you right now though: Access will complain loudly and will not allow you to create a query without a syntax error. The Access SQL interpreter seems to have a problem with a query involving a join being used as a correlated subquery (we need a correlated subquery because we want the subquery to work specifically with the JobID and CandidateID selected in the outer query). This is one more reason why it is important to know several ways to accomplish the same thing in SQL. When you run into problems with one approach, you might be able to salvage the situation by trying a different method to get the same results.

Hope this post has been helpful in solving any problems you might have had with job-candidate-matching type problems in Access. If you have any problems or concerns with the SQL in this lesson, 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 lessons, please feel free to let me know through your comments too. Good luck!

Sunday, March 28, 2010

A Frantic Week

This was one of those weeks that just wouldn't give me any free time whatsoever! Work was very busy because we were coming up on a deadline, and one of my colleagues is on vacation this coming week, so the work had to be completed last week.

The work involved processing a large amount of data. The IT department was late in giving us access to the data, and we had to hurry up through the processing of it. As is normal with any large quantity of data, the data had holes and inconsistencies that we had to clean up as we went along. It was a fun exercise that had me staying late at work on more than one day last week.

The couple of days I actually did manage to get off work on time, I had other problems. Twice last week, I had to pick up a friend of my daughters' from her home and bring her to mine to have a play-date and/or sleepover with my daughters. And the sleepovers themselves kept me busy, having to keep an eye on them and get them to bed at the proper time, etc.

It was so hectic, and I was so exhausted by Saturday that I did not even feel like writing anything at all. Instead, I surfed a bunch of websites by allowing Google Blogger to serve up random blogs to my browser. I decided my random surfing might actually be an interesting post in itself, so I actually ended up writing, but it was quite spontaneous and sudden!

Until Saturday night, no visitor from a new country had visited my blog this week. I was thinking that not having a new country on my flag counter widget was another reason not to write today, but a new visitor did show up late on Saturday. The visitor was from Guatemala, and that is the 115th country on my blog's flag counter. I decided I had to write up at least a short post because of this development.

But, on Sunday, I had to take my daughters to a singing performance they had. The singing lasted for all of 20 minutes or so (and my daughters were part of a group, so they did not even sing by themselves for 20 minutes), but the drive each way to where they were performing took 40 minutes. So, that ate up all my time on Sunday evening. Most of Sunday morning was also taken up getting them prepared for the singing by monitoring their practice sessions, etc.

Finally, I have about 20 minutes before bed-time, and I have decided to put something together quickly before I give up on it entirely.

I skipped my usual Thursday post on my blog because of the severe time-crunch. It was the one post I just couldn't get to either during the day or during last weekend. I decided to skip the day even though I had an idea for it, and decided I would do it the coming week instead. So, on Monday, I put together a post on calculating statistics with grouping in Microsoft Access. On Tuesday, I published a post on ugly technology products, and on Wednesday, I concluded my series of posts on polynomial division (yes, this coming week, I move on to a whole new topic). On Friday, I published another set of humorous church signs. Then on Saturday, I was not planning on publishing anything, but decided to chronicle my random surfing in the form of a blog post with a list of blogs I had skimmed that day.

I am hoping my workload at work comes down a bit this week compared to the week that was. That would be the only way I can put together any posts for my blog this coming week. I am thinking of doing a blog post on tax-related jokes in honor of tax month that starts on Thursday. And that reminds me that I have yet to do my taxes this year. I have to find some time within the next couple of weeks to do that too! It is already getting very late, so I will conclude this post with that sobering, or perhaps even, depressing thought!!

Saturday, March 27, 2010

What Random Blogs Did I Browse Through Today?

Yes, I decided that it might be vaguely interesting to go through some random blogs by hitting the "Next Blog" button on the Blogger bar starting with my own blog. I was surprised by the randomness and variety of blogs I encountered on my little round of the blogs today. I decided I would surf from blog to blog until I encountered the same blog twice. I was quite surprised by the number of blogs I surfed before I came to a repeat.

Most of the blogs were personal, family blogs. Only a few seemed to be either professionally oriented, or highly opinionated. The vast majority of the blogs were in English. My comments about some of the blogs are in italics. Many of the blogs had very nice, interesting photographs!

Without further ado, here are the blogs I encountered during my random browsing. I have provided the name of the blog, and the header paragraph that introduces the blog to its readers (if there was one).

Legal Point
A mouth piece for common man A place of legal knowldge A platform for discussing legal issues

Science And Technology On The Hill
Welcome to Science and Technology on the Hill. Could your technology move markets if only the government changed its regulations, legislation, rules, or practices? You need to be informed to make that happen. Or you may be a policy maker sorting out the long-term strategies for education, funding, and privacy issues. We'll give you the latest in science and technology that affects policy making.

ProActive Rants
Got a beef with meat, tired of bad service from those that are paid good public or private money to protect your interests? This is the whistle blowing place to vent your rant and point of view to make them visible and accountable.

Midwatch Cowboy
You're not the senior most OOD, nor the most experienced. But the CO feels that when he's fast asleep, you can tell when things are getting out of hand. And you'll pick up the phone. Congratulations, you're the Midwatch Cowboy. "Maneuvering, Conn, shut up and push."

Marine Historian
The author is a high school history teacher and Marine reservist whose mission is to help document Marine Corps history as it occurs. This blog contains some of his personal observations and reflections as he works to document the role of the Marine Corps in the global war on terror. The opinions herein are his alone and should not be construed as those of the United States Marine Corps or Department of Defense.

Bob Simrak Photography

Assoluta Tranquillita

Fuzzilicious Thinking
The wartime thoughts of a "Fuzzybear Lioness..."
USO girl, Soldiers' Angel, Sheepdog caretaker and proud American

Steven Kiel
Army Reservist

Sharfa's Space

Richard's MIL BLOG from Fort Lewis
LTC Richard Phillips' MIL BLOG discussing today's Army and the facts of life pre and post deployment.

Disinformation and Xenophobia in Western Media
The Institute for Media Ethics is convinced that the honesty or dishonesty of media affects the mental health of the world. Freedom of expression is vital as a means of permitting all views to flourish peacefully. It is a cliché that the price of this freedom must be continual vigilance – in particular vigilance to identify and expose the encouragement of malice, war and the incident of hate speech and image.

New Monastic Individuals
"…there is no sin but ignorance." -- Christopher Marlowe, "Jew of Malta" (c. 1589)

One (Syrian) Disobedient Mofo
The One & Only Disobedient Mofo, A.K.A Fidel KashFlo, The Last One Standing, 47, G-Killa, SyrialKilla, Da Upsettor, Da Last Real G Alive & SteelBallz amongst other names. In here I keep it real, and prove you wrong yet again. What U read in here is not my opinion, it is the truth. Enjoy Yourselves with some O.D.M Wisdome...(((WARNING: READING ON MAY CAUSE SELF RE-EVALUATION, PErception Distortment & CHANGE IN IDEOLOGY))) "GOOD BLESS US, SCREW EVERYBODY ELSE"

Blessed Beyond Measure
Sharing the Joy

What we've done
We're always doing something, but probably we've just lain down and taken a nap, or watched something on t.v., or emptied the dishwasher. I try to write about the other things we do. So grab a tinto and enjoy.

Four Writters Club (Yes, it is spelt as Writters Club, not Writers Club)

Skinner Family
The Family is of God.

The blog that went down

Aaron Rogers

The Crowell Clan
Our Life's Little Adventures

The A-List
A woman searching for what the hell she's supposed to do in life... but trapped in a world of desire for designer jeans, makeup, philanthropy, and faith.

Zime Ilustraciones

The Blatter Family

Empty nest
"Cherish forever what makes you unique, 'cuz you're really a yawn if it goes!" ~Bette Midler~

**4 Ever Breathe**

SwedishMama

At home with Ian
NO LONGER spending ANY MORE time with my unwanted guests cancer and chemotherapy

Catching Our Breath
On living with asthma

Move Along, Nothing to see here
(This blog was true to its name: it had a photo of a gate with a "CLOSED" sign on it and nothing else! The caption below the photo read "For Renovations, well maybe even a new site. I don't know, SOMETHING!")

Jo Blogs
CFS Recovery is Possible

Zola-Ink-Spots
Both wine and bread at confirmation were fake and this small site might just attract others that experienced the same. Critical voices? Those that participate? Who knows. For those that find sympathy with a walk on the wild sides of life, mountains, rivers or forests but do not pretend to escape. Other bits and pieces the news and also odds and sods that cry out "leave it off mate". Justly a lark and maybe the lark. But the lark will often land on the cactus.

SAHM Feminist
Ramblings from my life

The Marshall Family
I'd like to curl up in a sunny spot and take a nap.

Kerry and Megan's Newest Adventure

Roots & Wings
Honoring my roots, flexing my wings

My husband grows cotton
Random chronicles and photos about a Farmwife.

At Full Capacity

Bountifully Blessed!

The perambulations of Barkfoot

†ÅŊĜĘŁŨކ
A little ole place to sit and reflect awhile, whilst the world races around outside and passes you by,....!

Free Flan (the socially awkward dessert)
A blog about life, love and the pursuit of Joe Jonas

My thoughts, poems and short stories

The Sharp End

The World of a Breathless Miss

Danglish

TO SEE WITH EYES SHUT IS TO DREAM

Random thoughts of kindness

The Lindmark Family

Cantabile

The 'whys' that don't matter anymore

The Codielee Edition
Where life, love, art, rodeo {& everything in between} collide

Here,Now
My Life, Right Now

Waiting for directions
My attempts at waiting well

Change Management Blog
Change is a State of Mind.

Let´s /b/log in
This blog was created to be a part of our project.

Diseño de Programas en Orientación Educativa
Asignatura de 5º de Psicopedagogía. Universidad de Vigo.

TSL 641: Computer Assisted Language Learning
This is the class blog for the students of TSL 641: Computer Assisted Language Learning (CALL), ED220, B.Ed TESL, Faculty of Education, Universiti Teknologi MARA, Section 17, Shah Alam, Selangor, Malaysia.

After this blog, I was taken back to the blog just before this one on my list. So, because of the repeat, I stopped surfing for the day. Hope you enjoyed this account of my random surfing! I certainly enjoyed looking at random websites which I would otherwise have probably never visited. My random surfing today took me about 90 minutes. Maybe I will take some time off each day to do this kind of random surfing. Blogger is like a box of chocolates - you never know what you are gonna get!!

Friday, March 26, 2010

Who's Up For A Dozen More Funny Church Signs?

I sure hope you are. That's right - a dozen more humorous church signs, just for your chuckling enjoyment! Enjoy!!

What is missing from church?
Walmart is not the only saving place
Trespassers will be baptized
Stop Drop & Roll does not work in hell
Staying in bed does not constitute going to church
Why change churches?
God wants spiritual fruits, not religious nuts
Gift from God, some assembly required
Bible will scare the hell out of you
These last 3 signs must be from high-tech churches!

Prayer wireless access to God
Don't pay for GPS
Try God's plan of salvation

Wednesday, March 24, 2010

Vedic Mathematics Lesson 45: Polynomial Division 3

In the previous lesson, we saw how we can extend the method we developed for polynomial division by linear divisors in this earlier lesson to divisors of higher degree. In this lesson, we will now look at some applications of this methodology for arithmetic division.

You can find all my previous posts about Vedic Mathematics below:

Introduction to Vedic Mathematics
A Spectacular Illustration of Vedic Mathematics
10's Complements
Multiplication Part 1
Multiplication Part 2
Multiplication Part 3
Multiplication Part 4
Multiplication Part 5
Multiplication Special Case 1
Multiplication Special Case 2
Multiplication Special Case 3
Vertically And Crosswise I
Vertically And Crosswise II
Squaring, Cubing, Etc.
Subtraction
Division By The Nikhilam Method I
Division By The Nikhilam Method II
Division By The Nikhilam Method III
Division By The Paravartya Method
Digital Roots
Straight Division I
Straight Division II
Vinculums
Divisibility Rules
Simple Osculation
Multiplex Osculation
Solving Equations 1
Solving Equations 2
Solving Equations 3
Solving Equations 4
Mergers 1
Mergers 2
Mergers 3
Multiple Mergers
Complex Mergers
Simultaneous Equations 1
Simultaneous Equations 2
Quadratic Equations 1
Quadratic Equations 2
Quadratic Equations 3
Quadratic Equations 4
Cubic Equations
Quartic Equations
Polynomial Division 1
Polynomial Division 2

Before we proceed, we need to be clear that arithmetic division is a messy problem. We have dealt with arithmetic division using different methods in some previous lessons (here, here and here). Using polynomial division techniques for arithmetic division is just one more tool to tackle division that we can add to our arsenal. It is not a magic bullet that is going to make division trivial. But, the more tools we have in our arsenal, the better off we are.

As in this earlier lesson, using polynomial division techniques for arithmetic division relies on our ability to express numbers as polynomials. There is not a single way to express numbers as polynomials, but a multitude of them. In fact, each numeric base will result in a different polynomial expression of a number. The most common numeric base, of course, is 10. Most of this lesson will therefore rely on using 10 as a base in converting numbers to polynomials, but there is no rule that says the base has to be 10. We can use any other power of 10 or any random number such as 2, 3, 14 or whatever else we think will make the problem easier to tackle.

Let us first tackle the simple case of dividing 32895 by 123. It is easy to see that this can be rewritten as dividing 3x^4 + 2x^3 + 8x^2 + 9x + 5 by x^2 + 2x + 3, where x = 10 (thus we have used a base of 10 to convert the numbers into polynomials). Since the divisor contains 3 terms, we need to arrange our dividend into groups of three terms. We also notice that the ratio of coefficients in the divisor is 1:2:3. So, the ratio of coefficients in each of the groups of three terms in the dividend has to 1:2:3 also. Based on this, we see that the following expression of the dividend will work:

3x^4 + 6x^3 + 9x^2
- 4x^3 - 8x^2 - 12x
+ 7x^2 + 14x + 21
+ 7x - 16

This will then lead to the following factorization:

3x^2(x^2 + 2x + 3) - 4x(x^2 + 2x + 3) + 7(x^2 + 2x + 3) + 7x - 16

We can then collect the terms and conclude that the quotient is 3x^2 - 4x + 7, which is simply 300 - 40 + 7 = 267. Similarly the remainder is 7x - 16, which is simply 70 - 16 = 54. We can easily check to make sure that 267*123 + 54 = 32895, so we have performed the division correctly.

Consider another problem, 38979 divided by 132. Using x = 10 as our base, we can express this as the polynomial division of 3x^4 + 8x^3 + 9x^2 + 7x + 9 by x^2 + 3x + 2. We need to express the dividend as groups of three terms, with the coefficients within each group being in the ration 1:3:2. We then write the dividend as below for factorization:

3x^4 + 9x^3 + 6x^2
- x^3 - 3x^2 - 2x
+ 6x^2 + 18x + 12
- 9x - 3

Factorization of the dividend gives us:

3x^2(x^2 + 3x + 2) -x(x^2 + 3x + 2) + 6(x^2 + 3x + 2) - 9x - 3

This will give us a quotient of 3x^2 - x + 6, which is 300 - 10 + 6 = 296. We get a remainder of -9x - 3 = -93. We see that the remainder is negative. This is not the standard way in which the results of a division problem are presented. But the way around it is quite simple. We simply have subtract one from the quotient, and add the divisor to the remainder. Repeat this process until the remainder becomes positive. This procedure then gives us a final quotient of 295, and a final remainder of 39. I encourage you to verify that the answer derived here is indeed correct.

Now consider the division of 56424 by 1421. We can express this as the division of 5x^4 + 6x^3 + 4x^2 + 2x + 4 by x^3 + 4x^2 + 2x + 1. This will require the expression of the dividend in the form of groups of 4 terms, whose coefficients are in the ratio 1:4:2:1. This can be accomplished by expressing the dividend as below:

5x^4 + 20x^3 + 10x^2 + 5x
- 14x^3 - 56x^2 - 28x - 14
+ 50x^2 + 25x + 18

This can be factorized as:

5x(x^3 + 4x^2 + 2x + 1) -14(x^3 + 4x^2 + 2x + 1) + 50x^2 + 25x + 18. This yields us a quotient of 5x - 14, which is 36 when x = 10. We also get a remainder of 50x^2 + 25x + 18, which is 5268 when x = 10. Obviously, this is not the answer expressed in standard form since the remainder is much higher than the divisor. However, the situation is easy to correct. All we have to do is subtract the divisor from the remainder and add 1 to the quotient. Repeat until the remainder becomes smaller than the divisor. Following that procedure, we get a final quotient of 39, and a final remainder of 1005. You can verify that this indeed the correct answer.

Now, let us consider 435323 divided by 212. Once again, we can express this as 4x^5 + 3x^4 + 5x^3 + 3x^2 + 2x + 3 divided by 2x^2 + x + 2, where x = 10. We once again have to arrange the dividend in groups of 3, and the coefficients within each group have to be in the ratio 2:1:2. Based on this, we rewrite the dividend as below:

4x^5 + 2x^4 + 4x^3
+ x^4 + 0.5x^3 + x^2
+ 0.5x^3 + 0.25x^2 + 0.5x
+ 1.75x^2 + 0.875x + 1.75
+ 0.625x + 1.25

As you can see this is getting a little messy, but let us proceed with the factorization as below:

2x^3(2x^2 + x + 2) + 0.5x^2(2x^2 + x + 2) + 0.25x(2x^2 + x + 2) + 0.875(2x^2 + x + 2) + 0.625x + 1.25

This then gives us a quotient of 2x^3 + 0.5x^2 + 0.25x + 0.875. Substituting x = 10 in this expression gives us 2000 + 50 + 2.5 + 0.875 = 2053.375. The remainder is 0.625x + 1.25, which is 6.25 + 1.25 = 7.5, when x = 10. This answer is correct, but it is obviously not in standard form, where we expect our quotients and remainders to be whole numbers. To get rid of the fractional part from the quotient, let us take the fractional part, 0.375 (which is 3/8), multiply it by our divisor, 212, and add it to our remainder. This then gives us a final quotient of 2053, and final remainder of 7.5 + 212*3/8 = 87. We can verify that 2053*212 + 87 = 435323. So, not only was the division performed correctly, but we also managed to get the results into standard form without much difficulty.

Now, let us see how we can use polynomial division techniques to perform the division of 369670 by 1212. There are actually two ways to tackle this problem. The more traditional way is to consider this to be the division of 3x^5 + 6x^4 + 9x^3 + 6x^2 + 7x by x^3 + 2x^2 + x + 2. This would necessitate the expression of the dividend as groups of 4 terms, whose coefficients are in the ratio 1:2:1:2. This is accomplished by writing the dividend as below:

3x^5 + 6x^4 + 3x^3 + 6x^2
+ 0x^4 + 0x^3 + 0x^2 + 0x (note that this line can be skipped entirely)
+ 6x^3 + 12x^2 + 6x + 12
- 12x^2 + x - 12

This can be factorized as:

3x^2(x^3 + 2x^2 + x + 2) + 6(x^3 + 2x^2 + x + 2) - 12x^2 + x - 12

This gives us a quotient of 3x^2 + 6, which is 300 + 6 = 306, and a remainder of -12x^2 + x - 12, which is -1200 + 10 - 12 = -1202. Since the remainder is negative, we add the divisor to it and subtract 1 from the quotient. This gives us a quotient of 305, and a remainder of 10. One can verify that this is the correct answer.

But there is another way to approach this problem using the principle of polynomial division. We can also express it as the division of 36x^2 + 96x + 70 by 12x + 12, where x = 100. Now, we see that the divisor terms are in the ratio 1:1, and the divisor terms have a common factor of 12. We can choose to take care of the common factor in two different ways.

In the first case, let us divide by the dividend terms by the common factor before we factorize. This gives us:

3x^2 + 8x + 70/12

We then express the dividend in groups of two terms, with the coefficients in each group being the ratio 1:1. This gives us:

3x^2 + 3x
+ 5x + 5
+ 10/12

This can be factorized as:

3x(x + 1) + 5(x + 1) + 10/12

This gives us a quotient of 3x + 5, and remainder of (10/12)*12 = 10 (remember that in this method, the remainder has to be multiplied by the common factor to get the final remainder as explained this earlier lesson). Substituting x = 100 in the quotient expression, we see that we once again get a quotient of 305 and a remainder of 10.

The other way to handle the common factor is to ignore it initially, and divide the quotient by 12 after the factorization (remember not to divide the remainder by the common factor if you do it this way, again as explained in this earlier lesson). Using this technique, we can rewrite the dividend as below:

36x^2 + 36x
+ 60x + 60
+ 10

This then factorizes out as below:

36x(x + 1) + 60(x + 1) + 10

This then gives us a quotient of 36x + 60 and a remainder of 10. We divide this quotient by the common factor, 12, to get a final quotient of 3x + 5, which is equal to 305 when x = 100. The final remainder is 10, as before.

The example above is a demonstration of how changing the base in which we do our calculations can sometimes lead to shorter, easier and quicker calculations.

Lastly, let us consider the division of 328972 by 788. This can be expressed in polynomial terms as the division of 3x^5 + 2x^4 + 8x^3 + 9x^2 + 7x + 2 by 7x^2 + 8x + 8. We need to split the dividend into groups of 3 terms each, and the coefficients in each group have to be in the ratio 7:8:8. As soon as you see ratios that involve such numbers, alarm bells should start ringing in your head! But we will ignore them for now, and proceed with the factorization. We start out by rewriting the dividend as below:

3x^5 + (24/7)x^4 + (24/7)x^3
- (10/7)x^4 - (80/49)x^3 - (80/49)x^2
+ (304/49)x^3 + (2432/343)x^2 + (2432/343)x
+ (1215/343)x^2 + (9720/2401)x + (9720/2401)
- (7536/2401)x - 4918/2401

If you still want to proceed with the factorization, you have to be very brave indeed! Obviously, we should have heeded the alarm bells rather than proceeding with the factorization as above.

This is where creativity comes into play. Remember that 788 is also equal to 1000 - 212. Thus, we can express the divisor as x^3 - 2x^2 - x - 2, where x = 10. Now, we see that the dividend has to be expressed in groups of 4 terms each, and the coefficients in each group have to be in the ratio 1:-2:-1:-2. These are much smaller numbers, and will probably not create too many problems. Thus, we decide to proceed with the factorization and are left with:

3x^5 - 6x^4 - 3x^3 - 6x^2
+ 8x^4 - 16x^3 - 8x^2 - 16x
+ 27x^3 - 54x^2 - 27x - 54
+ 77x^2 + 50x + 56

We see that the problem has been much easier to tackle so far already! Now, we factorize the dividend as below:

3x^2(x^3 - 2x^2 - x - 2) + 8x(x^3 - 2x^2 - x - 2) + 27(x^3 - 2x^2 - x - 2) + 77x^2 + 50x + 56

This then gives us a quotient of 3x^2 + 8x + 27, which is 407 when x = 10, and a remainder of 77x^2 + 50x + 56, which is 8256 when x = 10. Obviously, 8256 is much higher than 788, so we need to subtract the divisor, 788, from the remainder and add 1 to the quotient, and repeat the procedure until the remainder becomes less than 788. We recognize that 8256 is a little over 10*788, so we can simply subtract 7880 from the remainder and add 10 to the quotient, instead of doing the procedure of subtracting one 788 at a time from the remainder. This then gives us a quotient of 417, and a remainder of 376.

This is another application of the concept of vinculums that we learned about in this earlier lesson. The use of such creative techniques is sometimes necessary to solve division problems if we don't want to use brute force. Keeping these various techniques in mind and applying them at the correct time under the right conditions is essential when you want to solve problems mentally, and the first technique that comes to mind is not the ideal method to tackle the problem with.

Hope this lesson has provided you with a wide variety of examples of solving arithmetic problems using polynomial division techniques. As mentioned at the beginning of this lesson, division is never a trivial problem, and it is impossible to make it universally easy in any system of mathematics. However, having a large arsenal of tools to rely on and using the right tool at the right time is the secret to tackling them effectively. Good luck, and happy computing!

Monday, March 22, 2010

Microsoft Access Tips & Tricks: Statistics With Grouping

My post on finding percentile values using Microsoft Access received a comment from an interested reader who had an interesting twist on the idea. Instead of finding percentile values for all the data in the table, the commenter wanted to find out percentile values for individual groups in the data. After some experimentation, I found a way to calculate what he/she wanted. But it led me to think about how one can calculate other aggregate statistics of one field like medians, modes, etc. while grouping by other fields in the table. This post is a result of that research.

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, and finding unmatched rows.

Obviously, finding aggregate statistics like average, minimum, maximum, etc. while grouping by other fields is quite trivial in Access. These aggregate statistics have built-in aggregate functions, making their computation simply a matter of using the correct aggregate function in the query. Thus, to find the average of myField while grouping by groupField would involve a query like the one below:
select groupField, avg(myField) from myTable
group by groupField
But what happens when you want to find the median, mode or different percentile values? These don't have built-in aggregate functions, so I devoted entire posts to tricking Access to cough up these statistics using SQL constructs and tricks. It turns out that grouping while finding these kinds of aggregate statistics can be tricky at first. Once you figure out the limitations of Microsoft Access SQL, and learn how to live within them, it is not too bad.

I have provided some solutions to calculating aggregates while grouping below. I have kept these individual sections short so that I can accommodate as many of them as possible in this post. Given the links to the posts that deal with how to calculate these statistics without grouping, you should not have any problem seeing how the SQL there has been extended to accommodate grouping. Still, if you have questions or concerns, please don't hesitate to let me know with your comments.

Also note that even though I have grouped by only one field in all the queries below, you can actually group by as many fields as you want. You just have to add the additional fields to the main query in the GROUP BY clause, and add additional conditions to the subquery in the WHERE clause. In all the queries below, we are calculating aggregates on myField while grouping on groupField. Both of them are fields in myTable.

Median:
SELECT T.groupField, (select max(myField) from myTable where myTable.myField in
(select top 50 percent myField from myTable
where myTable.groupField = T.groupField  order by myField)) AS Median
FROM myTable AS T group by T.groupField
or
select T.groupField, 0.50*(select max(myField) from myTable where myTable.myField in
(select top 50 percent myField from myTable
where myTable.groupField = T.groupField order by myField)) +
0.50*(select min(myField) from myTable where myTable.myField in
(select top 50 percent myField from myTable
where myTable.groupField = T.groupField order by myField desc)) AS Median
from myTable AS T group by T.groupField

Mode:
SELECT groupField, (select max(myField) from myTable where myField in
(SELECT top 1 myField
FROM myTable where myTable.groupField = T.groupField
GROUP BY myField
ORDER BY count(*) DESC)) as Mode
from myTable as T group by T.groupField

Geometric Mean:
SELECT groupField, (select exp(avg(log(myField))) from myTable where myField in
(SELECT myField
FROM myTable where myTable.groupField = T.groupField)) as GeomMean
from myTable as T group by T.groupField

Harmonic Mean:
SELECT groupField, (select count(*)/sum(1.0/myField) from myTable where myField in
(SELECT myField
FROM myTable where myTable.groupField = T.groupField)) as GeomMean
from myTable as T group by T.groupField

Percentile Values:

Medians are just a special case of percentile values, the median being the 50th percentile value. Thus, the SQL for a percentile value while grouping is almost identical to that for the median. In the SQL below, PV represents the percentile value we are trying to find.
SELECT T.groupField, (select max(myField) from myTable where  myTable.myField in
(select top PV percent myField from myTable
where  myTable.groupField = T.groupField  order by myField)) AS PVPercentile
FROM  myTable AS T group by T.groupField
or
select  T.groupField, (1-PV/100)*(select max(myField) from myTable where  myTable.myField in
(select top PV percent myField from myTable
where myTable.groupField = T.groupField order by myField)) +
(PV/100)*(select min(myField) from myTable where myTable.myField in
(select top (100-PV) percent myField from myTable
where myTable.groupField = T.groupField order by myField desc)) AS PVPercentile
from myTable AS T group by T.groupField
Thus, the 25th percentile would be found as below:
SELECT T.groupField, (select max(myField) from myTable where  myTable.myField in
(select top 25 percent myField from myTable
where  myTable.groupField = T.groupField  order by myField)) AS PVPercentile
FROM  myTable AS T group by T.groupField
or
select  T.groupField, 0.75*(select max(myField) from myTable where  myTable.myField in
(select top 25 percent myField from myTable
where myTable.groupField = T.groupField order by myField)) +
0.25*(select min(myField) from myTable where myTable.myField in
(select top 75 percent myField from myTable
where myTable.groupField = T.groupField order by myField desc)) AS 25Percentile
from myTable AS T group by T.groupField
Notice the pattern in all the queries above: We select groupField first in the main query so that we know which group the aggregate statistic belongs to. The main query also sets an alias for myTable that can be used in the subquery. The main query also has a GROUP BY clause to group by groupField.

The second field selected in the main query is a calculated field. The calculated field is our aggregate statistic for myField. We use a correlated subquery to perform the calculation. The correlated subquery uses another subquery with an IN clause to calculate the value. The query inside the IN clause uses the value of groupField from the main query to subdivide the table into multiple temporary tables that contain only rows that have the same value of groupField as in the outer query.

The aggregate statistic we need is then calculated inside this subquery. Since the subquery has segregated the table to include only rows with the value of groupField equal to the value of groupField in the main query, we end up calculating the aggregate statistic for that group of rows.

Using this pattern, it should now be possible for you to calculate other aggregate statistics while grouping if you have the need to. If there are any that you have problems with, I would love to hear about it so that I can take it on as a challenge while helping you out at the same time. Good luck!

Hope this post has been helpful in solving any problems you might have had with aggregating with grouping in Access. If you have any problems or concerns with the SQL in this lesson, 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 lessons, please feel free to let me know through your comments too. Good luck!

Sunday, March 21, 2010

I Am Now Ready For "The Two Towers"!

I had very little time to read last week. I was busy at work, came home later than usual most days, then had to attend a couple of social programs that took some more of my time. I was also dealing with setting up my new DSL modem, and testing a new power supply and so on and so forth. But through it all, by squeezing in as little as a page at a time, I finished "The Fellowship Of The Ring" this week.

It was a book that was hard to put down. Every interruption that I had to endure during my reading of it was painful, but such is life. Now, I am looking forward to starting "The Two Towers". I hope the book is not checked out when I try to pick it up at the library tomorrow. I don't think I need have much fear of that because most people don't seem to have much interest in such classic books anymore.

I see it even in my daughters' reading habits. My daughters are voracious readers who read all the time. In fact, they read during their meals, which I don't approve of at all. But you have to pick your fights! Anyways, in spite of their reading during most of their waking hours (outside of school), I can't get them to read much non-fiction. That is another fight I have had repeatedly with them (this I am not willing to give up on yet). When I meet their teachers at parent-teacher conferences, I have let them know about this problem, and they have acknowledged that it is difficult to get children to read non-fiction.

As an alternative to getting them to read more non-fiction books, I offered them a compromise. I would not harp on them to read non-fiction if they would read classics, as identified by me. But that has not gone too well either. I picked out some Robert Louis Stevenson books like "Treasure Island" for my daughters. They read a few pages because I insisted they do. After that, they went back to their usual fare. Maybe they will develop an interest in the classics later in life. In the meantime, I doubt I will have much competition at the library trying to pick up "The Two Towers" tomorrow!

At work, things have been proceeding vaguely in the direction I want things to go. There have been many detours and meanderings that have wasted time and effort. This week also some false starts and a couple of false alarms that had us thinking we had hit a brick wall which we could not surmount. But we managed to move forward anyways. We still are on track as far as our deadlines are concerned, so I am not complaining yet.

I also had my performance review this week. The review turned out to be approximately as I expected, there were no surprises on either the positive or negative side. I gave my manager a decent accounting of my performance last year, and in exchange he gave me a manager's eye view of what I did right and what I did wrong. Our perspectives were not too far off, so we shook hands on the review and called it done.

In my karate class, one of the newer students took his green belt test last week. I had trained him with his latest katas, and also worked with him on his self-defense techniques and other skills he needed for this test. I was relieved that he did pretty well on his test, and passed without any problems. Obviously, he needs more work, but as my sensei loves to point out, everybody in the world, including himself needs more work all the time. But, overall, he was quite pleased with the performance of this student.

My sensei has also let me know that I will be taking my black-belt test pretty soon. I am reviewing all my katas in class right now and ironing out the wrinkles in them so that they look presentable in a black-belt test. I am also working on my other skills like self-defense, sparring, etc. I am pleased with how my interval training workout routine has improved my endurance and stamina while sparring. I have a feeling that will come in quite handy in the actual black-belt test when I will probably be sparring continuously for 15 to 20 minutes or more, and probably against more than one opponent at a time too.

My sensei also told me that part of becoming a black belt in the martial arts is the ability to teach others and convey the art to them. I obviously can not be tested on this aspect of things during the test itself, but I am probably being tested right now with my assignments to teach the newer students various techniques and getting them ready for their tests.

At home, my daughters had a major dance performance this week. They had a bunch of rehearsal sessions before that. The dance performance itself was about 3 hours long. It went off very well, and my daughters danced very well if I do say so myself! Shuttling them to the rehearsal sessions, in addition to their already full schedule of music lessons, gymnastics lessons, violin lessons, etc., kept my wife and I pretty busy this week.

But now, they have their spring break, so at least I don't have to take them to school for the next week. They still have a couple of classes during the break, and they have a music performance next Sunday. But hopefully, the frenetic pace of life at home will come down significantly this week.

As I wrote last Saturday, I did not have internet access for a few days earlier this week. But since I still had internet access at work, my blogging was not affected much. My post on Microsoft Access focused on unmatched queries. All the graphics in that post were created by me using various tools like MS Paint and MS Powerpoint. Those were the graphics I talked about last Sunday as taking a whole morning.

On Tuesday, I came out with a post on the 10 worst PC's of all time. On Wednesday I continued my explorations into the nature of polynomial division. On Thursday, I wrote up a post on regaining internet access at home (it actually happened late on Tuesday, but I did not have time to write about it right away). On Friday, I put together a post on some humorous, confusing signs, and another post on giving away a power supply I had bought with the hope that it would work with my DSL modem as a replacement power supply (actually it did not, which is why I want to give it away to someone who might have a better use for it).

Even though I avoid being too politically slanted or opinionated, I felt strongly enough about healthcare reform to put together a post on Saturday arguing for it. I also took my own advice from that post and sent emails to my congresspersons urging them strongly to vote for healthcare reform or lose my vote in the next election. If enough people did this, I am sure healthcare reform would be a done deal by now. As I write this, I am reading news articles that say there are now enough votes in the US House of Representatives to pass the healthcare reform legislation under consideration. I am happy. But I hope this is just the first step on the road towards a national single-payer healthcare system, which I am convinced, is the only system that works over the long term.

This week four visitors from new countries visited my blog. They were from Saint Vincent & The Grenadines, Uganda and Oman. Yes, I said 4 new countries, and I have listed only 3. Well, the fourth new "country" was classified as being from an unknown anonymous proxy. I have had visitors to my blog from 114 "countries" so far (4 of them are actually not countries. Flag counter says they are from "Unknown European Union", "Unknown Asia/Pacific Region", "Unknown Satellite Provider", and "Unknown Anonymous Proxy").

What a wonderful thing the internet is. In what other medium can I write such that people from 114 (or 110 if you want to get technical about it!) different countries would read my words? Newspaper publishers would be lucky in the days before the internet to have readers from a dozen countries read their writings in printed form! Obviously, my blog does not yet have the circulation of a printed newspaper, but printed newspapers are bridging the gap in leaps and bounds by losing their circulation numbers rapidly!

That is going to have to be all for now. I spent most of today shopping for clothes for my daughters. Going in and out of stores trying to satisfy their tastes while at the same time trying to make sure I don't break the bank was quite exhausting. I don't know what I would do without my wife doing most of the legwork. It was exhausting enough driving them to the mall, and sitting around waiting for my wife and kids to troop through the stores looking for clothes! It is time for me to unwind and catch a movie or something before I have to get ready for another week at work. Why can't they give me a spring-break like my kids have?!

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