Queries :: Breaking Out (Timestamp And Total Duration) Into 15 Minute Interval?
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 Replies
ADVERTISEMENT
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 20, 2014
Let's say I have a StartDate: 02/02/2014 10:00 and EndDate: 01/30/2014 15:00
Is it possible to have this result in a query?
View 12 Replies
View Related
May 24, 2014
I need to show the interval between dates. The dates are all held in the same field.
A/CDate
52601-Mar-14
52601-Feb-14
52601-Jan-14
View 5 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
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
Jul 23, 2015
I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
View 9 Replies
View Related
Mar 8, 2008
Hi,
1) I am pretty newbie to this access programming, do forgive me if my questions sounds stupid.
2) Basically I create an application in access capturing or production information for my company. now the top management suddenly wanted whats their main concern:- Total Daily/Monthly, Quarterly, Annual Sales (By Model If possible)
3) I start with daily (Lets don't be too overly ambitious).
4) I try to let user select dates from my calender control and reflect daily sales (in Total & By Model break down) insert into my form.
5) Understand someone told me from my previous post in Calender control I can achieve it either through forms or queries, which is a better way. (in terms of flexibility to change for program maintenance/ scalibility) wise ?
PS: Please forgive my ignorance :o:(
Thanks (In advance) & God Bless.
View 2 Replies
View Related
Feb 17, 2015
I have a table that records log entries for equipment and I'm trying to compose a query that calculates the time difference between consecutive timestamps for individual operators but can't quite get it to work out
The table headings are
Fatigue Log ID <----- this is the primary key for the table and is unique
Timestamp
Operator ID
View 4 Replies
View Related
Aug 1, 2006
Hi
I want reduce one minute from my date field. My date field looks like this: 17.3.2006 3:57:00 and I want it to look like this:17.3.2006 3:55:00. I have tried to make it like this: DATEADD( minutes, -2, [StartTime]) AS NewStart. StartTime is date field. But this doesent work. Could somebody tell me how to reduce one minute from date field??
View 2 Replies
View Related
May 6, 2014
I have several pieces of equipment that register data every minute, is there a way to do like a 5 minute average
i.e., 00:00 - 00:04, 05:00 - 09:00
NOT 00:00- 05:00, 01:00- 06:00 and so on.
View 1 Replies
View Related
Jun 22, 2014
i have a problem i have field contains (minutes as number) like 5750 i want to convert thes minutes to short time hh :nn to be 95:50
View 3 Replies
View Related
Aug 19, 2004
I am using a Access database with a frontend/backend design. I want to keep a table in the BE updated with the current list of users inside of the frontend. To do this I thought that I would just have the front end update a time field saying that that user is still inside of the FE. My question is two-fold - firstly, how can I run a function every minute or so to update this table.
Secondly - is there a better way to do this?
Thanks in advance for any help you can offer.
View 3 Replies
View Related
May 19, 2015
I have to write a code in a form so that if nobody is doing any activity for 5 minutes then after 5 minutes automatically press Close button named BtnClose in that form.
View 5 Replies
View Related
Jan 18, 2007
I want to take a report which covers sales figures and e-mail to those people that have had sales in the period. I only want each person to get just their records, not the whole report. Any ideas how I can do it automatically, starting with a query of records or a report ? ?
I know how mto e-mail a standard report using a macro !!
View 1 Replies
View Related
Mar 23, 2006
Hello all,
I need your help! Iam breaking my head for a day almost to solve this problem.
This is a timesheet application in MS Access in which I have a MS Access table with the following columns:
1. PersonID
2. SkillID
3. EntryDate
4. MON
5. TUE
6. WED
7. THU
8. FRI
The work hours for a person is entered once a week, at the end of the week. The person's SkillID can change but not in the same week. For ex a person scheduled as a Welder has to work as Welder for that week.
So, I need to find now all the PersonIDs with more than one SkillID in a week and flag them as errors.
Pls check the attached image. The first entry with EntryDate as "06-May-05" and Person_Code as "MK0259" repeating with 3 different Skill Codes. Then this is a problem which I want to hight light. Hope I explained clearly.
Can someone help please? Desparate!
Thanks in adv.
sgmuser!
View 2 Replies
View Related
Nov 3, 2006
Ok I have heard many times that you should never save a calculated field in a table. I know this is breaking the rules but can someone tell me how to save a specific combo value change to a table? and avoid all the rest?
Here is my problem, I was asked to have a combo box that has 5 choices and when the default is changed to another choice, I must have the date saved for reference. But the date is only supposed to be saved when a specific option in the box is choosen. Such as
Default: basketball
was changed to football
I can do a timestamp but don't know how to change it when a specific option is selected. It just changes when it is updated , even when I change it to baseball
Im actually saving it but can't annoy the other options...
Can someone help me out here PLEASE
View 1 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
Apr 20, 2006
I have a query that shows a list from GPS software (car fleet management), unfortunately the software is not capable of doing a decent reports thus I'm trying to use its data and do it myself.
The table structure is as follow:
Car, Date, Time, Position.
The time interval I have now is 1 minute (short time format), I don't need it so detailed, that's why I was thinking to limit this into 5 minutes interval.
Is it possible?
Thanks for any suggestions.
View 1 Replies
View Related
Apr 26, 2007
I have a report - I have a agent header and code header.
It should look like this:
Jeff Moenning Agent Header
1-Active Code Header
Report Details
For some reason the first page just has Jeff Moenning and nothing else - after that the pages are correct. They are breaking when the agent changes and also when the codes change within the agents.
View 3 Replies
View Related
Feb 5, 2008
Hi All,
I have a field with a bunch of records. All is formatted the following:
CAN-2007-US-00001
CAN-2007-US-00002
CAN-2007-US-00003
CAN-2008-US-00001
CAN-2008-US-00002
CAN-2008-US-00003
and so on and so forth. Is there a way Acess can break the fields out? I want 1 field with CAN, 1 field with the year, 1 field with US, and 1 field with the number (4 different fields broken by the hyphen).
I can do text to column in excel before I import, but just wondering if it can be done in Access. Thanks in advance, guys...
Caliboi
View 10 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
Feb 8, 2007
How would I find if 2 dates fall between 2 other dates?
For example:
This would evaluate to True
StartDate 02/08/2007
EndDate 02/11/2007
StartTestDate 02/09/2007
EndTestDate 02/10/2007
This would also evaluate to true
StartDate 02/08/2007
EndDate 02/11/2007
StartTestDate 02/07/2007
EndTestDate 02/12/2007
This would evaluate to false
StartDate 02/08/2007
EndDate 02/09/2007
StartTestDate 02/10/2007
EndTestDate 02/11/2007
View 5 Replies
View Related
Dec 19, 2007
I am attempting to create a report that breaks down a field of 'ClaimID' numbers into groups of x. In the sample report below x = 12 and the report will apportion the first 12 'ClaimID's to the first page and textboxes with extra large fonts will signify the start and end of 'ClaimID' numbers for that page. These sheets are used for sorting and pulling guides at our local Xmas project and x will vary depending on the size of the facility we're using.
http://i35.photobucket.com/albums/d186/HoodRiverDude/SortReport.gif
I believe I can attain my goal if I were able to create a query which broke down the field 'ClaimID' into multiple fields based on x. The sample below represents this breakdown creating multiple records with x, 4 and 5, amount of fields.
http://i35.photobucket.com/albums/d186/HoodRiverDude/SortReport2.gif
Does anyone know how I can create this query, or perhaps suggest another way to create this report using the existing 'ClaimID' data.
Thanks in advance,
Aldo
View 2 Replies
View Related
