Queries :: Timesheet Calculations - Round Up To Nearest Fifteen Minutes
Sep 2, 2013
I have a timesheet database, which has the exact start time and exact end time. I want to ignore this if it is five minutes either side of the half hour, and display the exact half hour, but otherwise want to round it UP to the nearest fifteen minutes.
For example:
Exact Start: 09:58:23 Displays as: 10:00:00
Exact Start: 09:42:16 Displays as: 09:45:00
Exact Start: 09:02:08 Displays as: 09:00:00
Exact Start: 09:07:01 Displays as: 09:15:00
Exact Start: 09:23:03 Displays as: 09:30:00
Exact Start: 09:33:00 Displays as: 09:30:00
Exact Start: 09:36:04 Displays as: 09:45:00
View Replies
ADVERTISEMENT
Apr 23, 2014
I am increasing prices and after increasing I am left with 4 decimal places and am trying to figure out how to run an UPDATE query to round down prices to the nearest 5 cents, examples below:
42.4516 round to $42.45
42.4659 round to $42.45
42.4489 round to $42.40
2.49 round to $2.45
2.46 round to $2.45
2.44 round to $2.40
2.04 round to $2
I am using "Currency" format.
View 10 Replies
View Related
Sep 15, 2014
I have a table of standard Circuit Breaker (CB) sizes. I then calculate a minimum CB size in a query field. I want to use that calculated minimum value to look up the next largest CB size from the table and fill a field in the query with it.
For now, I am going to add a field to the CB size table with the smallest size CB which would be assigned that standard value. I will then use a Dlookup with conditions of greater than "smallest size" and less than "standard size" fields from the CB size table.
I think this will work fine, but there must be a better way.
View 12 Replies
View Related
May 23, 2014
I am trying to round off times to the nearest half hour. To be clear, I don't want to only round down or only round up. I need the rounding to be to the nearest half hour. I want to do this in the query, not vba. I've attached a picture of the query.
View 3 Replies
View Related
Oct 4, 2006
I have values for example like, 0.03, 1.14, 1.28 I'd like to round them to the nearest tenth. So resulting values will be as follows:
0.03 = 0.00
1.14 = 1.10
1.28 = 1.30
How do i achieve this? What's the formula?
View 10 Replies
View Related
Feb 13, 2014
How can a I round to the nearest 10 cents
e.g
10.04 = 10.00
10.06 = 10.10
10.28 = 10.30 ........
View 2 Replies
View Related
May 24, 2007
hiya, just posted a message today.
Problem, ive got a work database which employees logon to and enter time against certain projects they have been working on. What i need is to somehow create some code that will look at each of the employees names and sum up there total time for the previous day. If this is less than say 6 hours of timesheets or even zero entries have been made, I want a pop up message to pop up as soon as they logon to the datatbase the next day and jsut mention they need to fill in there timesheets.
At the moment each person when they log on to the database has there name they logged in as, placed into a field (called "First Name") within form (called "Employee Startup Screen"). *** maybe use this as a way to identify who is logged on and when to run the code.***
Timesheet table has the following columns:
Employee; Date; ProjectID; Timespent
PLEASE help im a noob at this and have tried using some code on a query that sums up the previous days times filter on a employee. When the "Employee Startup Screen" opens it runs the following code
Dim internal1 As Integer
internal1 = Me.SumOfTimeSpent
If internal1 < 6 Then
MsgBox " Less than 6 hours have been entered " & Chr(13) & " into your timesheet for yesterday. ", vbCritical, Title1
ElseIf DCount("*", Me.SumOfTimeSpent) <> 0 Then
end if
End Sub
----------------------------
BUT it doesnt work if no entries have been made on the timesheet as the query result is blank.
ANY help would be muchly appreciated.
Thanks Keith
View 4 Replies
View Related
Aug 1, 2013
I'm trying to create a query that will return the closest matches to what is input. However the closest i have gotten to this working correctly is by using the following criteria.
Like Left([Forms]![TESTTESTTEST]![Text2],1) & "*"
Now obviously this isn't a great solution as it just produces results that match the first number but i need it to display results closest to the number entered into text2.
Basically i am doing this as a designer can search for a gap on a machine where there new project can fit on causing the least amount of disruption. So by entering in the size of there project the query will display jobs of a similar size that are already planned on the machine.
View 1 Replies
View Related
Jan 28, 2015
I'm creating a job/timesheet database and have a problem...
I have a main timesheets table that includes every job/piece of work thats been done (sometimes multiples for each employee each day). Each record contains a ref to the employee, ref to the contract, ref to the type of work, time taken and date.
There are then various tables that support the main table, which provide actual employee names etc.
I want to produce a query that provides the number of hours undertaken on each day of a particular week for each employee. So I'd have one row for each employee from the employees table, then a field for the sum hours for Monday through Friday.
The way I was going about this was
- to create five query's for the main timesheets table that would limit the entries to the five days in question - Monday-Friday. That worked fine.
- to then create a query that takes the employee name and using each of the five day based queries perform a Sum Total on the time field to give me the total hours worked for each day for each employee. That also worked fine, but the trouble is it removes any null values. So, if I only include the first day there might be 80 employees listed, but when I include the second day as well that goes down to 72 - presumably as 8 employees who entered timesheets for day 1 didn't for day 2. By the time I add all five days, I have almost no employees. I assumed that there would be a query level property to set, but i can't see one.
Also, is there a simpler way to do what I want? If I can get this working I'd like to replicate to breakdown by contract, job type etc in the same way as I have with employees.
View 4 Replies
View Related
Jun 12, 2013
Basically I have a table setup where Time Fields are stored. What I need to do is in a Query, have the difference of two Time Fields calculated, and then displayed on a Report. Here's what I have so far...
Table
- Start Time
- End Time
Query
Difference Time: DateDiff("n",[Start Time],[End Time])
All that works fine and shows up on the report, except for the fact that the time is showed in straight minutes, so for a difference of 1:30 minutes, it just shows 90 minutes. I need to make it so that on the report in the Difference Time Text Box, it's showed as 1:30 instead of 90.
I tried putting the following in the Control Source for the Difference Time Text Box on the report, but it returns an error.
=[Difference Time] 60 & Format([Difference Time] Mod 60, ":00")
View 4 Replies
View Related
Apr 2, 2013
I need to do a report that takes data from many different tables (like 10).
To do this I have a big query with many LEFT JOIN to link all the tables together. I have made this query last year and it worked well and efficiently until now.
But I have to add something there, and the problem comes with it :
One of the tables is a currency exchange rate table, with the exchange rate of USD to 50 different other currencies and for each day since Jan 1st 2011.
But in this table there are some dates when a currency does not have an exchange rate (example : it was a holiday in that country, so there is no official exchange rate with this currency on that day).
However, I need to have rates even if they are not present in the table.
I need to get the nearest rate, for example if the rate of EUR on 31st December is not available, I can take the one of the 30th, or 29th or January 1st...
Ideally I would like to do the following :
Add a new LEFT JOIN to my already big query that would look like :
LEFT JOIN CurrencyExchangeRate ON ABS(RateDate - Instrument.Date) < 3
But to take only the closest rate.
I tried with a subquery :
LEFT JOIN (SELECT TOP 1 * FROM CurrencyExchangeRate WHERE ForeignCurrency = Instrument.Currency1 AND ABS(RateDate - Instrument.Date) < 3) AS MRate ON MRate.ForeignCurrency = Instrument.Currency1)
But the subquery does not accept Instrument.Currency1 because it is not part of the table CurrencyExchangeRate
I have a solution that should work, it is to search for this rate in a subquery in the select clause instead of doing a left join. But the tables are quite big (hundreds of thousands of entries) and my work machine is not really 'fast' so it would take hours and hours (when I tried I stopped after 3 hours).
I know there is the solution to make a macro to "fill" the missing rates with the rate of the previous day... But I would prefer not to go to this extent.
View 2 Replies
View Related
Aug 6, 2015
I would like to add fields from one table to another based on matching datetime codes.
Most data are in the table 'metadata' (information on sound recordings), and i want to add lat long fields from a large table of GPS points 'gpsData'. Each record in the metadata table has start and end datetime fields - i want to a add lat and long that matches the start time and a lat and long that matches the end time (so four fields to metadata).
My approach has been to try this with two separate select queries (i.e. one for adding the start locations and one for adding the end locations), with the intention of then combining these two outputs in a single table.
When i try a simple select query though I can see that:
a) there are no matching time codes for some records, because although the hh:mm:ss look the same there are differences in the lesser decimal places of the datetime serial numbers. I have tried using the round function on the datetime fields of both tables but this doesn't fix it (I don't understand why not).
b) there are some duplicates because there are sometimes more than one GPS point for each second, and the metadata times are given to second precision (so some metadata records get two entries in the query result).
How can I have the query select the gpsData record with the nearest datetime stamp to that in the metadata table's start time field? The respective field names are UTC and StartUTC. Can i do the same for the end times simultaneously or should i do this separately?
View 14 Replies
View Related
Jul 8, 2013
I have developed a Timesheet database,
1) I have main table "tbltimsheet" which contains all the raw data enter by employees
2) I have employee info table "tblempinfo" Which contains all the employes information
3) Employee will submit timesheet on weekly. Under "tbltimesheet" i have a field for recording workweek
4) Now i need to find out who are all haven't submit the timesheet and for which week
5) I can find those who haven't submit timesheet but i need to know submission status of employee for every week.
View 9 Replies
View Related
Oct 10, 2014
I know the Round function exists in MSAccess, but what I need is one that will always round up, and never round down!
Easy enough in Excel, but I can't find one in Access?
View 2 Replies
View Related
Mar 27, 2014
I have an expression that sets an AI Buy It Now value that is 125% of AI Value and rounds it, which follows AI Buy It Now: Round(([AI Value]*1.25)/10)*10
I need to add to this expression that I only want this calculation applied if AI Values are greater than 999. But I can't figure out how to add this stipulation to the above expression. I have tried IIF but it is not working.
View 5 Replies
View Related
Feb 20, 2015
I'm trying to get a query to perform a calculation and round the results.
The fields that I am running the calculation and am trying to round are Data Type Number, properties Field size Single and decimal places Auto.
I have tried the built in function described in Allen Browne's site to no avail.
[URL]
I have tried rounding the individual fields and then adding result.
I have also tried rounding the result (as attached).
View 11 Replies
View Related
Jan 22, 2008
How can I convert hours and minutes to minutes as an Integer?
For example, convert 2:15 to 135.
Any help is greatly appreciated.
View 8 Replies
View Related
Feb 25, 2014
I have about ten append queries to group various financial data to one main table.
I have used the round function (iff (Round(Nz([FIN_data]),2)) in the queries to round the original data into 2 decimal places but there is still one or two lines exceeding 2 decimal places.
What is the better approach to have only 2 decimal places for all append data?
View 4 Replies
View Related
Apr 23, 2015
I'm working on a utilization database and pulling Excel data from Cognos 8. I use Access formulas to separate hours and minutes because I want to use time (.30, .45 (minutes)) instead of quarterly increments (.25, .50). I've successfully segregated hours, because I also have to turn days into hours. When I try to calculate out a column just for minutes, my formula keeps the number of days and I don't know how to fix it so the column just shows the minutes.
An example is a job has a decimal time of 2.02083333, which when done correctly is 48 hours and 30 minutes. I get the 48 hours correct, but the minutes shows as 2.30 instead of 0.30. That inflates the total job time to 50.30. This is the calculation currently in my database to pull out minutes:
Minutes: Int(([Min]60)24) & "." & Format([Min] Mod 60,"00").
How do I get rid of the '2' in the 2.30?
Access 2010
View 2 Replies
View Related
Mar 5, 2014
I have a database which has a table for the quotes prepared, each record has a quote amount. In a query linked to that table I have the ability to get just a specific month view which shows all totals in that month. I want to calculate a grand total for the amounts shown in quote amount:
Record 1 : 100.00
Record 2 : 100.00
Record 3 : 50.00
Grand total : 250.00
Then I want to display this grand total on a form which is visable on a screen based in the office that has an auto refresh on it so after 2 minutes any new records added to the table will increase the grand total on the form on the screen.
I have worked out how to do the totals bit, but now I need getting this figure (I.e. 250.00) on a form without having to run the query and have it looking in the background.
View 3 Replies
View Related
Feb 18, 2014
I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.
Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 CohortQ([InputDate]))
In the VBA, InputDate is defined as a date
Code:
Function CohortQ(InputDate As Date) As Integer
If InputDate = 0 Then
CohortQ = 0
Exit Function
End If
[Code] .....
But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows.
If I put the function into the query,
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 Year([InputDate])*10+DatePart("q",[InputDate]))
it works.
View 3 Replies
View Related
Jun 15, 2005
I have a table that archives hour meter readings for my equipment. You sharp folks here helped me set it up and it works great. Thanks! I am at the point now where I want to do some studies and calculations on this data. The first project I’m working on is equipment utilization over a period of time. I have all my calculations figured out, all I need is to pull the proper data out and run it through. And that’s where I’m a little stuck.
My table is just three fields: tblHourMeterArchive = eqnum, readingdate, meterreading. I want to be able to input a StartDate and EndDate and have the query choose the archive date that is nearest to the input date and the meter reading on those dates. For example, I have meterreading dates of 1/9/05, 1/30/05, 6/5/05, and 6/19/05. I input 1/18/05 as the StartDate and 6/16/05 for the EndDate, the query should return the archive dates of 1/9/05 and 6/19/05. How would I go about doing this?
I’ve done some research on “nearest date”, but most of the information has been about doing it in VB and SQL. Can this be done without going into coding? I’m still very much a novice when it comes to VB code. Ultimately, I would like to have this work with a form and calendar controls. I’ve been studying some of the examples and suggestions for the calendars, but I’m not really sure how I would go about setting up a query in code and linking it all together.
As always, any help and/or links to information you could provide would be greatly appreciated.
View 7 Replies
View Related
Sep 28, 2015
I have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:
=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]
#Error results when the doc is put into a form mode.
Now when I input each value in the equation above separately.I still get no entry.
For " = sbfCustomerRoomUse" , I get #Error;
and for "= txtTotalPhoneUse", I get #Name.
CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.
View 14 Replies
View Related
Jul 22, 2014
How to implement YTD calculations in Access.
Current table:
Property Month RevenueA Jan '13 100
A Feb '13 50
B Jan '14 200
B Feb '14 300
Desired output:
Property Month Revenue YTD revenue
A Jan '13 100 100
A Feb '13 50 150
B Jan '14 200 200
B Feb '14 300 500
I am new to Access programing and have not been able to figure out a query/macro to execute this.
View 3 Replies
View Related
May 12, 2015
I have a query that I run to extract some values for calculations. Its run on an event based on a dropdown selection. At the moment, I run via:
Code:
DoCmd.OpenQuery "CallMidprice"
and then close it. this seems to take more time than is necessary when I only want to refresh the results.Open query without close, switches to the table but doesn't update until I press F5.When I try requery I get an error. "Runtime error '2109': There is no field named 'CallMidprice' in the current record.
Requery without 'Callprice' seems to requery my form.
View 5 Replies
View Related
May 20, 2013
I need to do a calculation on the RESULTS of a query. Each record in the table I'm querying has a date stamp. I have a query that produces a list of the newest records for each machine. I want to calculate how old each of those records is based on today's date.
So far when I try to include the calculation in that query, it returns all the records instead of the newest ones. This makes me think I'm doing something fundamentally incorrect. The calculation is affecting the query results where I just want to do the calculation on the results. What I'm trying to ultimately do is have a way for the users to see a list of machines that are overdue for a certain procedure. I was thinking of putting a button on a form that will run the query and present the list, perhaps with some conditional formatting highlighting the machines that are overdue, etc. Do I need to just use 2 queries?
View 14 Replies
View Related
