Count Entries Over A Specified Number Of Days
Nov 1, 2006
Hi...
here's a good one...
I have created a database which allows people to enter in when they are off sick. They have to enter in the FIRST DAY of sickness and the LAST DAY of sickness. All of this information is stored in one big table - SICKNESS TABLE. There are no other date fields in that table.
I want to know how many people have been off for 5 TIMES OR MORE in a 365 day period (which may be say, from Oct 2005 to Nov 2006 - so not always in the same year).
I have managed to get a query that will count the number of times someone has been off; but that could be over, for example, 2 years.
How can I get it to tell me if someone has been off for 5 TIMES OR MORE in a 365 day period???
Thanks
Maria
View Replies
ADVERTISEMENT
Jan 24, 2014
I have to count the number of days each week (7 days) that a person has worked - simple enough.
But - what if they come off the job within the week?
I have been using the DateDiff function which is fine.
In the database they have a Mobdate and a DeMobDate but if the DeMobDate falls within the week the whole thing falls apart?
View 2 Replies
View Related
Aug 19, 2015
I have a table called exiting staff data and a field called Follow up required. I want to count the number of Yes entries and display it on the main menu. Have tried
Code:
=Count([Exiting Staff Data].[Follow up required]="Yes")
And using dcount but keep getting error messages.
View 13 Replies
View Related
Apr 14, 2014
I have a form where a start date is inputted (Inputfrm , StartDate) and a form where the end date of the process is recorded (Inspectionfrm , EndDate) and these both record in the table InputTbl as StartDate and EndDate respectively.
I have created a union query which shows a list of all the dates where there is work recorded (WorkingDatesQry and the column of list of unique dates is "WorkingDate"), and as we run a highly varied schedule depending on time of the year and order numbers I cannot just use a query which says Monday-Friday or Tuesday - Saturday.What I am trying to do is to find the number of days between StartDate and EndDate where there is a date recorded in the WorkingDates query.
View 1 Replies
View Related
Feb 24, 2006
I got an assignment from my supervisor and I am not getting it! :(
I have a query (see result in attachment) with the following fields:
tID, emailID, emailtype and EmailDate.
Example:
A customer (tID 1297542) sends an e-mail (emailtype 1) to the company on 2-8-2005.
The company replies on that e-mail (emailtype 3) on 5-8-2005.
Problem:
I would like to create a query that shows per tID the total of days between the e-mail from the customer and the e-mail from the company (between emailtype 1 and 3).
View 4 Replies
View Related
Jul 22, 2006
I have seen a few modules that let you count how many business days between two dates. What I am looking for is how to count 10 business days from another field.
Say for example I have a txt box that auto populates =Date(). I want to have another txt box that will count 10 business days ahead and put that date in the box.
This is so I can have the db show a due date for reports sent in.
Anyone seen/done/have code for this?
View 2 Replies
View Related
Nov 5, 2007
I've had it before, but can't find it.
basically something like this --
DateDiff("w", StartingDate, EndingDate)
that also makes sure date is not in tblHolidays.
anyone knows how to acomplish this ?
View 1 Replies
View Related
Oct 15, 2004
Hi, I'm trying to get some date tracking stats...
trying to get the average number of days between two dates using formula:
=Avg(DateDiff("d",[DateRec],[DateNumb]))
where datenumb was the first date app was entered into db and
where daterec is the date the first part of app was recorded.
this works but I'm assuming it's counting straight calendar days. I was wondering is there a way to get it to count only business days and not weekends or holidays in access???
View 6 Replies
View Related
Mar 11, 2014
i am trying to find the expression to count the number of days excluding fridays from a given txtStartDate and txtEndDate.
i've got the expression to count the number of days but i need omit the fridays from it.
View 14 Replies
View Related
Oct 9, 2014
how to come up with the number of unic days worked as the DB i atached if worker works in diferent work order in the same day in the report it comes up as he work two days
View 8 Replies
View Related
Jul 18, 2014
Recently, my parents have bought a campground and I agreed to attempt to build a database which they can useto keep track of openings, reservations and I want to be able to now calculate the price for the customer. I know I can easily create an "invoice" using the forms.
Originally, I thought this would be easy and it actually wasn't too difficult, I had everything set up to go. Then I hit a snag. The prices are by days, weeks, months and season and get slightly cheaper for each tier you go up. Charging for a week is cheaper than charging the daily rate seven times, makes sense right? it makes it difficult for me because, I'll use the example of nine days, for a stay like this you would charge for one week and two days.
I can't just convert the days stayed into a number like 1.3 weeks and multiply it by the weekly rate because it would be undercharging the customer by about $30 and this number would go up as the stay got longer. I know I could do something like an "IIf" statement but in order to cover all the variables, that would be a long equation with lots of spots where a mistake could be made.
Through doing research, and asking here, I figured that it would be best to develop a UDF which would take the number of days stayed and convert it to something which would count them and turn it into a number like "1 week, 2 days". From there I will have to figure out how to make it so that the program knows which sections to multiply by which rates to build the price. I have taken a course in Access and have a huge book relating to the subject but I'm not quite sure where to begin.
View 1 Replies
View Related
Mar 5, 2014
I have a table with two fields named as "Month" and "Bank Holidays". I have put names of the month in this table for 2014 and the corresponding bank holidays of each month. Now I want to write down the piece of code that will execute when a button is pressed and check the current month of the year and then display the total working days ( After subtracting bank holidays of that month and weekends of that month from the total working days of that month).
View 2 Replies
View Related
Jun 8, 2015
How can I calculate the difference between two dates but I only want to count the work days? So if was today and I wanted to go until 6/15/2015 the difference would be 5 and not 7 because I do not want to count Saturday or Sunday. Is there a special %datediff function where I would only count work days?
View 4 Replies
View Related
Jul 31, 2013
I have 2 tables on one report, 1 is visible, the other visible = no, Is there a way to make that visible = yes when you reach a certain count (60days)...
View 1 Replies
View Related
Feb 23, 2008
Hello all,
I need your help please, i have a Form that also has a Subform in it. I need to create a Text box on the form that will display a number which will reflect the total enties made against a particular ID within a rolling six month period using the system date as a perameter. The number shown will only reflect previous entries made that fall within the six months from the current system date, and will need to update once new entry is made. Incase i have confussed the hell out of you, let me give you an example:
Example One
Entry made today = 24/02/08
Previous entry Date = 20/10/07
Textbox on Form = 2
Example Two
Entry made today = 24/02/08
Previous entry Date = 20/08/07
Textbox on Form = 1
View 4 Replies
View Related
Jul 3, 2015
I am using Access 2013.I am trying to create a query that will count the days difference between two dates. The dates are in the same field. I want to group by Region.So:
tblRegion = RegionID
tblStatus = StatusDate
I know how to use the DateDiff when it is two different fields, but I can't figure out how to do it from the same field.
View 7 Replies
View Related
Apr 5, 2008
I have a query which returns 2 dates. I need to add an expression which will calculate the number of weekdays (Monday to Friday) between the 2 dates.
Anyone help me?
View 1 Replies
View Related
Mar 16, 2014
I would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.
View 8 Replies
View Related
Sep 9, 2006
Hi,
I need to work out a couple of calculations, the number of days between an engineer starting and finishing a project, the number of days between the project being put on the database in the first place and being completed by an engineer and finally, the difference between these. These maybe simple, but I have no idea tbh (still new to access).
I have a form with the date it was added onto the database, and the engineer fills in the date started and date finished as it happens.
These run off of 2 tables BookingIntbl and TechnicalServicestbl
[BookingIntbl].[Date], [TechnicalServicestbl].[Date Started] and [TechnicalServicestbl].[Date Finished]
I have then got the fields, 'Days with Engineer', 'Days in Repair Centre' and 'Day Difference' for the answers to be displayed.
Any help would be appreciated, Ben
View 2 Replies
View Related
May 4, 2006
In a database, I have the following date fields that I use to track projects that start and stop.
START1
STOP1
START2
STOP2
START3
STOP3
Is there a way I can use a query to calculate the total days the project was worked on? For example if I enter 1/1/06 in START1, 1/10/06 in STOP1, 2/1/06 in START2 and 2/15/06 in STOP2, 3/1/06 in START3 and 3/30/06 in STOP3, I need to calculate the total days. I need it to work regardless if there are 3 start/stops, 2 or just one start/stop.
I really appreciate the help.
Jim
View 14 Replies
View Related
Feb 8, 2007
How would I find if 2 dates fall between 2 other dates?
For example:
This would evaluate to True
StartDate 02/08/2007
EndDate 02/11/2007
StartTestDate 02/09/2007
EndTestDate 02/10/2007
This would also evaluate to true
StartDate 02/08/2007
EndDate 02/11/2007
StartTestDate 02/07/2007
EndTestDate 02/12/2007
This would evaluate to false
StartDate 02/08/2007
EndDate 02/09/2007
StartTestDate 02/10/2007
EndTestDate 02/11/2007
View 5 Replies
View Related
Jun 27, 2007
I'm looking to pickup the cases where the date last paid is greater than 60 days. Can someone help me
select id_case,
dt_last_pay
from noldba.case_rollup;
View 4 Replies
View Related
Mar 6, 2008
I imported an large Excel file with a date column consisting of 3 different date formats and need help on a query to extract the number of days. Examples of the date on that column:
7/6/2006
7/1/06-7/31/06
7/1-7/5
Appreciate any advise.
View 7 Replies
View Related
Mar 15, 2006
I have a library system and i need to work out the number of days from the date the book was due and the current date. Could anyone help me.
View 2 Replies
View Related
Feb 11, 2007
I've been searching the Queries Forum for help on this issue. I have a database that records loading records for a Taconite facility. Some days contain many records and on other days there are no records because of no loading activity. I create reports that group these loading records by each day, but I really need a quick and dirty report that just shows me the number of days in any month in which we had loading activity (records present). I have not figured out a way to create a query which counts the number of days in a month which contain records. I'm hope I'm explaining this well enough for someone to help me understand what to do.
Thanks ... Erik
View 5 Replies
View Related
Dec 8, 2007
Hello all,
I have 2 tables:
- Employee
- PackageTracker (Contains PackageNumber, date received, date sent, status)
I would like to query:
- which employee has the package
- How long have we had the package (calculate only business days)
- What is the status
I would like the things mentioned above to be ran in one query.
I am stuck with the calculations. I found a macro code to calculate the business days but I am stuck.
Please advise.
Thank you,
John
View 14 Replies
View Related
