Count Duration Between Records
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 Replies
ADVERTISEMENT
Apr 13, 2006
I have a table tblBookings.
In this table it has a bookingID, CustomerID and some other none relevant details.
The CustomerID comes from table tblCustomer. i.e a customerID must exist in the customer table to be allowed in the bookings table tblBookings
A customer can exist in tblCustomer without existing in the booking table.
I am trying to write a query that will list each and every customer ID in the tblCustomer and count the number of bookings that that customer has (even if it is zero).
I have a query that will count the bookings if they exist in the booking table and display the number of times that a customer appears in the bookings table.
SELECT tblBookings.CustomerID, Count(tblBookings.CustomerID) AS NoOfBookings
FROM tblBookings
GROUP BY tblBookings.CustomerID;
How do I create a query that will do this but list all customers even if they don't exist in the bookings table (but obviously occur in the customers table)
I am trying to create a similar query where all bookings per hotel are listed even if no bookings are made for that hotel. I am guessing the answer is the same as above.
The Ritz. Bookings 0
The Hilton. Bookings 3
The Carlton. Bookings 0
The Lowry. Bookings 2
For every hotel.
That kind of thing.
If you need more information please shout.
View 3 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
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
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 2 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
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
Jan 23, 2007
I have created a query that returns x number of records. How can i count this number (x)?
View 1 Replies
View Related
Feb 1, 2005
I have a field on my Project form(form is based on a query) which is called Completed (Y/N). It holds values Y and N.
I’m wondering if there is a way to count how many Projects completed and how many didn’t. I’m not sure how to count just Ys and then just Ns.
Any help would be appreciated.
Thank you!
View 8 Replies
View Related
Nov 9, 2006
HI there
How to set up textbox which will count how many records is already in my table and add one more and display that in my Form????
cheers
Natal:)
View 2 Replies
View Related
Jan 26, 2006
I have "Age" field from 10-100 years old. My problem is I want to count how many are young(21 below), middle aged(22-45), and old(46 above) in a report. How am I going to write it in a textbox? Please help me and teach me step by step how.
View 8 Replies
View Related
Dec 10, 2007
I have a table with quite a number of records.
In this table I have a field [Qnumber], which gives me a sequential number.
The field size is Long Integer. So my records are numbered 1, 2, 3, ....n
What I want to achieve is to select only the even numbered records in this table, i.e. Qnumber = 2, 4, 6, ...... etc.
I can't find a way to do this.
Would appreciate any help on this.
View 3 Replies
View Related
Jun 24, 2005
Yes i'm having trouble with a Count problem, don't laugh at me.
I've got a query with 200000 plus records. Each record has an equipment ID number (its not the primary key) in column B. I just want to count up the number of records that have each equipment ID code, but im not sure how to do it.
Please help
View 2 Replies
View Related
Mar 27, 2006
I have an order database using Access 2000, I need a report to list all agents that have ordered in the past year, how many times they ordered and sort by Office city.
I have
SELECT tblCustomers.Type, tblAgentList.AgentID, tblAgentList.LastName, tblAgentList.FirstName, tblAgentList.Agency, tblAgentList.AgencyCity, tblAgentList.DeliveryAddress, tblAgentList.DeliveryCity, tblAgentList.DeliveryState, tblAgentList.DeliveryPostalCode, tblAgentList.[AgentOffice#], tblCustomers.InspectionDate
FROM tblAgentList INNER JOIN tblCustomers ON tblAgentList.AgentID = tblCustomers.AgentID
GROUP BY tblCustomers.Type, tblAgentList.AgentID, tblAgentList.LastName, tblAgentList.FirstName, tblAgentList.Agency, tblAgentList.AgencyCity, tblAgentList.DeliveryAddress, tblAgentList.DeliveryCity, tblAgentList.DeliveryState, tblAgentList.DeliveryPostalCode, tblAgentList.[AgentOffice#], tblCustomers.InspectionDate, tblCustomers.[Job#]
HAVING (((tblCustomers.Type)="P") AND ((tblCustomers.InspectionDate)>[Enter starting Date:]))
ORDER BY tblAgentList.AgentID, tblAgentList.AgencyCity, tblAgentList.AgentID;
It list all agents sorting by AgentID, with a line for each order.
I would like to list each agent once with the number of times ordered, and then sort by AgentCity. Any help would be appreciated... Thanks in advance!:D
View 3 Replies
View Related
Jun 14, 2006
Hi,
I've been going round in circles a bit with this, read a lot of interesting stuff posted, but can't quite answer my problem.
I have a query that returns 2012 records showing the project phase for every Employee assignment. How do I group & count the records so that I get the number of projects in each phase for each employee?
My group and count attempts so far have either reduced the record set to 79 (the number of employees with assignments) or simply returned a "1" for each record.
Any pointers appreciated.
Regards,
View 2 Replies
View Related
Jul 29, 2006
Hi
Been taking abreak from Access for a few years now and cant get my head round my problem.
I have been asked to manipulate data from a Training Recods database.
Basically, training consists of 26 modules, there are currently 180 people who need training. Each person has been assigned a unique number, once a module has been completed, the date it was completed is entered into the database. There are currently lots of modules which havent been completed, what I need is a way of pulling that data from the DB.
I need to know how many people need to complete each module. For example, Module 1 might need to be completed by 15 people, Module 2 might need to be completed by 27 people etc.
I have enclosed the DB with the table and the form that I am looking to populate with the data.
Anyone have any ideas, I just can't get my head round it!!!!
View 4 Replies
View Related
Sep 15, 2006
Hello, I am new to Access and trying to run a query that will count only unique invoice numbers in my table. I am sure this is an easy command, and thank you for your help!
View 3 Replies
View Related
Feb 16, 2007
Hi guys,
I know how to count records so that null records would be counted but I do not know how to do this:
I have customers and works. I want to create a query that will show ALL customers and number of works done for that customer (sometimes there is no work done for customer).
How can I do it?
Thanks,
Matt
View 2 Replies
View Related
