Archive for the ‘MS Access’ Category

The Ultimate Correlated SQL Query

Posted: August 23, 2013 by Transformer in MS Access, SQL
Tags: , ,

Problem Statement:  There is a table named SkillSet which has employees’ skill details. We need to find out employees (skill wise) who have the highest proficiency rating. If multiple employees have the highest proficiency rating in some skill then the employee having more experience should be selected.

Employee Skills Proficiency Experience
Thor SQL 9 1
Thor VBA 8 1
Mike VBA 9 1
Mike SQL 6 1
Robert VBA 9 2
Robert C# 6 2
Robert SQL 9 1
Logan C# 9 1
Logan SQL 9 2

Solution:Lets do it in steps. Try to find out skill wise employees having maximum proficiency. (more…)

Conditional Insert Query Based on Data in Target Table

Posted: June 23, 2013 by Transformer in MS Access, SQL
Tags: ,

Problem Statement: There is a table named Employee which has department wise employee details as given below. We want to insert some new records in this table but a record should not be inserted if it is already there in the table.


Dept	Employee_ID	Gender
IT	E123	          M
IT	E124	          M
IT	E125	          M
IT	E126	          M
IT	E127	          F
IT	E128	          F
HR	E129	          M
HR	E130	          F
HR	E131	          F
HR	E132	          F
HR	E133	          M

Solution: This can be done using the following query where we use EXISTS to check whether or not that record already exists.


INSERT INTO Employee (Dept,Employee_ID,Gender)

   SELECT  TOP 1 'HR' , 'E132','F' 
       FROM
         EMPLOYEE 
  WHERE Not EXISTS 
         (SELECT 1 
             FROM 
               Employee 
          WHERE Dept = 'HR' AND Employee_ID = 'E132'  AND Gender = 'F')

The above query will not insert the record in the table since it already exists.
Here TOP 1 has been used to create a single record to insert otherwise it will return multiple records(No of records in the Employee Table) with the same values.


Problem Statement: There is a table named Employee which has department wise employee details as given below. We want to find out gender-wise subtotals and a grand total for each department.


Dept	Employee_ID	Gender
IT	E123	          M
IT	E124	          M
IT	E125	          M
IT	E126	          M
IT	E127	          F
IT	E128	          F
HR	E129	          M
HR	E130	          F
HR	E131	          F
HR	E132	          F
HR	E133	          M

Solution: This can be done using a UNION query where we calculate gender wise population in one subquery and department wise population in the other and then union them as below:


SELECT [Dept], [Gender], count(*)  as Pop
FROM  
        [Employee]  
GROUP BY [Dept], [Gender]

UNION                   

SELECT [Dept], "Total", Count(*) as Pop
FROM
        [Employee]  
GROUP BY [Dept]

This query will return the following result:

Dept	    Gender	 Pop
HR	       F	   3
HR	       M	   2
HR	    Total	   5
IT	       F	   2
IT	       M	   4
IT	    Total	   6

Calculating Ratio Within Groups using Self Join

Posted: May 21, 2013 by Transformer in MS Access, SQL
Tags: ,

Problem Statement: There is table named Employee that has department wise employee details as given below:


Dept	Employee_ID	Gender
IT	E123	          M
IT	E124	          M
IT	E125	          M
IT	E126	          M
IT	E127	          F
IT	E128	          F
HR	E129	          M
HR	E130	          F
HR	E131	          F
HR	E132	          F
HR	E133	          M

We have to calculate ratio between male and female employees in each department.
Solution: This can be done using a self join query where we calculate gender wise population in one query and department wise population in the other and join them as below:

SELECT T1.Dept, T1.Gender,T1.Pop/T2.Pop
    FROM
            (SELECT Dept,Gender,count(*)  as Pop
                  FROM  
                        Employee  Group By Dept,Gender) as T1

                       INNER JOIN

             (SELECT Dept,Count(*) as Pop
                   FROM
                       Employee  Group By Dept) as T2

 ON T1.Dept = T2.Dept

Full Outer Join in MS Access

Posted: May 18, 2013 by MaxFraudy in MS Access, SQL
Tags: ,

Problem Statement: MS Access does not allow a full outer join query. What this means is that if I have two tables and I want all data from both of these tables, I cannot do it using one join.

Sql full outer join image

Image Credit – http://www.w3resource.com

Let’s take, for example, two tables – Table1 & Table2. Both these tables have employee information but not all employees are common. Table1 has emp_id 9 which is not there in Table2 while Table2 has emp_id 3 which does not appear in Table1. I want all employee IDs, common as well as non-common.


   Table1                           Table2
Emp_id	Salary			Emp_id	Name
1	10000			1	Tom
2	23000			2	Rock
4	40000			3	Thor
9	34000				

(more…)


Problem Statement: There are two tables and we need to get all the records from Table1 that are not in Table2.


   Table1                           Table2
Emp_id	Salary			Emp_id	Name
1	10000			1	Tom
2	23000			2	Rock
4	40000			3	Thor
9	34000				

Excluding Records from Table1 that Exist in Table2

Solution: It can be done in two ways, using Left Join OR using Exists condition. (more…)

Use of Self Join in Update Query

Posted: May 3, 2013 by Transformer in MS Access, SQL
Tags: , , ,

Some times one might need to update a record in a table based on a calculation on some field of the same table.It can not be done without using Self Join.
e.g. Consider the following data, there is a region named Region1 and this region includes C1 ,C2 and C3 cities.
So the population of Region1 is equal to the sum of population of these three cities.Now there are two more cities C4 and C5 that needs to be added in Region1.
Now population of Region1 should be updated by adding the population of C4 and C5 into it.


Geography	Population
C1	         136376
C2	         154644
C3	         176040
C4	         148793
C5	         138288
C6	         137754
C7	         141093
Region1	         467060
Region2	         278847

(more…)


How many times have you needed to copy the path of one of your opened files and you had to go all browsing through folders to get it?…. Many, right? What if you could get it without having to go anywhere else? Sounds lovely, doesn’t it?

Well, you actually can do it without much of an effort. Simply follow the steps below and you are set:

  1. Go to ‘Quick Access Toolbar’ (QAT) that appears in all MS Office files.
  2. From the dropdown, select ‘More Commands’

    QAT - More Commands

    QAT – More Commands

  3. In the pop-up that appears, Go to ‘Customise’ tab
  4. In the left dropdown, select ‘All Commands’

    Untitled

    All Commands in ‘Customise’ tab

  5. A long list of available options appears in the box below. Scroll down and select ‘Document Location’.
  6. Click on the ‘Add’ button.
  7. Click OK.
  8. The option will appear on the right. This is the list of commands available to you in the QAT.

    Untitled

    Available Commands in QAT

That’s it!!! Now the location of any document that you are viewing can be seen right at the top.

Tip: If there are any other commands that you use on a frequent basis, then they can also be added to the Toolbar. Simply right-click on that option and select ‘Add to Quick Access Toolbar’.

QAT - More Options

QAT – More Options

For more options, check out MS Office Support page

RANK in MS Access

Posted: April 23, 2013 by Transformer in MS Access, SQL
Tags: , , ,

Sometimes one needs to rank data based on some field e.g. Students’ Marks, Product Sales etc. In different platforms there are different ways to do it.
In SQL Server and Excel there is RANK function that does the job but in MS Access there is no such built-in function.

However same result can be achieved using a correlated query.

e.g. Consider a dataset of student marks below.

Students Marks
Tony 34
Bob 32
Thor 48
Jack 42
Tom 41
Kate 45
Sid 26

Suppose one wants to rank students based on their marks (rank = 1 for highest marks). Following query can be used to rank them.

SELECT Students,
       (SELECT COUNT(T1.Marks)
          FROM
                 [Table] AS T1
         WHERE T1.Marks >= T2.Marks) AS Rank
FROM
      [Table] AS T2
ORDER BY Marks DESC

Following is the result of the query.

Students Rank
Thor 1
Kate 2
Jack 3
Tom 4
Tony 5
Bob 6
Sid 7

If,  rank 1 is to be assigned to the lowest value, then in that case the query above can be slightly modified to:

SELECT Students,
       (SELECT COUNT(T1.Marks) 
          FROM
                 [Table] AS T1 
         WHERE T1.Marks <= T2.Marks) AS Rank 
FROM 
      [Table] AS T2 
ORDER BY Marks 


There is often a confusion in the minds of most people when it comes to using the HAVING clause. On the surface of it, it looks very similar to how a WHERE clause works, but that is not the case. While WHERE clause filters out records before data aggregation is done, HAVING does it later.

Let’s take an example to understand it better. I have a dataset of employee salaries with details of their departments.

Employee_ID                    Dept                                 Salary
E10002                                HR                                   14000
E10050                                IT                                    20000
E15334                                HR                                   15000
E33336                                IT                                    16500
E35480                                Legal                                30000
E45670                                Legal                                29000
E74016                                HR                                   12000

Suppose I want to find out the average salary of the HR and the IT department. I will write the following query:

          SELECT dept, AVG(salary) AS AVG_SAL
         FROM employees
        WHERE dept IN (“HR”, “IT”)
        GROUP BY dept

Continue Reading