Forums
This topic is locked
Adding times stored in an access DB...DMX2004
Posted 01 May 2004 17:26:44
1
has voted
01 May 2004 17:26:44 Richard Georgiou posted:
Hi DMX Zone,I'm in the porcess of creating a 'virtual airline' using Dreamweaver MX2004 and Access for the databases. The site structure and coding is nearly complete except for one element I'm really having a problem with...
I've got a database called flights that contains details of completed flights... In this database there is a field called flightduration that contains the length of the flight (01:25 for example), and another called userid...
I've also got an active session variable called MM_Username that contains the logged in userid...
What I'm trying to do is to create the recordset SQL so I can put the total flight time of all the flights for a particular user onto the screen... I however am having real difficulty with the following two problems...
1). How do I total up the flightduration from all the records in the flights database that are for the current user?
2). How do I go about adding up times as they're not decimal values but time values?
I'm really hoping it's not too difficult as I'm not that much of an expert when it comes to ASP/Javascript and SQL!!
Hope someone can help.
Thanks
Richard Georgiou
Edited by - rgeorgiou on 01 May 2004 17:30:02
Edited by - rgeorgiou on 01 May 2004 22:27:06
Replies
Replied 01 May 2004 18:42:01
01 May 2004 18:42:01 Vince Baker replied:
Hi Richard,
Right this is not an easy one, I have searched for formula or sql commands to do this but in access with no luck. So, I have coded part sql part asp to do what you need..
The problem is that time/date fields hold values as decimals of days (1/24 of a day is an hour, 1/1440 of a day is a minute etc).
You MUST have the field in the database for the time set to date/time for this to work.
here is the code from a test page, just copy this to a page and then you will see which bits you need to change (field names, connection name etc).
Basically, you just need to place the following line of code where you want the total to be displayed:
<%=strTotalTimeNorm%>
<%
Dim Recordset1
Dim Recordset1_numRows
Set Recordset1 = Server.CreateObject("ADODB.Recordset"
Recordset1.ActiveConnection = MM_Connection name needed here_STRING
Recordset1.Source = "SELECT (sum(datepart('h',test_time) + (datepart('n', test_time))/60)) as result_in_hours_dec FROM testing"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>
<% Dim arrDecTime, strHours, strMinsInDec, strMinsInNorm, strTotalTimeNorm
arrDecTime = Split(formatnumber(recordset1("result_in_hours_dec",2), "."
strHours = arrDecTime(0)
strMinsInDec = "0." & arrDecTime(1)
strMinsInNorm = formatnumber((strMinsInDec*60),0)
strTotalTimeNorm = strHours & ":" & strMinsInNorm
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<%=strTotalTimeNorm%></body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
Regards
Vince
Visit my home: www.chez-vince.com
VBScript | ASP | HTML | SQL | Oracle | Hosting
Right this is not an easy one, I have searched for formula or sql commands to do this but in access with no luck. So, I have coded part sql part asp to do what you need..
The problem is that time/date fields hold values as decimals of days (1/24 of a day is an hour, 1/1440 of a day is a minute etc).
You MUST have the field in the database for the time set to date/time for this to work.
here is the code from a test page, just copy this to a page and then you will see which bits you need to change (field names, connection name etc).
Basically, you just need to place the following line of code where you want the total to be displayed:
<%=strTotalTimeNorm%>
<%
Dim Recordset1
Dim Recordset1_numRows
Set Recordset1 = Server.CreateObject("ADODB.Recordset"
Recordset1.ActiveConnection = MM_Connection name needed here_STRING
Recordset1.Source = "SELECT (sum(datepart('h',test_time) + (datepart('n', test_time))/60)) as result_in_hours_dec FROM testing"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>
<% Dim arrDecTime, strHours, strMinsInDec, strMinsInNorm, strTotalTimeNorm
arrDecTime = Split(formatnumber(recordset1("result_in_hours_dec"
,2), "."strHours = arrDecTime(0)
strMinsInDec = "0." & arrDecTime(1)
strMinsInNorm = formatnumber((strMinsInDec*60),0)
strTotalTimeNorm = strHours & ":" & strMinsInNorm
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<%=strTotalTimeNorm%></body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
Regards
Vince
Visit my home: www.chez-vince.com
VBScript | ASP | HTML | SQL | Oracle | Hosting
Replied 01 May 2004 22:26:41
01 May 2004 22:26:41 Richard Georgiou replied:
First of all I'd like to say a big thank you to Vince for replying with so much info...!
BUT...
Sorry but I really didn't understand how to incorperate this into my page. My page includes 2 recordsets for various other stuff and the code view scares the s**t out of me!!!
So... I've decided to use just minutes instead of hours and minutes... This makes life so much easier BUT I still can't seem to get this working??!?!?!?!?!?
I think I need to use the sum() function in my recordset but I don't know where to put it...
Here's the advanced recordset sql at the moment
SELECT *
FROM Table1
WHERE userid = 'MMColParam'
VARIABLES
MMColParam = Session("MM_Username"
The above successfully returns all records for a particular userid but I need it to add up all the minutes in a field called 'FlightDuration' and then put it on the screen.
Sorry to be a thick pain but... that's who I am!!
Thanks again for your time... Oh, and I took a look at your web site (Chez-Vince). Lovely place you live....
Richard Georgiou
BUT...
Sorry but I really didn't understand how to incorperate this into my page. My page includes 2 recordsets for various other stuff and the code view scares the s**t out of me!!!
So... I've decided to use just minutes instead of hours and minutes... This makes life so much easier BUT I still can't seem to get this working??!?!?!?!?!?
I think I need to use the sum() function in my recordset but I don't know where to put it...
Here's the advanced recordset sql at the moment
SELECT *
FROM Table1
WHERE userid = 'MMColParam'
VARIABLES
MMColParam = Session("MM_Username"
The above successfully returns all records for a particular userid but I need it to add up all the minutes in a field called 'FlightDuration' and then put it on the screen.
Sorry to be a thick pain but... that's who I am!!
Thanks again for your time... Oh, and I took a look at your web site (Chez-Vince). Lovely place you live....
Richard Georgiou
Replied 01 May 2004 22:34:12
01 May 2004 22:34:12 Vince Baker replied:
No worries, I was actually going to sugest this as it is a much simpler solution:
change your recordset:
SELECT *
FROM Table1
WHERE userid = 'MMColParam'
to the following
SELECT (sum(datepart('h',insertyourfieldnamehere)*60 + (datepart('n', insertyourfieldnamehere))) as Total_Minutes
FROM Table1
WHERE userid = 'MMColParam'
change where it says insertyourfieldnamehere to the name of your time field.
this will give you a field called Total_Minutes in this recordset that will show a total of the flight times in minutes
Regards
Vince
Visit my home: www.chez-vince.com
VBScript | ASP | HTML | SQL | Oracle | Hosting
change your recordset:
SELECT *
FROM Table1
WHERE userid = 'MMColParam'
to the following
SELECT (sum(datepart('h',insertyourfieldnamehere)*60 + (datepart('n', insertyourfieldnamehere))) as Total_Minutes
FROM Table1
WHERE userid = 'MMColParam'
change where it says insertyourfieldnamehere to the name of your time field.
this will give you a field called Total_Minutes in this recordset that will show a total of the flight times in minutes
Regards
Vince
Visit my home: www.chez-vince.com
VBScript | ASP | HTML | SQL | Oracle | Hosting
Replied 01 May 2004 22:45:36
01 May 2004 22:45:36 Richard Georgiou replied:
Chez! - What can I say... You're fast...
I've actually changed the field type in the database to a plain old number field. Does this mean I could get rid of some of the date part bits?
Thanks again
Richard Georgiou
I've actually changed the field type in the database to a plain old number field. Does this mean I could get rid of some of the date part bits?
Thanks again
Richard Georgiou
Replied 01 May 2004 22:52:50
01 May 2004 22:52:50 Richard Georgiou replied:
The field name is actually "Flight Duration"... Does it matter that there's a space in there?
Thanks again
Richard Georgiou
Thanks again
Richard Georgiou
Replied 01 May 2004 23:35:08
01 May 2004 23:35:08 Vince Baker replied:
shouldnt do, but sometimes when you start to code spaces can cause problems...best to avoid them from the beginning but for this instance there should be no problem...
always better to seperate words with and underscore (like_this)
Regards
Vince
Visit my home: www.chez-vince.com
VBScript | ASP | HTML | SQL | Oracle | Hosting
always better to seperate words with and underscore (like_this)
Regards
Vince
Visit my home: www.chez-vince.com
VBScript | ASP | HTML | SQL | Oracle | Hosting
Replied 01 May 2004 23:39:47
01 May 2004 23:39:47 Vince Baker replied:
just read your first reply, you could remove the datepart bits if you do this...
couple of points though,
1. Make sure you only enter the flights in minutes into the db, no seperating hours and mins....
2. Replace the first line of the sql with
select sum(flight duration) as Total_Duration
and you are good to go.
Personally I would keep the input as hours and mins (easier) and change the field to date.time and use the sql i gave you with the datepart in it....easier for you to insert but your call.
Regards
Vince
Visit my home: www.chez-vince.com
VBScript | ASP | HTML | SQL | Oracle | Hosting
couple of points though,
1. Make sure you only enter the flights in minutes into the db, no seperating hours and mins....
2. Replace the first line of the sql with
select sum(flight duration) as Total_Duration
and you are good to go.
Personally I would keep the input as hours and mins (easier) and change the field to date.time and use the sql i gave you with the datepart in it....easier for you to insert but your call.
Regards
Vince
Visit my home: www.chez-vince.com
VBScript | ASP | HTML | SQL | Oracle | Hosting
Replied 02 May 2004 16:47:28
02 May 2004 16:47:28 Richard Georgiou replied:
HI Chez-Vince,
I just wanted to leave a quick message to say "You're the man!"... I've now got the field type set to date/time, and the Flight Duration is asked for in hours and minutes (hh:mm). Then when the Pilot Statistics is displayed it displays the total in minutes... Most satisfactory! I've also taken the space out of the field name as it caused issues...
Thanks again for your time and pateints Chez.
I'll let you know in about a month when the site is up and running and you can take a look at your own work!! (hehe)
Richard Georgiou
I just wanted to leave a quick message to say "You're the man!"... I've now got the field type set to date/time, and the Flight Duration is asked for in hours and minutes (hh:mm). Then when the Pilot Statistics is displayed it displays the total in minutes... Most satisfactory! I've also taken the space out of the field name as it caused issues...
Thanks again for your time and pateints Chez.
I'll let you know in about a month when the site is up and running and you can take a look at your own work!! (hehe)
Richard Georgiou