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
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 :
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.
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..
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 ?
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
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??
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.
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.
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 !!
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.
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...
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.
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.
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.
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...
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
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.
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:
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??????
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.
