counting total records v/s no of records based on a given condition ..in a single sql query

vb.net , access Bangalore, India
  • 8 years ago
    i'm using vb.net 2008 with msaccess 2003 database.i'm writing an windows application for calculating the employees attendance details like Number of days worked, No of days Absent.I've a column named "Status" in access table. query="SELECT Employee_ID, Employee_Name, count (*) as [Total Days] From EmpTable Where (Employee_Name='" & ComboBox1.Text & "' and Attendance_Date between #" & Me.DateTimePicker1.Value.Date & "# and #" & Me.DateTimePicker2.Value.Date & "#) group by Employee_ID, Employee_Name" this query me ...Total Days (total working days including absent & present days of employee) as shown in the picture (Total Days column).I also wanted to show a new column which gives No of Days Present...so my query should look like this.... select count(*) as [Days Present] from EmpTable Where (Employee_Name='" & ComboBox1.Text & "' and Attendance_Date between #" & Me.DateTimePicker1.Value.Date & "# and #" & Me.DateTimePicker2.Value.Date & "# and status='P') i'm adding status column condition in where clause..."P" stands for present...as stored in status column of access database. my requirement is to show the result using both the above mentioned queries...i mean how to combine these two queries into a single query which gives the output as i mentioned.
  • 8 years ago
    Hi, I am not an SQL guru by any means, but when I have to do something like this, then I tend to use Case when in Oracle. Do you know if Access has an equivalent? You could use it in this scenario by just selecting every status of attendance from the correct date range, and then use something like: select EmployeeId, EmployeeName, case when status='P' then +1 else 0 end as "Days Present", case when status='A' then +1 else 0 end as "Days Absent", from [tablename] where employeeName= :EmployeeName and attendanceDate between :StartDate and :EndDate group by EmployeeID, EmployeeName Note the use of parameterised queries to stop people running amok and using SQL injection attacks against you. Let me know if you need any more help. Regards Simon C

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“A computer lets you make more mistakes faster than any other invention in human history, with the possible exceptions of handguns and tequila” - Mitch Ratcliffe