Calculating the rates over a period of time. I think the attachment will give you a clear picture of what I am looking for.
To get the cost for a particular period I want the rates to be updated as per the given period. How I could get the calculation in the mdb format...
Hi,
I need to calculate the number of days between two days however the format on which i have my date includes the time in the same field -
2007-02-20 07:15:30.474
When I calculate one date minus an other the result is #error
I require the result to be in a count format e.g 2.3 days (like in excel)
Is there anyway to get around this without splitting the date and time?
Please help!:)
I am working on a database where i need to perform the below activities,
start->pause->resume->pauseagain->resumeagain->finish
I wanted to calculate the actual time from start to finish excluding the pause time.
I have a working dB which can calculate a shift duration and sum total all shifts worked within a period for the purpose of producing a labor report for payroll. I have successfully used the DateDiff function and converted the minutes to HH:MM on my form and reports. Now I want to calculate elapsed time for a specific period within a shift, I'll call it OtherHours and I am aiming to calculate a portion of time that meet the following conditions below. I am using field names of [PunchIn] and [PunchOut] and both are of type General Date.
IF [PunchOut] ISNOT Saturday,Sunday
EXIT FUNCTION
ELSE
IF [PunchOut] ISNOT Between Midnight and 0559 hours
EXIT FUNCTION
ELSE
DATEDIFF ("n", <MIDNIGHT>, [PunchOut])
My thoughts are to solve the DateDiff portion and then figure out how to apply the conditions within the IF statements.
I'm asked to do a specific task, a database concerning of:Tables: 25Forms: 25Reports: 40-45Build from ground up, multi-user, accounts, account-restrictions, has to be linked to an already existing database, will need a fair amount of VBA coding.I would like to know specifically two things:--> what is your common hourly rate --> what would your common total-price-estimate for a project like this.
View 1 Replies View RelatedStarted working with access 2010
Have a text field in a form with following relevant properties:
Format tab - Format: Short Time
Data tab - Inputmask: 00:00;0;_
Data tab - Default value: =Time()
Goal: show a default but editable current time in HH:MM format
Behaviour: When entering form current time is displayed in HH:MM - problem is that when I enter field with the curser it automatically changes format to HH:MM:SS.
How can I avoid/control that behavior?
I have set a field on my FORM with the General Date format. Every time I use the date picker it inserts the correct date with a time of 00:00? I even changed the format on my table to general date - still no luck.
View 4 Replies View RelatedI need to alter filed 24-gen-64, in 24/01/64... how to?
View 5 Replies View RelatedI have a series of fairly large csv files which I need to consolidate by importing them to a single Access database. There are of the order of 50-60,000 'lines' per csv and about a dozen different files (all comma de-limited and text-qualified with inverted commas).The problem I have is, there are several date fields within the csv which are not formatted to Access' liking. Namely <Mmm_dd_yyyy_hh:ssAM> with underscores used here to represent spaces.
So for example, today's date appears thus :
Feb 6 2013 12:00AM (i.e. two spaces between 'Feb' and '6')
And this day next week would appear thus :
Feb 13 2013 12:00AM (i.e. one space between 'Feb' and '13')
I will obviously need this field recognised as Date/Time so I can query appropriately but I have no control over the source data and need to find a way of converting the format.One option would be to run a script on the csv's themselves prior to importing but I'm not sure how to do that.import this field as Text and then edit the contents after import (VBA?), such that the field can be redesignated with a data type of Date/Time.
How do I enter and store a period of time that may be of any length: years (.. unlikely but ...), days, hours, minutes, seconds (or even micro seconds) that I can later add to other dates or times stored in my database? How do I create a sensible data entry mask for yyyy/ddd/hh:nn:ss.999 and then convert this to a field compatible with date/time functions? And what format should this data take (be converted to ..?) and be stored in?
View 2 Replies View RelatedCan you help we have a data with a list of jobs that scores points, I need to build a query or scirpt that will add 5 points every 30 day fro the date the job was submitted this will help old jobs come the top of the list.
View 1 Replies View RelatedHi,
Over the past three months I have been trying to develop an application which can repeat appointment entries.
I would like to have the option to repeat an appointment entry for a given number of weeks.
For example if an appointment entry was placed for today (Wednesday), there would be a command to create additional duplicate enties for the same day of the week for a given period of weeks ie say every Wednesday for the next 52 weeks.
Any help on how I could approach this would be very much appreciated.
Thanks
Hi there
I have created a database in Access XP (2002). In a nutshell, the database records numbers of people attending a seminar; which can take place any number of times per week, and so hence can take place any number of times per month.
I have set up the query so that it can run immediately after a seminar to show the attendants who attended the seminar on that current date "Date()" in the criteria box. However, how would I go about setting it up so that it shows who attended every seminar in the current week or month?
We sell products out of vending machines and once in every few days (time span between readings may be different every time) read data from the machines using flash drive. Each reading from all machines produces an Excel file:
Reading_Date | Machine_ID | Product_ID | Amount_sold
2008-04-08 | 1 | 1 | 26
The only date that we have is the day on which the data has been read and this tells how many sausages were sold from the date of last reading till the date of current reading (after each reading counters are set to zero).
The problem is to get average amount of product X sold from machine Y on every calendar day. For a total rookie like me it seems like creating another column that would divide the Amount_sold by the number of days between two consecutive readings and put this average amount for every calendar day between these two readings. May it be done this way? How? Another way? How? I would be very grateful if anyone could help me and explain the solution in an accessible way.
I have designed an access application that logs emails that come into a department. However, the team leader has asked me to create a report to work out if emails were answered within a 24 hour period.
Is there a method to do this. It is not something I have attempted to do within access before.
I am using access 2013 and have an issue creating a query with employee hours for a two week period.
For starters I have a table with the following:
Employee Number, Date, Time in, Time out, Description, Rate Code, Week end, Pay Period. (there are a couple others but they are not needed for the query)
My table is set up this way so that each employee can clock in and out 1-4 times a day based on what they are doing. For example I may work from 6-8 in the office then 8-10 in the field. The office has one rate code and the field has a different one so i cant just be clocked in from 6-10.
At the end of the pay period I create a report and send it to an accouting firm who does the payroll. Right now I have to manually enter in each employees time. I was hoping that by using this table I could create a query that will give me the calculations I need in order to create the report.
In my query (to Start) I need to have the following with it set up per pay period.
Employee Number Rate Code Regular Hours Overtime Hours
How to set it up so that it calculates the hours based per rate code with the given pay period.
I am trying to get a count from a table, but only for a certain time period. Here is a little bit of my table:
STRMIDtrDateDT trDT
1617 3/25/2015 7:30:00 PM0
1530 1/8/2015 4:40:00 AM10
1532 1/12/2015 9:20:00 AM60
1533 1/14/2015 4:50:00 AM1
What I am currently doing is querying a table for the Count(*) for the day before. This works all well and good, but now I am wanting to break it down by a time range from the trDateDT field.
I've been struggling with getting a report to simply count records (I don't need any detail other than the counts). Here is what I have:
Table CompressorRoundsT contains these fields (many more actually but these are the ones I care about for this purpose):
Date, Shift, Round
Possible records in the shift field are 1st, 2nd, 3rd and the round field has Rnd 1 and Rnd 2.
For each date, I need to count the number of records per shift and per round. For example, the report should show that on June 7th, 1st shift has 14 records for round 1 and 14 records for round 2. Same for 2nd and 3rd shift. I don't need any other details of what the records contain, just the counts.
I've had some success but the layout ends up too spread out because it is including the individual records instead of just showing the counts and it isn't grouping the way I want.
It's a report for management to see how many readings (rounds) were missed in a period of time.
I 'm downloading the excel data from the site and connecting it to access.
In excel the particular column (Time Taken) is in the format of "00:12:26".
After connecting it to access and appending it to the table, the format changed to "12:12:26", the first two digits changed to "12" and the remaining are as it is how it looks like in the excel. I need to change it to format what it looks like in the excel.
HI all,
I am still new in Access databases :o
I have a table with 'General Date' column. So I have to create query wich extracts records between Last record (via Date field) and 'for example' 10 days before. But Last record in Date column may differ from Now().
When this is done I have create calculations with extracted records using agregate functions.
I'm trying to use Last function for Criteria putting it into Date field and substracting with -10 (for 10 days) but it doesn't work.
Please help! :confused:
I have a pre-established database with a form that calculates total sales and tax for a time period i specify. I want to add a box that will display the number of sale records. Ex. if i had sales of 50,000.00, and that came from 200 sales i want the box to show the number 200.
View 1 Replies View RelatedCould I change my unbound textbox (txtperiod) on the Report to display the time period.i.e.
Weekly, Monthly, Quarterly
Criteria is BeginDate and EndDate on main form.
I'm in the process of creating a database in Access 2010 that has two front ends, basically. What I want to be able to do is lock out one of those front ends for a specific period of time every day.
View 2 Replies View RelatedI have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still shorttime but when you read the time within the code it comes out in AM/PM format. Also, when the defaul tiem should be #20:00:00# but this changes to #8:00:00 PM#
Weirdly though, when you just open the table, the times are in the correct shorttime format.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
This screen shot may help:
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still short time but the format is in AM/PM or medium time.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
I have one table with employees and positions. Each position has a specific rate which is stored in another table (table2). Since the rates of the positions are changing, I want each time the employee get paid with the correct rate.
The problem is that I do not want to lose historic data since I want the rate to calculate the salary. I thought that the best way is to look up for the rate according to the position and the period that is valid.
I have attached herewith a small example with the two table that I want to use the relationship.