Calculating Duration
Nov 10, 2004
HI! I am a university student taking a computer science course. I have created a database with multiple doctors and patients. Within the patients’ visits table I have included the two fields [Time Entered] and [Time Left] with values entered in these fields, for example, of 2:50:00PM and 3:30:00PM respectively. Within a query I am asked to calculate visit’s duration using MS Access Minute function. The expression I tried to use as a calculated field was:
Duration: (((hour ([Time Left])-hour ([Time Entered]) * 60) + (minute ([Time Left])-minute ([Time Entered])))
However, when I tried to run the query using this expression, an error message displayed which read “The expression you entered is missing a closing parenthesis, bracket (]), or vertical bar (|)”.
A fellow student told me to delete the first parenthesis and run the query again. I did this and the query did run, but returned vales of –820, -802, -455, etc for the duration of each patient’s visit. Can anyone please help me with the correct calculation??????
Marcella
View Replies
ADVERTISEMENT
Jun 26, 2005
I have a form with three fields:
CallStart
CallEnd
Cost
When a call is logged, I have it set that the time is entered automatically viz =now(). I would like it where if the cost = £20.00 the call end would be automatucally 30mins ahead. If the cost = £40.00 then the call end would be 1 hour ahead. And if the call costs £10.00 then the call end would be 15mins ahead.
Any help would be appreciated.
View 2 Replies
View Related
Jul 10, 2007
Hi to all,
I have a table of daily_outs with two columns date_out and date_in and below are my sample:
Table:daily_outs
line________date_out______________date_in_________ ___ duration(minutes)
1______#12/25/2006 12:26 PM#___#12/27/2006 13:26 PM#_____2,940.03
2______#12/26/2006 04:33 AM#___#12/27/2006 12:26 PM#_____1,913.00
3______#12/26/2006 01:16 AM#___#12/26/2006 15:25 PM#______849.03
4______#12/25/2006 01:05 AM#___#12/28/2006 15:27 PM#_____5,182.22
I would like to query the date_out=12/26/2006 00:00 AM and date_in=12/26/2006 11:59 PM. Below are the result I would like.
line________date_out______________date_in_________ ___ duration(minutes)
1______#12/26/2006 00:00 AM#___#12/26/2006 23:59 PM#_____1,439.98
2______#12/26/2006 04:33 AM#___#12/26/2006 23:59 PM#_____1,166.00
3______#12/26/2006 01:16 AM#___#12/26/2006 15:25 PM#______849.03
4______#12/26/2006 00:00 AM#___#12/26/2006 23:59 PM#_____1,439.98
Can anyone help me on this?
Thanks,
Corbitext
View 8 Replies
View Related
Mar 23, 2005
I have an import source that gives me lots of fields containing duration in format hh:mm:ss
This is a comma delimited txt file with headings and imports just fine, but I (thought) that i had to import the duration fields as text types....
Now, what can i do with that? I need to calculate (sum, average) on these fields, but can't. It seems a bit daunting (and stupid) to try and split these into separate hour, min and sec fields and use TimeSerial to put them back together. Do i have other choices?
It is not too late to import as a different format but here is a couple of lines of the source file:
Login ID,Date,ACD Calls,Avg ACD Time,Avg ACW Time,Extn In Calls,Avg Extn In Time,Extn Out Calls,Avg Extn Out Time,ACD Time,ACW Time,Agent Ring Time,Other Time,AUX Time,Avail Time,Staffed Time,PC Agent Occupancy w ACW,PC Agent Occupancy wo ACW
OrderCalls,01/01/2005,0,0,0,0,0,0,0,:00:00,:00:00,:00:00,:00:00,24: 00:00,:00:00,24:00:00,0,0
as you can see, leading zeroes are dropped.....
please help!
View 4 Replies
View Related
Mar 13, 2008
Hi all,
I am trying to get the formula to round to 15 seconds to charge rating events.:confused:
so 76 seconds will calculate up to 90 seconds..
& 91 will be 105 seconds.. and so on.
Any help greatly appreciated.
Thanks Dean
View 9 Replies
View Related
Oct 5, 2005
is it possible to count the time between records? example
Table set up:
LogID
StartTime
StopTime
ProblemCode
Example records:
Id -------> StartTime ------> StopTime--------> ProblemCode
1 -------->06/10/05 10:00 -> 06/10/05 10:02 -> A101
2 -------->06/10/05 11:00 -> 06/10/05 11:02 -> A131
i want to count the time elapsed between, ID 1 ending and ID 2 starting, can ido this?
if so how..
the duration i am looking for in this case would be 58 mins
cheers
Andy
View 2 Replies
View Related
Apr 15, 2012
what is the best way to record durations, not time?I want to have a table that records charging data such as below:
startDate
startTime
Duration
(hh:mm:ss)
15/04/2012
12:00:00
13:00:00
In this example you can see we started charging the device at 12pm on 15/04/2012 for 13 hours, from this I will be working out the endDate/time using dateAdd etc.Do I set the duration field up as a dateTime and if so how do I avoid the DB thinking that the field is a time and not a duration, meaning that the endDate would be 1pm on the 15/04/2012 instead of the correct 1am on the 16/04/2012?
View 6 Replies
View Related
Jun 20, 2007
I hv a access mdb which contains one column as "duration", data format as hh:mm:ss, e.g. 00:02:20 - 2 mins 20secs.
How could I calulate the average of the duration?
like sum of all duration fields / the ttl no of records.
View 1 Replies
View Related
Dec 22, 2014
I want to calculate decimal hours duration taken for a job starting before and finishing after midnight.
I have something that works but it seems cumbersome.
Hour(Format([Job]![Start_time]-1-[Job]![Finish_Time],"Short Time"))
+ (Minute(Format([Job]![Start_time]-1-[Job]![Finish_Time],"Short Time"))/60)
View 11 Replies
View Related
Nov 19, 2013
I developed a finance tracker database for tracking project revenues and costs (forecast and actual amounts). I have a form where we enter a new Work Order in the DB. This Work Order form has a continous subform where we create all the billing periods needed for the life of the Work Order. Once the billing periods are created (opened) we can then add our revenue and cost forecasts for each period. We have a Billing Period lookup table that has our billing periods with their respective start and end dates (which usually begin around the last week of a month and end about 3 weeks into the next month).
For the purpose of this question, lets say we only enter the required WO_Number and WBS_Code (Composite key) and the Work Order Start_Date and End_Date. I want to click a button to runs some code to automatically create all the billing periods for which their start and end dates fall into the Work Order start and end dates.
For example, say a Work Order starts on 5/1/2013 and ends on 7/31/2013
The billing period dates in the Billing Periods lookup table are as follows:
May-13 ---> 4/22/2013 - 5/19/2013
Jun-13 ---> 5/20/2013 - 6/23/2013
Jul-13 ---> 6/24/2013 - 7/21/2013
Aug-13 ---> 7/22/2013 - 8/18/2013
Then we would need the following billing periods created in the Work Order subform:
May-13
Jun-13
Jul-13
Aug-13
I don't even know where to start on this. Is it possible to automate this process with the setup I have? If so, how would I structure the VBA code/logic to use the billing period lookup table and create entries in the subform for all the billing periods that fall into the duration of the Work Order.
The attached DB is a stripped down version with only the tables and forms needed for this problem.
View 2 Replies
View Related
Aug 30, 2014
I am creating an access database for the results of my triathlon times and I am having trouble with the race results. Based on some information I found here, I am using number fields for the swim, bike and run times because I want to do calculations and also sort them and it doesn't sort properly if they are text.I have created separate fields for the hours, minutes and seconds for each of them.
Now I am trying to make a calculated field to convert the numbers to the following..For a swim time, I want to convert it to the time per 100m. I have the calculation for that, but the result gives me the decimal portion and not the actual seconds portion and I am stuck. Here is what I have so far :
Sw100m: (([SwMin]+([SwSec]/60)+[SwHr]/60)*100/750)
So as you can see I have added up all the number to get the total number of minutes and then converted to the minutes per 100m (the race is 750m). And the result gives me 3.31. But the .31 is a decimal and I want to have seconds which is 18 seconds. I know how to do the calculation on paper but I don't know how to change my formula to fix this. On paper I have to multiply .31 x 60. But how do I refer to the decimal portion of the number and modify my formula? how to input race times as duration in an access database.
View 11 Replies
View Related
Dec 20, 2013
I am trying to figure out how to take a table of timestamps and activity duration and break it into intervals, where it groups the duration into the time spent in each 15 minute interval. I have included a sample of the data and output. Is there any way to pull this in Access (or SQL)?
Raw Data
EmployeeActivityTimeStampStartTimeStampEndTotalDuration
Doe,JohnSomeActivity12/16/13 9:06:02 AM12/16/13 9:57:14 AM0:51:12
Smith,JaneOtherActivity12/16/13 9:22:15 AM12/16/13 10:06:55 AM0:44:40
Query to break out the total duration time in to the 15 minute interval it fell into
EmployeeActivityIntervalIntervalDuration
Doe,JohnSomeActivity12/16/13 9:00:00 AM0:08:58
Doe,JohnSomeActivity12/16/13 9:15:00 AM0:15:00
Doe,JohnSomeActivity12/16/13 9:30:00 AM0:15:00
Doe,JohnSomeActivity12/16/13 9:45:00 AM0:12:14
Smith,JaneOtherActivity12/16/13 9:15:00 AM0:07:45
Smith,JaneOtherActivity12/16/13 9:30:00 AM0:15:00
Smith,JaneOtherActivity12/16/13 9:45:00 AM0:15:00
Smith,JaneOtherActivity12/16/13 10:00:00 AM0:06:55
View 2 Replies
View Related
Apr 13, 2014
I am building a database to enter staff phone statistics. As an example my fields would be - Name, Date, Staffed time, Available time, Aux time and then calculated fields to show the percentage of time i.e %Aux, %Available etc.
My problem is the formatting of the times entered as they are duration not time. Say staffed time is entered as 08:00:00 for 8 hours and Aux time 03:57:21. The only format I can see to suit is date time but then Access takes these entries as 8am and 3:57am is there a way to change this to work as duration hh:mm:ss?
View 6 Replies
View Related
Apr 7, 2015
I'm new in access I working on DB for my company equipment
I used tables and queries and reached to the following query
Query name (02-By Project)
KSC_NO From_Project To_project Date_of_Trans
K-01-037 52500 12013 02/14/2014
K-01-037 12013 52500 03/31/2015
K-01-037 52500 12013 04/07/2015
K-01-129 52500 12013 03/03/2015
I want to make query to give me the following
KSC_NO IN_Date Out_date Duration (months)
K-01-037 02/14/2014 03/31/2015 13.6667
K-01-037 04/07/2015 Today 0.0333
K-01-037 03/03/2015 Today 1.2
Is that possible?
View 5 Replies
View Related
Dec 31, 2012
I have a table with 4 field which describe clips.
ClipName (txt)
ClipSize ( Long Integer)
ClipDuration (HH:MM:SS)
Date created (dd/mm/yy).
I would like to get the total of ClipSize and also total of Clip Duration either in table down or in query.
View 13 Replies
View Related
Feb 19, 2007
I have a table with 2 coloums with nuberin them. I would like to add those 2 number together and display them in a 3rd coloum. I need to automatically change the 3rd coloums value should any others change. Is this possible?
View 4 Replies
View Related
Jan 19, 2007
age: DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))Dob is stored yyyyddmmI thought this above function looks ok. When I run it, I get a data type mismatch in criteria expression.What am I missing?Any help would be appreciated.Thanks
View 6 Replies
View Related
Sep 20, 2005
hi all,
i have a mainform with a subform.
in the subform i have 2 fields: duur and subtotaal
in duur i enter a number.
in subtotaal a calculation has to occur: =20*([duur]/60)
i tried to do this calculation in VBA but then i got the same value on every record in the subform.
But now the problem:
in the mainform i have a control 'totaal' that needs to do a DSum of subtotaal.
And here i am getting into troubles since subtotaal is not stored in the table but calculated.
The formulla for DSum would be:
Me.Parent!totaal = DSum("subtotaal", "tblFacturenDetail", "[FactuurID] = " & Me.Parent!FactuurID)
but as said, subtotaal is not stored.
How should i proceed?
Thanks!
View 2 Replies
View Related
Jan 6, 2005
If I want to work out how old all my patients will be on the 01 Feb 2005 then why doesn't the expression (in a query)
=(01/02/2005 -[tblPatient]![DateOfBirth]))/365.25 work?
How would I get the answer expressed in months?
View 1 Replies
View Related
Jun 28, 2005
I am designing a new database for our life and disability department and they have several different policies where the base life insurance reduces at certain ages.
Please take a look at the scenarios listed below and give me any suggestions and tips on the best way to get this done. If you have any.
Below are the 12 scenarios:
1.) Reduces 75% at Age 70
2.) Age 65 to 70 the life amount is $9,000 and Age 70 and over the life amount is $6,000
3.) Reduces at age 65 by 92%, age 66 by 84.64%, age 67 by 77.869%, age 68 by 71.639%, age 69 by 65.908% and age 70 and over by 40%.
4.) On the day following the 70th birthday, the life amount will reduce to 70%. On the day following the 75th birthday the life amount will reduce to 40% of the 70th birthday reduced amount.
5.) On the Policy Anniversary Date following the 70th birthday - reduces 50%
6.) On the Premium Due Date following the 70th birthday, the life amount will reduce to 70%. On the Premium Due Date following the 75th birthday, life amount will reduce to 40% of the amount of the 70th birthday reduced amt.
7.) Age 65 life amount reduces to 65%. Age 70 life amount reduces to 35%. Age 75 life amount reduces to 25%.
8.) Terminates on the first of the month following the 65th birthday.
9.) Terminates Age 70
10.) Terminates on date of retirement or 1st or month following 70th birthday
11.) Terminates Age 65
12.) Never Reduces
Thanks for your time, Brian
View 2 Replies
View Related
Nov 5, 2006
I have a database containing values in 16 fields. the fields are filled in over a period of three years. I would like to be able to calculate the average of the last four values entered, regardless of when in the cycle the value is required. I have tryed to use quereies but connot find away to assign the four fields to the expression so that it is the last four values and if four don't exist, avearage what values there are.
View 2 Replies
View Related
Dec 21, 2006
Hi,
I am new to Microsoft Access (2002) and the Access Word Forums. I am in need of a way to calculate timespent on six main topics through an 8 hr. working day. If I worked on Topic "A" from 8:00am to 8:15am, I need it to calculate it to 15 minutes. Then I need to figure a way to calculate how much time was spent doing Topic "A" for the working day, then by the workweek, then by the month.
I'm willing to learn, if someone has the time to teach or can direct me to a link that discusses something similar to what I am looking for. Any and all suggestions welcomed. I am looking forward to doing this in a timesheet format if possible.
Thank you,
Marty6
View 2 Replies
View Related
Mar 17, 2008
Hi,
I think this should be simple, but I am struggling to work it out
In a relational database I have a stock table containing stock and quantities, there is a customer table with an order table used as the link table also containing quantities.
What I am trying to do is place an order using an order form linked to the order table, automatically reducing the stock table by the amount ordered.
I have tried an update query with no success, also tried using the builder to make a query and linked it to the stock table with no success.
I would be grateful for any ideas, I am sure it is relatively simple
View 3 Replies
View Related
Jun 15, 2005
Hi Guys,
New to the forum, and its nice to see how active this place is, and upto date.
My question is, and probably simple to correct. ( Please be patient with me, i`m new to this, and my terminology might be incorrect, also please simple answers )
I`ve searched the forums, but as my teminology is wrong I cant find the results im after :)
Anyway, I have a table with many fields in it, but the parts i`m having problems with are as follows.
I have fields with
Price Inc Vat, Deposit Payment, Stage Payment, Balance Payment & Balance Due.,
The part im trying to get to work is the balance due which I want to automatically update, based in the data inputed into price inc vat deposit payment etc.
The calculation i`m after is as followes
Balance Due = ( Total Ammount Inc Vat - Depost Payment - Stage Payment - Balance Payment )
eg, Total Ammount Inc VAt = £2000
Depost paid is £1000
so far balance due would be £1000
Then Final Payment is made 2 weeks later £1000
So balance due would then show £0.00
Is this possible
Thanks in advance
If I have not made myself too clear, please excuse my crapness, and anyhelp Is much appreciated.
If you need any more info, please do not hesitate to contact me on
max.vernon@claddagh-group.com
Thanks in Advance
Max Vernon
[edit]
Just to let you know, I also need these details kept in the table for future reference so I dont think I can use a query to do it.
I`ve attached a copy of the database with some example data in it if this is needed for any assistance.
View 8 Replies
View Related
Jul 17, 2006
I'm suppose to create a list of 20 clients and let 5 accounts be 90 days past due, 5 over 60 days, 5 over 30 days three of each of the accounts have a balance over $75. Then it asks me to create a total of all account balances so that the total amount of recievables can be known. The second part is calculate the number of days each balance has been outstanding.My question is this can you calculate dates in a table or can you only calculate in a query. I'm new at access so have patience with my question(s)
View 3 Replies
View Related
Feb 26, 2008
Hi I'm building database for a car shop. One of my table (Cars) has these two fields in it intModelPrice (which stores factory price of a model) and intPriceWithTax (which value should mathematicaly be intModelPrice+[(intModelPrice)*(18/100)]).
Now, can I set my default value of intPriceWithTax to be calculated by this formula and how? I tried some combinations but I got various error messages.
I red Tips section on your site and saw that default value can't be calculated. I know how to get what I want in the select query but I want this table field (intPriceWithTaxt) to be acctualy filled with number value each time I enter a new record in this table (for a new car model). How do I acomplish this? Thanx.
P.S. - I searched the forum for answer but I had no luck.
View 11 Replies
View Related
