Sunday, 31 May 2015

How to calculate Average between two Dates in Excel

Excel AverageIf Function to calculate Average between two Dates
Suppose we have a Sales data table as follows :


Prod_Id
Sal_Date
Net Sales
359
5/15/2015
1472
264
5/16/2015
6266
435
5/17/2015
8877
359
5/18/2015
3881
264
5/19/2015
7190
435
5/20/2015
1778
359
5/21/2015
9394
264
5/22/2015
4185
435
5/23/2015
3560


Suppose if we want to find the Average of Net Sales between two dates , we can find generally using AverageIf or AverageIfs Functions.

Also we can calculate the Average between two Dates using an Array based Average and If  Combo Formula as shown below :

Cond_1:
Cond_2:
AVERAGE FORMULA
Result
5/18/2015
21-May-15
 =AVERAGE(IF(($B$1:$B$10>=E2)*($B$1:$B$10<F2),C1:C10))
4283

Examples :
Source Data as follows :
Prod_Id
Month_No
Sales
359
3
1472
264
5
6266
435
7
8877
359
5
3881
264
4
7190
435
2
1778
359
5
9394
264
3
4185
435
2
3560

Calculating Average based of Different Criteria as follows :
Cond_1:
Cond_2:
AVERAGE FORMULA
Result
3
5
 {=AVERAGE(IF($B$1:$B$10={3,5},C1:C10))}
5039.6
359
5
 {=AVERAGE(IF(($B$1:$B$10=5)*($A$1:$A$10=359),
C1:C10))}
6637.5
">=3" &<"5"
264
 {=AVERAGE(IF(($B$1:$B$10>=3)*($B$1:$B$10<5)*
($A$1:$A$10=264),C1:C10))}
5687.5


Note :
To understand more about the AverageIf and AverageIfs , please go through the below link of this Blog Post.

http://excelkingdom.blogspot.in/2013/03/excel-averageif-and-averageifs-functions.html

Thanks,
TAMATAM

Friday, 29 May 2015

SQL Server Query to find Highest or Maximum Salary in each Department

SQL Query to Retrieve Highest or Maximum Salary drawing Employee Details from each Department

Example :
Suppose we have a Employee Table as follows:

EMP Table :
EMP_ID
F_Name
L_Name
Dept_ID
Cur_Address
Perm_Address
Salary
1001
Ravi
Kumar
1
Hyderabad
Hyderabad
20000
1002
David
Smith
2
Bangalore
Pune
35000
1003
Victory
Venkatesh
1
Hyderabad
Hyderabad
50000
1004
Tamatam
Reddy
3
Bangalore
Hyderabad
25000
1005
William
Smith
2
Hyderabad
Bangalore
40000
1006
King
Fisher
6
Bangalore
Bangalore
30000


Now if we want to Retrieve Highest or Maximum Salary drawing Employee Details of each Department , from the above table , we can use the following SQL Sub Query :

SELECT E_Id,(F_Name+'_'+L_Name) as E_Name,Dept_Id,Salary From EMP Where Salary IN (SELECT MAX(Salary)AS Max_Sal From EMP GROUP BY Dept_Id)

Output :
E_Id
E_Name
Dept_Id
Salary
1003
Victory_Venkatesh
1
50000
1004
Tamatam_Reddy
3
25000
1005
William_Smith
2
40000
1006
King_Fisher
6
30000

Thanks,
TAMATAM

SQL SERVER CARTESIAN JOIN or CROSS JOIN Syntax and Example

SQL Server CARTESIAN Product or CROSS JOIN 
The Cross Join returns the Cartesian product of the sets of records from the two or more joined tables.
when a join condition is omitted when getting result from two tables then that kind of query gives us Cartesian product, in which all combination of rows displayed. All rows in the first table is joined to all rows of second table.



Syntax :
Select* from Table_I , Table_2....
OR
Select* from  T1.Column1,T1.Column2, T2.Column3,T2.Column4 from Table_I  T1, Table_2 T2


Example :
Suppose we have a Two Tables as follows:
EMP Table :

DEPT Table :

Cartesian or Cross Join Query :
SELECT E_Id,(F_Name+'_'+L_Name) as E_Name , D.Dept_Id,D.Dept_Name 
from EMP E, DEPT D

Output :
As the Table_1(EMP) contains 6 records and Table_2(DEPT) has 3 records , the Cartesian/Cross Join gives an output of 18 (6*3) records as shown below :



--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence Professional

--------------------------------------------------------------------------------------------------------

Thursday, 28 May 2015

MS Access Sample PTO Tracker Form

MS Access Sample PTO Tracker Form to Store PTO Details of Employees
Let us suppose we have a sample Employee Table as follows :




Now we need to store the PTO Details of the Employees in the below format :




For this , I have designed a Form as shown below :



-----------------------------------------------------------------------------------------------------------------
Code for the Above Form as follows :
-----------------------------------------------------------------------------------------------------------------
Option Compare Database

'Refreshing the Form on Load
Private Sub Form_Load()
Form.Refresh
End Sub
----------------------------------------------------------------------------
'Refreshing the EMP_ID combo values 
Private Sub cmbo_EMP_ID_GotFocus()
Me.cmbo_EMP_ID.Requery
End Sub
----------------------------------------------------------------------------
'Retriving EMP_ID ,EMP_Process values from the Employee Table[MyTeam] based on the selection of E_Name , and loading in to text boxes  EMP_ID ,EMP_Process of Form.
Private Sub Cmbo_EMP_NAME_AfterUpdate()

Dim SQL_Str As String
Dim DB As Object
Dim RS_ID As DAO.Recordset
Dim RS_Process As DAO.Recordset

'Retriving EMP_ID ,EMP_Process values from the Employee Table[MyTeam] based on the selection of E_Name.

E_NAME = Me.Cmbo_EMP_Name.Value

Str_ID = "SELECT EMP_ID FROM MyTeam WHERE MyTeam.EMP_NAME=" & """" & E_NAME & """" & ";"

Str_Process = "SELECT EMP_Process FROM MyTeam WHERE MyTeam.EMP_NAME=" & """" & E_NAME & """" & ";"

Set DB = CurrentDb
Set RS_ID = DB.OpenRecordset(Str_ID)
Set RS_Process = DB.OpenRecordset(Str_Process)

'Getting EMP_ID ,EMP_Process values from the Employee Table[MyTeam] to respective Texboxes of Form based on the selection of E_Name.

Me.txt_EMP_ID.Value = RS_ID.Fields("EMP_ID").Value
Me.txt_Process = RS_Process.Fields("EMP_Process").Value

End Sub
----------------------------------------------------------------------------
'At any moment while inputting into the form , if you want to Reset the form on clicking the Reset button , you can assign the below code to 'Reset' button.
Private Sub cmd_Reset_Click()

Me.Cmbo_EMP_Name = ""
Me.txt_EMP_ID = ""
Me.cmbo_LEAVE_TYPE = ""
Me.txt_Process = ""
Me.txt_COMMENTS = ""
Me.txt_PTO_START_DATE = ""
Me.txt_PTO_END_DATE = ""

End Sub
----------------------------------------------------------------------------
'Validating the Form Input on clicking the Submit button and then submitting/updating the data to the Output Table(PTO_Details) , assign the below Macrp code to 'Submit' button :
Private Sub cmd_Submit_Click()
DoCmd.SetWarnings False

Checking that mandatory input values should not be left blank
If Me.Cmbo_EMP_Name.Value = "" Then
MsgBox "Employee Name Field Cannot be Blank", vbCritical, "Please Enter Employee Name"
Exit Sub

ElseIf IsNull(Me.txt_EMP_ID.Value) Or Me.txt_EMP_ID.Value = "" Then
MsgBox "Employee ID Field Cannot be Blank", vbCritical, "Please Enter Employee ID"
Exit Sub

ElseIf Me.txt_Process = "" Then
MsgBox "Employee Process Field Cannot be Blank", vbCritical, "Please Enter Employee Process"
Exit Sub

ElseIf Me.cmbo_LEAVE_TYPE = "" Then
MsgBox "Leave Type Field Cannot be Blank", vbCritical, "Please Enter Leave Type"
Exit Sub

ElseIf Me.txt_PTO_START_DATE = "" Then
MsgBox "PTO Start Date Field Cannot be Blank", vbCritical, "Please Enter PTO Start Date"
Exit Sub

ElseIf Me.txt_PTO_END_DATE = "" Then
MsgBox "PTO End Date Field Cannot be Blank", vbCritical, "Please Enter PTO End Date"
Exit Sub

'Checking that PTO End Date should be greater that PTO Start Date
ElseIf Me.txt_PTO_END_DATE.Value < Me.txt_PTO_START_DATE.Value Then
MsgBox "The End Date should be Greater Than or Equal to Start Date", vbCritical, "Please Select Correct Date"
Me.txt_PTO_END_DATE = ""
Exit Sub

End If

'After data validation , submitting the Form Input to the Output Table ( PTO_Details):
Update_Query1 = "INSERT INTO PTO_Details ([Employee_ID],[Employee_Name],[Process],[Leave_Type],[PTO_Start_Date],[PTO_End_Date],[Comments])" _
& "Select[Forms]![PTO_Tracker]![txt_EMP_ID] AS [Employee_ID],[Forms]![PTO_Tracker]![cmbo_EMP_NAME] AS [Employee_Name]," _
& "[Forms]![PTO_Tracker]![txt_PROCESS] AS [Process],[Forms]![PTO_Tracker]![cmbo_LEAVE_TYPE] AS [Leave_Type]," _
& "[Forms]![PTO_Tracker]![txt_PTO_START_DATE] AS [PTO_Start_Date],[Forms]![PTO_Tracker]![txt_PTO_END_DATE] AS [PTO_End_Date]," _
& "[Forms]![PTO_Tracker]![txt_COMMENTS] AS [Comments]"

DoCmd.RunSQL Update_Query1

'Clearing the Form Input fields After submitting the Form Input to the Output Table ( PTO_Details):

Me.Cmbo_EMP_Name = ""
Me.txt_EMP_ID = ""
Me.cmbo_LEAVE_TYPE = ""
Me.txt_Process = ""
Me.txt_COMMENTS = ""
Me.txt_PTO_START_DATE = ""
Me.txt_PTO_END_DATE = ""
DoCmd.SetWarnings True

End Sub
----------------------------------------------------------------------------

'Exporting the Form Data when you click on the button 'Export PTO Data' , assign the below Macro code to 'Export PTO Data' button.
Private Sub Exp_PTO_Data_Click()
Dim OP_FileName As String

CurDB_Name = Left(CurrentProject.Name, Len(CurrentProject.Name) - 6)
OP_FileName = CurrentProject.Path & "\" & CurDB_Name & "-" & Format(Date, "DDMMMYYYY") & ".xlsx"

Dim Tbl As Object
Dim DB As Object

Set DB = CurrentDb


Tbl_Name = "PTO_Details"

'Exporting 'PTO_Details table to Excel in same location of Form/Database
DoCmd.TransferSpreadsheet acExport, , Tbl_Name, OP_FileName, True

MsgBox "PTO Data Exported SuccessFully", vokonly, "Export Success"

End Sub
-----------------------------------------------------------------------------------------------------------------
Key Notes :
-----------------------------------------------------------------------------------------------------------------
In the above from :
[Me.Cmbo_EMP_Name]  is a Combo Box
Me.txt_EMP_ID] is a Text Box
[Me.cmbo_LEAVE_TYPE] is a Combo Box
[Me.txt_Process] is a Text Box
[Me.txt_COMMENTS] is a Text Box
[Me.txt_PTO_START_DATE] is a Text Box with Date Picker
[Me.txt_PTO_END_DATE] is a Text Box with Date Picker

My Dear users please try understand that , we have to assign the right Macro code to the right Event/Property of the Form Control.

To understand the above Form structure completely we need to look into the Properties of the each Form Control.

But the above piece of Information will give you an Idea of Form Design and Validating and  Submitting Form Input as well as Exporting Form Data.

-----------------------------------------------------------------------------------------------------------------
Thanks,
TAMATAM






Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts