Queries :: How To Input Race Times As Duration In Access Database
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 Replies
ADVERTISEMENT
Jan 5, 2014
I have a race league, I want to select the top 10 point scores for each member.
I have read the Allen Browne article (and many others) and tried many variations on his code but cannot get this working.
I face two issues
- The ORDER by clause has no effect, points are not sorted with largest first
- Access being unable to differentiate between scores with the same value and returning additional records. I have added an "Event" field to make the record unique, but this does not seem to work.
Query code is
SELECT qLeague.Member, qLeague.Event, qLeague.Points
FROM qLeague
WHERE qLeague.Points IN
(SELECT TOP 10 Points
FROM qLeague AS Dupe
WHERE Dupe.Points= qLeague.Points
ORDER BY Dupe.Member, Dupe.Points DESC
)
ORDER BY qLeague.Member ASC, qLeague.Points;
This returns more than 10 results per member:
Member Event Points
Alex Peters SDMC North Weald Sprint 3
Alex Peters HCAAC Debden May 3
Alex Peters GB/Harrow TAMS NW Sprint 4
Alex Peters HCAAC Debden Sprint 5
Alex Peters Llys y Fran Hillclimb 6
etc ....
View 10 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
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
Jul 23, 2013
how to input alarm in my access database? I have data that should be insert over and over in 4 hours duration, when I put first data, I get local time and I need timer that could count 4 hours and after to make notification (massage) that the time for the Product of the database are over and need to input clean Product.
something like in first-->
ProductTime is column from Access Database
i=j=ProductTime;
for (i=0;i>= j+4hours, i++)
{
If(i=j+4h)
{
msgbox("ALARM- TIME is OVER");
}
}
View 1 Replies
View Related
Aug 6, 2013
How do I allow colleagues to input data to the Access database, but at the same time prevent meddling with the database design? Is it possible to password protect some functions but allow others (students/ nurses!) to input data?
View 3 Replies
View Related
Dec 3, 2013
I have an App that runs a few action queries using:
Code:
CurrentDb.Execute "[My Query Name]"
At some point I get this Error: The Microsoft Access database engine cannot find the input table or query <name>. Make sure it exists and that its name is spelled correctly. (Error 3078).The query is there, I can run it from the DB objects window.Queries run using CurrentDb.Execute earlier in the code.
View 3 Replies
View Related
Nov 9, 2005
Hi,
I need to calculate to times in a data base
Table: Times
leaving time 07:30
Interval 02:30
I did this calculation in a query: Time: [Times]![leaving time] - [Times]![Interval]
Both fields are in SHORT TIME
When I run the query I get: Time = 0.208333333333333
What am I doing wrong????
Please help!
Thanks
Ean James
View 2 Replies
View Related
Jan 17, 2006
Is there an easy way of doing this? I have a database that I want to automatically place racers as they finish. I have four race divisions. All racers no matter the division start at the same time. I have all of this figured out, but what is getting me is the finish place (1st, 2nd, etc.) Is there a way on a continuos form to automatically place the racers? How this works is...Main form has racer info., on this form is a stop button, and after the button is clicked, it will show his/her finishing place in their division which is subform of the main form. Here is the reason I want this. Someone may have registered wrong and they are in the wrong division, and we have to change them to the correct division after they have finished and already been placed. I want to be able to move them to the right division, and then my subform requery and they placed in the correct position in the new division. Sorry this is so lenghty. Plain and simple can I make a subreport have an unbound field that shows each records place the recordset? Thanks for your help, and like I said sorry this is so long.
View 4 Replies
View Related
Mar 31, 2013
I am creating a DB for race results and have a field which calculates the elapsed time based on the start and finished times. What I want to do now is populate a field "position" with the finishing position relative to other competitors elapsed times in the race. But I cant see how to do this.
I understand I would probab;y have to use some sort of query but not sure how to create it.
View 2 Replies
View Related
Nov 22, 2013
I work for a chemical company, over the course of the day there are 2 shifts, one from 07:00 - 19:00 and the other from 19:00 - 07:00. I want to measure the amount of time a piece of equipment has been offline for, and Sum these times for each shift, these pieces of equipment can go offline multiple times per day which is why I need the sum function. The fields I currently have are:
[Date1] : The date at which it went offline
[Time] : The time at which the piece of equipment went offline
[Duration] : The length of time it was offline for
For each day I wish to sum the offline time in two fields called:
[Shift 1]: The previous nights shift (between 7pm the day before and 7am that day).
[Shift 2]: The days shift (between 7am and 7pm that day).
Where to begin but the logic should be something like:
[Shift 1] = Sum of [Duration] For(([Date1]-1 and [Time] between 19:00 and 23:59) And ([Date1] and Time between 00:00 and 07:00))
[Shift 2] = Sum of [Duration] For([Date1] and [Time] between 07:00 and 19:00)
I currently have two tables storing all this data, one containing [Date1], and another containing the breakdown information such as [Duration], [Time] etc. They are linked by the [Date1] relationship. The reason for the first table only having the [Date1] field is because it is a Main Form which contains various sub-forms.
View 3 Replies
View Related
Mar 29, 2014
I am trying to build a query that depending on the value stored in a combo box called SHIFT, either lists all the entries that have been made by our dayshift (07:00-19:00) or our night shift (19:00-07:00)
Each of these components work if I seperate them out
IIf([forms]![FRMFRame74]![SHIFT]=1,Between #07:00:00# And #19:00:00#,Between #19:00:01# And #23:59:59# And Between #00:00:00# And #06:59:59#)
View 2 Replies
View Related
May 22, 2013
I have made a very simple log on form that requires the user to type in a user name and password. The user name and passwords are stored in a table. New users are added to this table to allow access to the database.
Is there a way I can add a counter to see how many times a particular user has logged onto the database?
Is there a way to make the password expire every 365 days requiring the user to reactivate their account and log in details.
View 2 Replies
View Related
Aug 9, 2014
I am new to using access but have managed to build a database that I use to find qualifiers for horse racing based on stats I import for all the racers in the days racing. I have a query which shows the date, time, track, horse and trainer but some races have more than 1 selection so I want to add a field that counts the number of qualifiers per race. I am finding it hard to do a countif, date AND time & track are all the same within that query.
I also want to add another show the total runners in the race but this will have to look into the tables where the query is run from. Hopefully I can figure this out when I find out how to solve my original problem.
View 5 Replies
View Related
Jun 10, 2014
I have a table with 3 fields:
numberFROM, numberTO, Quantity
e.g.:
10, 15, 6
I would execute an append query that will store in another table:
10
11
12
13
14
15
How could I mange it?
View 14 Replies
View Related
Dec 10, 2014
I have a situation where I am using a maketable query to create a table and then I need to use append queries to then add additional records to the created table - some of these are just run once and some multiple times.
if possible, I do not want to hard code the query multiple times i.e.
Code:
DoCmd.OpenQuery "qryCreate_1"
DoCmd.OpenQuery "qryAppend_1"
DoCmd.OpenQuery "qryAppend_1"
DoCmd.OpenQuery "qryAppend_1"
etc
So is there a way I can run the make table query and then get some sort of loop to run the append query a number of times ?
View 4 Replies
View Related
Mar 20, 2015
I have a table [maintable] that holds print records, with the following fields :
id,userlogon,printername,pagecount,color(yes/no),duplex(yes/no),timestamp and papersize.
I want to create a query that will show the following:
Sum of pagecount where papersize like 'a4' and between 2 dates but FOR EACH USERLOGON.The dates will take them from 2 fields in a form so i will use this
"WHERE (((maintable.Timestamp)>=[forms]![reportsform]![frmdate] And (maintable.Timestamp)<DateAdd("d",1,[Forms]![reportsform]![todate])))" and some more..
So i want the query to show,first the userlogon then a field to show sum of a4 then a4 color prints etc..(remember,i want the between 2 dates criteria to be global,to the whole query)
here are all of the criteria fields i want:
1) - papersize=A4
2) - papersize=A4 and Color=True
3) - papersize=A4 and Color=False
4) - papersize=A4 and Duplex=True
5) - papersize=A4 and Duplex=False
6) - papersize=A4 and Color=True and Duplex=True
7) - papersize=A4 and Color=True and Duplex=False
8) - papersize=A4 and Color=False and Duplex=True
9) - papersize=A4 and Color=False and Duplex=False
View 5 Replies
View Related
Oct 18, 2013
Sometimes i have a problem with dynamical queries. For some of these i need to put in the same parameter value two times, before it works. Why this occurs?
View 2 Replies
View Related
Jun 25, 2014
How to filter between times that trays are scanned. I want it to tell me basically how many trays are scanned between 9:00 to 17:00 17:01 to 1:00 and 1:00 to 8:59 but not having any luck. I've been able to get it to show me everything from 1:00 to 8:59 but I've not been able to get anything else to work.
View 6 Replies
View Related
Feb 13, 2014
I have a table with rooms each room have a number of gust. I want to append each room many times equal to the number of gusts.
View 1 Replies
View Related
Oct 24, 2014
I have a query that gives a value in a column either "A","V","L","H","P","S" or blank.
I want to be able to count the amount of occurrences of each letter and hold that number in a column. Is it easy to do?
View 5 Replies
View Related
Sep 1, 2014
I have a table which list a load of items, one field is date and one field is time.I have a form with two date boxes and two time boxes, the idea is for the user to search between the two inputted dates and the two inputted times.This then runs a query for a report to be produced. The problem I having is getting the query to runs both criteria it returns nothing.here is the Where part of the current SQL.
WHERE (((tblIncident.IncDate) Between [Forms]![FRM_SearchMulti]![txtrepdate]
And [Forms]![FRM_SearchMulti]![TxtrepDateB]) AND ((tblIncident.IncTime) Between [Forms]![FRM_SearchMulti]![txtreptimea]
And [Forms]![FRM_SearchMulti]![txtreptimeb]))ORDER BY tblIncident.IncDate, tblIncident.IncTime;
View 7 Replies
View Related
Mar 19, 2013
I have my data updated 3 certain times in a day. So i dont want to bother opening the same access 3 times a day. That access can be opened whole day no problem for me. So, how to run macros automatically in a time which I determine before ?
View 6 Replies
View Related
May 31, 2013
I have a log in table that counts every time a user logs onto the database. Each time a user logs in it adds another entry to a table.
I have built a query to display the user name and date of log in so I could display this information in a report. The report is now getting rather long, and I am looking for a way to display each users name and have the total number of times they have logged in, not display each time they have logged in separately.
The Query has two fields "Agent Name" and "Logger Date"
The report displays the data as follows
Agent Name Logger Date
User 1 26/5/13
User 1 26/5/13
User 1 27/5/13
User 2 28/5/13
What I would like to do is have a report listing each users name, with a column showing how many times they have logged on. e.g.
User Name Login Count
User 1 3
User 2 1
View 10 Replies
View Related
Nov 1, 2014
I am trying to write an access SQL query that returns all records shared between table1 and table2 LESS THAN 3 TIMES.
application has 4 tables: Patients, AnnualPhysical, Doctors, and PatientPhysicalDoctors.
each patient has an annual physical (once a year), and chooses one doctor per annual physical.
each doctor can only perform three or less annual physicals per year.
It is a small application with less than 1000 records per table.
the query will allow the patient to choose an annual physical doctor once a year from available doctors.
Patients is one-to-many to AnnualPhysical;
AnnualPhysical is one-to-many to PatientPhysicalDoctors;
Doctors is one-to-many to PatientPhysicalDoctors.
query for all records NOT shared between Doctors and PatientPhysicalDoctors is:
SELECT *
FROM Doctors
WHERE NOT EXISTS( SELECT * FROM PatientPhysicalDoctors
WHERE Doctors.DoctorID = PatientPhysicalDoctors.DoctorIDFK );
View 3 Replies
View Related
Dec 30, 2014
Preamble edit: I'm running Access 2003.
I have two tables, Students and AttendanceRecords.
Students just has studentID and studentName
AttendanceRecords has AttRecID, studentID, presence, thedate
I'm looking to create what looks like an Excel grid, with the last 10 days as columns and the student names as rows. All the cells in middle will be filled with the values of 'presence' for that student/day (e.g., P for present, A for absent).
Here's something I'm currently considering.
-I could make 10 queries, each using LEFT JOIN to connect studentName with presence & thedate on studentID, varying the 10 queries only in that 'thedate' will have a criteria of Date() -1 , Date() -2 , etc.
-If I'm understanding it correctly, I'll then have 10 tables, each containing 3 rows -- student name, presence, and the date (with each table having only 1 date repeated throughout).
-I could then join those 10 queries together on studentName, theoretically resulting in 1 big table with all the student names and the corresponding presence values for the last 10 days
If I do that, I could make a form in Continuous view and have each row show the studentName and 10 text boxes closely bunched up with presence values.
That seems very inefficient? Making 10 queries separately and then manually merging them seems redundant.
Also, now that I think about it, will the final product end up being read-only, or if the user changes one of the presence cells will it update the corresponding record in AttendanceRecord?
View 3 Replies
View Related
