sum Query in Access - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sum Query in Access

Author

Topic

ragh
Starting Member

34 Posts

Posted - 2005-02-14 : 03:57:00

Hi,
I got some rows like :

SNo ID Name Date TimeIn TimeOut TWH Duration
1 0005 Ragh 01/31/2005 15:32 15:36 240 00:04
2 0005 Ragh 01/31/2005 15:38 15:39 60 00:05
3 0005 Ragh 01/31/2005 15:40 16:01 1260 00:26
1 0005 Ragh 02/01/2005 09:00 09:01 60 00:01
1 0006 ASDF 02/01/2005 09:00 09:01 60 00:01

Now, i want to take only the maximum duration data and delete rest of them for that particular date and a particular ID.
The SNo is increased accordingly to ID and to Date. so the MAX(SNo) will be the total duration for that particular date.

How can i achieve this Query, i want to display a group report in VB Data reports.

Kindly help me !!!

Ragh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-14 : 04:20:22

Delete from Table where Sno not in (Select Max(Sno) from Table where Sno>1 group by ID,Date)

Madhivanan

ragh
Starting Member

34 Posts

Posted - 2005-02-14 : 04:53:56

Nooooo....
I want to take that SNo 3 and then porceed to SNo 1 of next date and so on...
there can be SNo = 1 also per date for ID. so what to do ?

Ragh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-14 : 05:22:32

Try this

SELECT * FROM table T
WHERE SNo in ( Select max(SNo) from table where id=T.id and Date=T.Date group by Sid, Date)

Madhivanan

ragh
Starting Member

34 Posts

Posted - 2005-02-14 : 05:24:19

Like this i want in my VB Reports....
SELECT id,date,name,twh
FROM grtable
ORDER BY id,date,twh
COMPUTE SUM(twh) BY id,date
id date name twh
----- ---------- ----- -----------
0005 2005-01-31 Ragh 60
0005 240
0005 1560
sum
===========
1860

0005 2005-01-31 Ragh 60
sum
===========
60
COMPUTE BY Doesnt work in ACCESS VB :(

Ragh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-14 : 05:37:15

SELECT id,date ,name,twh, (Select sum(amount) from grTable where id=T.id and date=t.date group by id,date) FROM grTable AS T
WHERE SNo in ( Select max(SNo) from grTable where id=T.id and date=T.date group by id,date )

Madhivanan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources