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

Queries :: How To Calculate Elapse Time Using A Query

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

DateDiff In A Query - Calculate Time Difference Between Two Columns

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

Queries :: Calculate Time Difference And Show Result Under Certain Criteria In Query

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

Calculate Time Difference

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

Calculate Time On Report

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

General :: How To Calculate Time

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

Calculate Time Difference

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

Field To Calculate Time Elapsed

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

Can I Calculate Peak Time Usage?

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

Calculate Average Waiting Time

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

How To Calculate Average Time/duration

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

Calculate Time Between Two Working Dates?

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

Calculate Working Time Between Dates In Access

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

Queries :: Calculate Time Period For 24 Hours?

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

Tables :: Calculate And Store Time From Dates?

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

SQL Expression - Calculate Difference Between Start And End Time

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

Modules & VBA :: Calculate Time Periods Based On A Given Tariff

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

Forms :: Calculate Difference Between Start And Finish Time

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

Queries :: Calculate Difference Between Two Date Fields To Get Total Time

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

Format Percent

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

Logic On Percent Format????

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

Percent Data Type

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

Percent Format Using Code

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

Percent Won't Change From 4000!!!???

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

Problems With PERCENT Data

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







Copyrights 2005-15 www.BigResource.com, All rights reserved