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:
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 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.
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.
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?
Can 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.
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.
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?
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.
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 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.
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.
i have a date field with time and date each record is entered. results look like this
Date_Complete 4/9/2007 8:26:11 AM 4/9/2007 8:31:25 AM 4/9/2007 8:34:14 AM 4/9/2007 8:34:21 AM 4/9/2007 8:34:29 AM 4/9/2007 8:34:36 AM 4/9/2007 8:34:49 AM 4/9/2007 8:41:27 AM 4/9/2007 8:41:49 AM 4/9/2007 8:42:32 AM 4/9/2007 8:42:39 AM 4/9/2007 8:42:49 AM 4/9/2007 8:43:36 AM 4/9/2007 8:44:21 AM 4/9/2007 8:45:48 AM
I want a query or report to give me the average entry time per record. Something like: Average time between orders 1:25 (one minute twenty five seconds)
I have a query with the fields dtDate (Date/Time in Short Date format), NC "Number of Chats" (Long Integer), and TCT "Total Chat Time"(Date/Time in hh:nn:ss format) from tblChats. Each date will have multiple NC and TCT values. This query is totaling them by date with the following SQL.
SELECT DISTINCTROW tblChat.dtDate, Format$([tblChat].[dtDate],'Short Date') AS ChatDate, Sum(tblChat.TCT) AS SumOfTCT, Sum(tblChat.NC) AS SumOfNC FROM tblChat GROUP BY tblChat.dtDate, Format$([tblChat].[dtDate],'Short Date') HAVING (((tblChat.dtDate)>=Date()-7)) ORDER BY tblChat.dtDate;
The query works fine. Now my question.
I would like the query to figure the average time per chat for each day.
Basically, I get daily reports from the client and the AHT and ACW values come in seconds. I've recently added code to the query changing the data to nn:ss which works perfectly. Below is some info:
tblTelephony [AgentName] [Calls] (number of calls answered) [AHT] (in seconds) [ACW] (in seconds)
[Code] ....
The above code works very well, where I'm stuck is in the attempt to average the fields [AHTMinSec] & [ACWMinSec] by Agent in a report.
Using [AHTMinSec] as an example:
I have the daily values for each agent [AHTMinSec] in the detail, Avg([AHTMinSec]) in the Agent group footer showing each agent's average, Avg([AHTMinSec]) in the Team group footer showing team averages and Avg([AHTMinSec]) in the report footer showing the campaign average including all teams. When I run the report the details are hidden providing each agent's average, the team average, and the campaign average.
The report worked just fine until I converted to minutes with the above code. Is there a reason that I'm getting an error stating that the calculation is too complex? I've done enough research to determine that the db can't Sum in order to average...
I've been searching all the forums and unable to find my answer. What I want to do is show on a form the average quote for the specific model number.
On the form there is customer info, product info, etc. I want to see the average of what we have quoted every other time we have seen this product (we do repairs). The identifer for the product is the model number, Model#, the quoted cost is PRICE. I've used this function before, DAvg("[Price]","Repairs","[Model#]='013003020'") - which of course gives me the average for one specific model number (013003020). How would I use a similar function but have it do the average for the model number that I am viewing on the form?
If I am able to do that - I also need to find a way to not include nulls and zeros from the Price field.
I have a report that counts the number of monthly calls. What I would like is an average of the monthly calls in the report footer.
My total for a particular month is =Count([Date]) and I named the unbound control MonthlyTotal.
This is in the DateFooter section of the report.
I then put an unbound control in the report footer and used the expression = Avg([MonthlyTotal]).
Of course when I changed from design view to report view, it asked me for an input of [MonthlyTotal].
I then tried =Avg([Reports]![qryLetterWritersbyDate]![MonthlyTotal]) and while it didn't ask me for an input, there was nothing in the ubound control in the report footer.
I have search for an answer, but all I find is using a query. Is what I am attempting to do possible? If so, how?
I need to get an average repair price of multiple part numbers with the same root number i.e.
8 each 8TJ124GGN1 4 each 8TJ124GGM1 7 each 8TJ124GGP1
First I used a query to average the repair price of each subset of numbers, and then queried the query to average all the subset prices together.
If I simply use the AVG function in the first query, I can use it again in the second query to get the average price of all the subset price averages. HOWEVER, if I use the expression:
…to get a more accurate average by throwing out the high and low values, I get a:
“…expression too complicated to be evaluated”
when I try to average the averages in the second query using the AVG function.
I don’t know why the expression is “too complicated” since the first query has already completed its computations before the second query begins its AVG function.
I have been at this for 6 hours and have read countless entires in this forum. I have come close......but not close enough.
I am trying to calculate the difference between two times. I can't use a combination of date and time because if I do, those who use the database won't use it. To much info to input. Right now they input a simple time in the form 16:43 to represent 4:43 PM. They are used to, and like that method.
Everything works fine until I span midnight. Now I have read all the postings about this issue and how datediff can be used with some wacked out formulas.
I can get the proper time by simply using the formula Format([timestart]-1-[Timeend],"Short Time") in a query. However, I need the total time in hours and minutes as a decimal.
Ex. 7:30 would become 7.5 To make things more complicated I need it rounded to the closest 15 minutes.
Ex. 7:35 would become 7.5 7:40 would become 7.75
If there is a way to do this with datediff great!
Now as I mentioned I have already solved the midnight span problem with the formula above but the result is in the form 7:30.
So if someone can just tell me how to get my query field "TIME" from 7:30 to another field TIMENUM as 7.5 even better
If someone can tell me how to get field TIMESTART and TIMEEND to give me a decimal number EVEN WHEN THE TIMES SPAN MIDNIGHT....well then you are a genius.
Take that one step further to the closest 15 minute interval and you are a genius and my hero =-)
