|
 |
access_asp thread: Finding the maximum value in the database
Message #1 by Achimwene Dzida <dzidap@y...> on Fri, 12 Apr 2002 07:47:31 -0700 (PDT)
|
|
i have the following in the database in access
DATE MILAGE(km)
1/1/02 10:00 12300*
1/1/02 12:00 12360
1/1/02 12:30 12470
1/1/02 13:00 12480*
4/1/02 12:00 12490
6/1/02 12:30 12500
6/1/02 12:30 12570
The figures could go up to end of month. i want to
find out which date of the month has the highest
milage e.g. for 1/1/02 i have to subtract 12480 -12300
to get the km travelled on this day. The same can be
done to other dates and find out from thee which is
the highest date of travelling:
i have made my connections quite alright
here my code
--------------------------------------
dim temp
temp = 1
'
Set con = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")
myconnstr = "DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=D:\Programme\OsCAR FB v1.4\db.mdb"
con.open (myconnstr)
mysql = "SELECT *from client"
rs.open mysql,con,2,3
'rs is the recordset
if not rs.eof then
for i = 1 to 31
anzahl = 1
do while day(rs("date") = i
if anzahl = 1 then
km = rs("kilometer")
else
rs.movenext
end if
loop
kma = rs("kilometer")
end if
diff = kma -km
if temp < diff then
temp = diff
end if
next
response.write "The maximum is " & temp
end if
when i run this course, it is executed indefinitely
and it does not give the results at all. sometimes it
gives an error that the statement rs("kilometer")
requires actual dataset. What does it mean?
How can i write this code better so that it gives me
the results i want.
thanx in advance for your assistance
__________________________________________________
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/
Message #2 by "Ethan Selzer" <ethanselzer@a...> on Fri, 12 Apr 2002 13:21:37 -0700
|
|
Hi,
One possible solution is to format the output in your SQL statement. Here is
an example.
SELECT TOP 1 Travel_Date, (Max(kilometer) - MIN(kilometer)) AS
Total_kilometer
From client
Group by Travel_Date
ORDER BY (Max(kilometer) - MIN(kilometer)) DESC
One suggestion is to change your database column name from "Date" to some
other name as Date is a reserved word.
Good Luck,
Ethan
-----Original Message-----
From: Achimwene Dzida [mailto:dzidap@y...]
Sent: Friday, April 12, 2002 7:48 AM
To: Access ASP
Subject: [access_asp] Finding the maximum value in the database
i have the following in the database in access
DATE MILAGE(km)
1/1/02 10:00 12300*
1/1/02 12:00 12360
1/1/02 12:30 12470
1/1/02 13:00 12480*
4/1/02 12:00 12490
6/1/02 12:30 12500
6/1/02 12:30 12570
The figures could go up to end of month. i want to
find out which date of the month has the highest
milage e.g. for 1/1/02 i have to subtract 12480 -12300
to get the km travelled on this day. The same can be
done to other dates and find out from thee which is
the highest date of travelling:
i have made my connections quite alright
here my code
--------------------------------------
dim temp
temp = 1
'
Set con = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")
myconnstr = "DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=D:\Programme\OsCAR FB v1.4\db.mdb"
con.open (myconnstr)
mysql = "SELECT *from client"
rs.open mysql,con,2,3
'rs is the recordset
if not rs.eof then
for i = 1 to 31
anzahl = 1
do while day(rs("date") = i
if anzahl = 1 then
km = rs("kilometer")
else
rs.movenext
end if
loop
kma = rs("kilometer")
end if
diff = kma -km
if temp < diff then
temp = diff
end if
next
response.write "The maximum is " & temp
end if
when i run this course, it is executed indefinitely
and it does not give the results at all. sometimes it
gives an error that the statement rs("kilometer")
requires actual dataset. What does it mean?
How can i write this code better so that it gives me
the results i want.
thanx in advance for your assistance
__________________________________________________
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/
|
|
|
View Cart
