Need Query To Calculate On Time Deliveries As A Percent
Mar 13, 2006
Hello:
This is for the purchasing guy at my client's office. He has 9 vendors he wants to track their on time delivery performance. The data comes from the purchase order tables in his accounting software, to which I've linked via ODBC.
So far so good. I've created a query to pull only those 9 vendors from his total population, then ask for the date range to query by date for the date range (start and end date), and use an expression to calculate the difference between the date wanted and date received so he can tell if the delivery was on time or not. (ex: date wanted: 3/10/06 date received 3/13/06. 3 days difference = late delivery)
The twist is he wants another field so he can mark whether he considers the delivery late or not depending on extenuating circumstances he keeps locked up in his cranium, despite what the date difference results tell him. So, I created a combo box and he can select "Yes" or "No." If he selects YES, a value of 1 is written to the table in that field, if he selects NO, a value of 2 is written to the same field.
So, I need the query to group by vendor, count the total number of entries for that month by that vendor, count the number of "1"s and express that total as a percent of total deliveries.
To summarize, vendor A had 8 deliveries, 5 were on time, meaning he had a 62.5% delivery score. Vendor B had 10 deliveries, 7 on time, meaning a 70% delivery score.
Sorry for the long winded explanation, but perhaps someone can assist. All help appreciated.
View Replies
ADVERTISEMENT
Feb 8, 2014
I have a AS400 database table that stores time in the following manner 70535 (hour, minutes, seconds). Utilizing access 2010, I have used the left, mid, and right command to separate hour, minute, and second into their are separate table values 7 05 35. The AS400 database stores 3:15:30pm in the following manner 151530.
At this point, I used the string function and created the following table value 7:05:35.
Overall, I am needing to convert 70535 to time and then calculate lapse time where the employee name is the same. my database has about 2500 unique employee records per day. I am utilizing a query to perform the above.
date name tstime hour minutes second time
2/8/14 NJohn 151530 15 15 30 15:15:30
View 5 Replies
View Related
Jan 22, 2012
DateDiff in a query
I am trying to calculate time difference between two time columns as follows in a query which has a few inner joins
Diff: DateDiff("h",[Table1.TimeStrat],[Table1.TimeStrat])
This gives problem, it is asking [Table1.TimeStrat] to be entered, where is the problem?
View 4 Replies
View Related
Jul 9, 2013
ID Up Time Down Time
John 18:00 15:00
Kelvin 08:00 08:05
Melisa 23:00 02:00
This is the Table , i need the Query result show IF [Up Time]-[Down Time] is >=3 hrs
Like Below :
ID Up Time Down Time
John 18:00 15:00
Melisa 23:00 02:00
IDUp TimeDown TimeJohn18:0015:00Melisa23:0002:00
I tried DateDiff("h",[Up Time],[Down Time]) in Field but POP out with error "Syntax error (comma) in query expression 'Table1.[DateDiff("h",[Up Time],[Down Time])]' " ...
View 9 Replies
View Related
Aug 3, 2005
Hello,
I,ve made the following date fields in a form: begintime, endtime. I've also made a field Diff that calculates the difference between the two by the code : Diff = Format([begintime] - 1 - ([endtime], "Short Time").
Bij this code you can calculate the difference even over night.
But now I've got to calculate the part of the time someone worked between midnight and 06:00AM if that person indeed worked in those hours.
e.g. someone started at 16:00 end ended at 03:30. The part of time I need = 3:30
e.g. someone started at 01:00 end ended at 11:00. The part of time I need = 5
How can I accomplish this? I hope someone can help me because I'm :confused:
Thnx Remco
View 4 Replies
View Related
Oct 5, 2007
I have one table, Leopard Test Scores 2007, that has only two fields, employee and TotalSeconds. The totalseconds column has a "Number" data type with Long Integer. All entries in this column represent the number of seconds and individual spent to take a test.
All I need to do is calculate the total minutes for ALL records in this table for a report. I want the total to come out in this format: HH:MM:SS - so I've added the following three fields to my query:
Hours:[TotalSeconds] 3600
Minutes: ([TotalSeconds] - Hours * 3600) 60
Secs: ([TotalSeconds] - Hours * 3600 - Minutes * 60) Mod 60
However when I add those three fields to my report, I get an "enter parameter value" for hours and minutes. I must be missing something? any suggestions??
View 10 Replies
View Related
Feb 22, 2013
i have 3x time fields in a table i.e time received, time started, time completed i need to accurately caculate the time between time received and time completed. whenever the time goes past 00:00 it seems to add 24 hrs to my calculated time is there a macro or code that i can use to rectify this problem?
View 6 Replies
View Related
Oct 25, 2014
I have to create a query in access that will calculate two Date and time fields [Date & Time Left]/ [Date Returned], need to figure out between the two fields. Trying to identify when the rep returned the call and the number of business hours (6:00am - 4:30pm) it takes to return a message in Ms Access 2010.
Code:
SELECT [Message Tracking_tbl].[Date & Time Left], [Message Tracking_tbl].[Date Returned], [Message Tracking_tbl].ID
FROM [Message Tracking_tbl];
View 14 Replies
View Related
May 15, 2006
I have nearly achieved this from an example on the Microsoft site but am having problems with the subforms. Can anyone take a look and if possible provide me with guidance as to what I am doing wrong. The instructions are included.Any help with this would be very much appreciated. Kind regards. Bernard
View 6 Replies
View Related
Mar 29, 2006
Hello All,
Is it possible to generate a query that can calculate the 'TotalMins' that an event covers our peak times?
I.e. Our peak times are 0700 to 1000 if we have a start time of 0600 and a finish time of 0900 is it possible for the query to work out that we have covered 120 mins of the peak period?
Thanks for any help provided!:)
View 1 Replies
View Related
Jan 16, 2007
Hi,
I need to calculate an average waiting time for patients waiting to be seen in an A&E dept between two dates.
I have calculated the total waiting time for each patient with the following formula
Total Waiting Time: Format([Time Arrived]-1-[Time Left Department],"Short Time").
Any help much Appreciated
View 10 Replies
View Related
Jun 20, 2007
I hv a access mdb which contains one column as "duration", data format as hh:mm:ss, e.g. 00:02:20 - 2 mins 20secs.
How could I calulate the average of the duration?
like sum of all duration fields / the ttl no of records.
View 1 Replies
View Related
Oct 6, 2013
The below function returns correct time difference between workdays. However, it is excluding Saturday as per the code.
It is calculating 06:30 am to 22:00 pm time for weekdays but I also want it to calculate the time from 10:00 to 13:30 on a Saturday.
I am trying to use the NetworkMinutes function to achieve this. However, there is a problem getting the time for Saturday.
Code:
Option Compare Database
Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : NetWorkMinutes
' Author : Rod
' Date : 13/12/2012
' Purpose : Returns the number of work minutes between two date-time arguments.
[code]...
View 14 Replies
View Related
Oct 26, 2006
I have searched the forum for this answer but no luck. :(
I'm trying to calculate the amount of WORKING time between two dates in an Access database. At the moment i am just subtracting one date from another but this gives me all of the time in between including weekends and evenings.
I need this time to be calculated in hours.
In Excel i know there is a NETWORKINGDAYS function which does something similar but with days rather than hours.
At least if i could get the working days i could then convert it into hours.
Please help!
View 7 Replies
View Related
Jul 9, 2014
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.
View 6 Replies
View Related
Nov 11, 2012
We are collecting data from client files that have multiple dates (start_date, event1_date, event2_date, end_date.) For client protection purposes, we are absolutely not allowed to leave the facility with the actual dates. However, we want to know how many days have passed between each event. I thought about having the data collectors enter each date, have a calculated field store the time between dates, and then have the actual dates cleared when the record is saved or when the person moves to the next record.
View 14 Replies
View Related
Jun 2, 2015
I'm trying to calculate the difference between start and end time, which works fine with:
Total Time: DateDiff("n",[tblTaskTimes]![StartTime],[tblTaskTimes]![EndTime])
However, this is for a database that calculates billable time in 10th of an hour. So, for example, 1 hours would be 1, 30 minutes would be .5, less than 6 minutes would be .1.
The format for this expression is set up to a general number, if that matters.
It's gotta be something in the formula like multiplying this by 24 (for 24 hours) and then dividing by something. Nothing I'm trying is working.
View 7 Replies
View Related
Mar 25, 2014
Suppose that you want to make a formula which will calculate time periods based on a given tariff. To be more precise.
Suppose you can "get in" somewhere, entrance is $2.
You are free to stay there, without additional charge, for 5 days.
Then, for the next 5 days, the charge is $1 per day
The next 7 days, the charge is $1.5 per day
Thereafter, $3 per day.
In this scenario, we can make a query, we use datediff function to see the number of days between "gate in" and "gate out".
The problem is that the tariff is not very simple and the query gets to big and difficult to control with lot's of "iif" and may be I need to make more than 4 queries and union select and so on.
I tried to find in the docmd if there is any suitable function that I could use.
View 7 Replies
View Related
Jan 30, 2014
I have a subform in which I want to calculate the difference between a start time and finish time, the problem I am having is when the start time is on a different date to the finish time
Start 23:15 on 14th Jan finish 00:015 on 15th Jan, also I want the answer to be the number of minutes. The fields I have are
BatchStartTime Short Time
BatchFinishTime Short Time
BoilTime ([BatchStartTime] - [BatchFinishTime]) General Number.
There are many batches to enter per job and the MainForm has the StartDate as I didn't want to enter the StartDate for each batch.
View 4 Replies
View Related
Oct 26, 2014
I have to create a query in access that will calculate two Date and time fields [Date & Time Left]/ [Date Returned], need to figure out between the two fields. Trying to identify when the rep returned the call and the number of business hours (6:00am - 4:30pm) it takes to return a message in Ms Access 2010.
The only issue is the calculation has to be done by time and so I have to calculate what time they left the message(so the difference between [Date & Time Left] and [Date Returned) [Date & Time Left] and when the rep returned the message which is suppose to be [Date Returned] but the problem with this field is the data entry is in date format (10/9/2014, 00/00/0000) of Date and not Date and Time like the [Date & Time Left] field, so I don't know what to do now. Not sure what to do now not a database that create or have allot of control over.
View 4 Replies
View Related
Jun 11, 2007
Hey i have a percent field in a query which displays correctly in the datasheet view, but when make the report the percent is just a long row of numbers :confused:
anyone got any suggestions? would be greatly appreciated
View 1 Replies
View Related
Dec 8, 2005
Maybe someone knows the logic behind MS Access for the display of percents in the formating..... ie.... If you input 3 you get 300.00%. I really dont think ANY user would look at a form... with a field called...say "Commission percent" and expect an input of a 5 to return 500.00% Does this need to be corrected with coding?
View 2 Replies
View Related
Feb 2, 2006
Hi,
How can format proper display for percentage values. What happens is I chose a field as percent, but if I put in 9 meaning 9 % I get 900% as the value is multiplied by 100, but if I put in 0.09 I get 0%.
I just want to put in day 9 and be saved as 9%.
thanks
dfuas
View 4 Replies
View Related
Aug 30, 2005
I searched everywhere for a solution to my problem. I have to create a field and set its format property to Percent using code. I know how to create the field using code but how do i get to Format property? Is that possible?
Thanks in advance for your answers!
View 2 Replies
View Related
Jul 16, 2007
I have set a field in table to be a 'percent' However, try as I might am unable to type in 40 and get 40%
I seem to get either 4000% or 0. I have tried differing settings.
Has anybody else had this trouble and could offer some advice?
Thanks :confused:
View 5 Replies
View Related
Nov 8, 2005
Hi, in my query I am calculating a NET PRICE starting from the GROSS PRICE and the DISCOUNT % (Percent field). The Net Price shows incorrectly an amount which contains several decimals "invented" at the right end. For example, in this moment I have an item with Gross Price = 576,09 and Discount % = 10,00%. The Discount Amount shown by the query is 57,6090049743652 while the correct result should be 57,609. Therefore the Net Price is incorrect as well. Any thoughts about this ? Is there any known problem with the PERCENT fields ?
Thanks and regards. Alberto
View 4 Replies
View Related