Database

count total records and (number of records based on a condition) for same column in single sql query

2-Feb-09 18:19

You can test the condition for example with NOT EXISTS condition. Something like:

SELECT ...
FROM  YourTableName alias1
WHERE NOT EXISTS (SELECT 1
                  FROM  YourTableName alias2
                  WHERE alias2.DateTimeColumn > alias1.DateTimeColumn
                  AND   alias2.DateTimeColumn <= alias1.DateTimeColumn 
                                                 + (1 / 24 / 60 / 3))
...

When adding to datetime, 1 means 1 day, so it's divided to get 20 seconds

The need to optimize rises from a bad design.My articles[^]

chandrubngit2-Feb-09 5:28

chandrubngit

2-Feb-09 5:28

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.

Re: count total records and (number of records based on a condition) for same column in single sql query

Colin Angus Mackay2-Feb-09 5:52

*Developer Day Scotland - Free community conference
*Colin Angus Mackay's Blog
*Latest Scottish Developers Newsletter

2-Feb-09 5:52

chandrubngit wrote:
(Employee_Name='" & ComboBox1.Text & "' and

AAAaaaarrrrggghhhhh!!!! You're vulnerable! You have committed the ultimate sin and you have left yourself open and defenceless.

Please don't let a SQL Injection Attack ruin your career, learn to defend yourself[^]. Remember, practice safe querying.

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Remember that the force of gravity can go up as well as down.

Re: count total records and (number of records based on a condition) for same column in single sql query

Wendelius2-Feb-09 6:13

Re: count total records and (number of records based on a condition) for same column in single sql query

2-Feb-09 6:13

The need to optimize rises from a bad design.My articles[^]

Wendelius2-Feb-09 6:13

Re: count total records and (number of records based on a condition) for same column in single sql query

2-Feb-09 6:13

Not sure if Access accepts this, but have a try:

SELECT Employee_ID, 
       Employee_Name, 
       count (*) as [Total Days],
       (SELECT COUNT(*)
        FROM EmpTable e2
        WHERE e2.Employee_Name = e1.Employee_Name
        AND   Attendance_Date  BETWEEN ? AND ?
        AND   e2.Status        = 'P')
From EmpTable e1
Where Employee_Name   = ?
and   Attendance_Date between ? and ?
group by Employee_ID, 
         Employee_Name

Note, literals are replaced by parameters (read Colin's post).

The need to optimize rises from a bad design.My articles[^]

chandrubngit18-Feb-09 23:51

chandrubngit

18-Feb-09 23:51

Hi Mika Wendelius,

Thanks a lot for your help.Query executes Perfectly with the desired results.

sorry for late reply...

Re: count total records and (number of records based on a condition) for same column in single sql query

Wendelius19-Feb-09 5:54

Run single instance of an application

19-Feb-09 5:54

You're welcome Smile | :)

The need to optimize rises from a bad design.My articles[^]

ashok_rgm2-Feb-09 2:22

ashok_rgm

2-Feb-09 2:22

Hi all,

I have created windows application in c#, it is Sample.exe,
If this exe is already running, andmeanwhile if double click and initiate one more instance of Sample.exe it should point to already running Sample.exe.

Concept is simple if my windows media player is running say a movie file is being run.
and if I launch media player from shortcut it will still show the already running one instead of opening the separate media player.

Is it possible to do so for my application, how can I apply same logic to my Sample.exe

Thanks,

ashok

Re: Run single instance of an application

Wendelius2-Feb-09 3:01

Re: Run single instance of an application

2-Feb-09 3:01

Why post this on General Database forum instead of C# forum Confused | :confused:

However, try searching the articles. Plenty of good samples: http://www.codeproject.com/info/search.aspx?artkw=application+single+instance&sbo=kw[^]

The need to optimize rises from a bad design.My articles[^]

Colin Angus Mackay2-Feb-09 3:02

*Developer Day Scotland - Free community conference
*Colin Angus Mackay's Blog
*Latest Scottish Developers Newsletter

2-Feb-09 3:02

I don't see the connection with databases? You should be asking this in the C# forum.

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Remember that the force of gravity can go up as well as down.

Re: Run single instance of an application

ashok_rgm2-Feb-09 22:20

ashok_rgm

2-Feb-09 22:20

Sorry for the inconvinience I forgot to change the forum........ D'Oh! | :doh:

ashok

mcd and history table

foryou2-Feb-09 1:49

foryou

2-Feb-09 1:49

HI!!
Sory my english is bad!
I work on a topic of management school I created this mcd I wish someone Regad if it is right or not.

I was looking to create a history table for the table CAPITALISE_MODULE and CAPITALISE_SEMESTRE how to define the historical table should I linked to the other table?what are the identifiers.?
Thank you for read me!
Whay can i insert my file mcd? Blush | :O

Paddy Boyd2-Feb-09 2:03

Paddy Boyd

2-Feb-09 2:03

I'm sorry, but i really can't understand your question. I don't think that we can help you to define tables when you have only given us the table names, but no other information.

What is an mcd?

Colin Angus Mackay2-Feb-09 2:18

*Developer Day Scotland - Free community conference
*Colin Angus Mackay's Blog
*Latest Scottish Developers Newsletter

2-Feb-09 2:18

foryou wrote:
I was looking to create a history table

If I understand you correctly, the structure of your "history" (or "audit") table will be the same as the source table except you need to add some sort of versioning column in there also. That can be a simple incremented integer, or it can be a datetime, or something else that suits your needs.

To maintain the history you need to add triggers to the source table to copy any updates to the "history" table.

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Remember that the force of gravity can go up as well as down.

foryou2-Feb-09 2:58

foryou

2-Feb-09 2:58

Thank you very much Colin Angus Mackay and Paddy Boyd.
MCD is Conceptual data model.
whay can i send you my MLD?

MS-ACCESS combofilling [modified]

foryou2-Feb-09 23:24

foryou

2-Feb-09 23:24

HI!!
if my table history will be as my table source so my table history is: (id_student, id_module, id_validat_module, note)
or informations about students are in student table.
if I want to select students in their history with name_etudiants
I have to link the history table to student table?

Thanks

ketan bader1-Feb-09 20:02

ketan bader

1-Feb-09 20:02

Hi all...
I am developing an application completely in MS_ACCESS. I have my project and the Access database at different locations e.g C:\ & D:\ resp.
Now i want to fill the combobox with two Columns Srno, Name. Here "Srno" is Should be hidden Column.

Help me how would I do it..
Thanks & Regards..

modified on Monday, February 2, 2009 7:11 AM

Re: MS-ACCESS combofilling

Vimalsoft(Pty) Ltd1-Feb-09 23:06

Vimalsoft(Pty) Ltd

1-Feb-09 23:06

Why 2 Fields?

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswam@its.co.za

Re: MS-ACCESS combofilling

Blue_Boy1-Feb-09 23:09

Blue_Boy

1-Feb-09 23:09

Do you want to display two fields in same time in combobox?
If yes then use query like this select srno+' '+name as FiledforCmb from tablename

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

query with aggregate

rm_suprapto1-Feb-09 18:02

rm_suprapto

1-Feb-09 18:02

Hi all,

I'm suprapto, I would like to ask how to query the table below,
based on the highest salary of each department. The table's name is Employee

EmpName Dept Salary
------- ------- -------
Blee Technic 10000000
Mawar Technic 2000000
Suprapto Prod 1000000
Edo Prod 2000000
P Thomas Technic 6000000
P Hank Prod 5000000
Bu Putri HR 20000000
Franky Technic 3500000
Rahel HR 4000000

So, what I expect to obtain for the result is like below:

EmpName Dept Salary
------- ------- --------
Blee Technic 10000000
Bu Putri HR 20000000
P Hank Prod 5000000

Could anyone help me to solve this problem ?
Your kind help is much appreciated.

Rgards,

Re: query with aggregate

Wendelius1-Feb-09 18:08

1-Feb-09 18:08

For example something like:

SELECT *
FROM   Employee e1
WHERE  NOT EXITS (SELECT 1
                  FROM   Employee e2
                  WHERE  e2.Dept   = e1.Dept
                  AND    e2.Salary > e1.Salary)

The need to optimize rises from a bad design.My articles[^]

Re: query with aggregate

rm_suprapto1-Feb-09 23:58

rm_suprapto

1-Feb-09 23:58

The query is error, I'm working with SQL server 2005

Re: query with aggregate

Wendelius2-Feb-09 0:30

2-Feb-09 0:30

There seems to be a typo in the text. Instead of NOT EXITS write NOT EXISTS. If you get another error, please include the error text in the post.

The need to optimize rises from a bad design.My articles[^]

Unicode?

Ali_1001-Feb-09 9:23

Ali_100

1-Feb-09 9:23

what is the meaning of unicode in sql?

Re: Unicode?

Colin Angus Mackay1-Feb-09 12:30