Counting Number Of Days That Contain Records
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 Replies
ADVERTISEMENT
May 27, 2014
I need to count the days that an item of equipment is used for the last week?
The problem comes if an item is deployed within the week i.e not used for the full week?
I have two fields - MobDate and DeMobDate
I have to create a third called NOD (number of days) which will display the number of days in the previous week that the item was hired out.
View 14 Replies
View Related
Sep 11, 2014
I have a form called subfrm_vactions
rowsource is a query called qryVacations
the query should calculated two things
1 - the difference between start_vac and End_vac in days to calculate the vacation period and put the value in field called Period (working well)
2-the work days which the period between the last day in Previous record (End_Vac) and the (Start_Vac) in the next record (didn't work)
the result is the difference between (End_Vac) and (Start_Vac) in the same record which i don't want
simply i want to calculate the work days.
View 5 Replies
View Related
Sep 10, 2013
I am trying to calculate the number of days between incidents.
If I have an incident that happened on the 10th August and another on the 15th August then this would be 5 days, the next incident occurred on the 28th August so that would be 13 days. The problem I am having is how to get access to use the latest date rather than the first date.
View 14 Replies
View Related
Dec 7, 2006
How would i count the total number of records on a form
View 5 Replies
View Related
Aug 20, 2004
Hi all,
Is there any option to count the number or records relating to
a specific field. Here is the details..The db is for a school and there is a tables for
class and students. I am using a form to enter students details. I had created a
relationship with these two tables. i am using a query to get the details regarding a specific
class like classname, class teacher like that.. Is there any way to calculate the number of students in that specific class.
now i am using the query as follows
SELECT Class.*
FROM Class
WHERE (((Class.ClassID)=[Forms]![Class]![ClassID]));
I had created the relationship with the above two tables with classid as the primary key
thanks in advance
Thanks
View 1 Replies
View Related
Aug 24, 2004
Hello all. I need some help calculating the total number of records returned in a query anytime i run the query.
I have a form that creates an SQL statement and changes the querydef at runtime and displays the results in a listbox. I want to include on my form a percentage of the total records from the table based on the records returned in the query Ex. (5/10) so I can use this information on a report.
What I am trying to ultimately acheive is to calculate an attrition rate based on the number of (clients that leave in a given period / total new clients added in the given period )
thanks for your help!
tony-t
View 1 Replies
View Related
May 24, 2013
I'm after some code as to how to count a number of records within a table, I then want to have this number returned into a variable so I can determine usages with it, e.g. If the value is < 1 and using conditional statements.
View 1 Replies
View Related
Sep 19, 2013
I have been tasked to create a DB for my unit. I have created a few DB, but I am a novice at best. I need a crosstab Query to count the number of records for each FY. The Army's FY is from Oct -Sep. I only need to show the the total number of record for the previous FY in a Report and on a form.
View 7 Replies
View Related
Oct 11, 2013
I'm creating a report for an imaginary "medical clinic's database", the intended function of which is described as follows: "Create a statistic that shows the total number of distinct drugs prescribed to a patient."
Where I'm at:
I've created a query called UniqueDrugs containing drug and patient info. The SQL is:
Quote:
SELECT DISTINCT Drugs.Drug_Name, Patients.[Patient _ID]
FROM Patients INNER JOIN (Drugs INNER JOIN Prescriptions ON Drugs.[Drug_ID] = Prescriptions.[Drug_ID]) ON Patients.[Patient _ID] = Prescriptions.[Patient_ID]
GROUP BY Drugs.Drug_Name, Patients.[Patient _ID]
ORDER BY Patients.[Patient _ID];
The results of this query seem to be what I need. All I need is for the count formula that I use to return the number of distinct drug names there are that are related to a patient's ID.
I've created a report and I've put the following formula into a text box:
=DCount("[Drug_Name]","[UniqueDrugs]","[Patient_ID]=[PID]")
Where PID is the name of another text box in the report that contains the Patient ID that I want to compare to the drug names returned by the query.
However, this *always* results in #Error, no matter how I change the formula.I have been led to believe that syntax is not the issue, as the following formula worked as intended for a different task:
=DCount("[Prescription_Date]","[UniqueYearQuery]","[Patient_ID]=[PID]")
How do I use a DCount to return the number of different drugs that have been prescribed to a patient?
If it did not require the number of different drugs, I would simply use the following formula, which works just fine:
=Count(Drug_Name)
View 6 Replies
View Related
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
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
Dec 1, 2005
could someone please help me. ive tried all sorts of things and now im loosing the will to live.
under the data protection act i have 40 days to produce requested files in a hospital. the problem is that my little database that has been working away for 2 years is not capable of flagging up any files that are gone over the limit.
i need to run a query that will bring up all files not completed in the past 40 days. or reaching the forty day limit.
ive tried <now()-40 or <date()-40 in the critieria of the date rquested field. ive also tried kicking the machine when my boss isnt looking. that didnt work either.
im sure this aint rocket science.
samzie
View 3 Replies
View Related
Oct 4, 2006
Hi guys
bit of a newbie here....but could you pls help me on this one!!!
Í have a table that looks kinda like this:
SRVDate - Date/Time
Fleet_Num - Text
Odo - number
ServiceType - Text
ServicedBy - Text
DiffOils - YES/NO
Gearbox_Oils - YES/NO
ValveAdjust -YES/NO
I need to extact the fleet numbers, the day the last 5 "B" services were done and then calculate how many days between each "B" service....
Kinda like this:
Truck1 B Service 01/01/2006
Truck1 B Service 01/02/2006 31 Days since last service
Truck1 B Service 03/02/2006 2 Days Since last service
I know how to extract all the data but its the subtracting the dates that killing me!!!!
Any help guys?
View 11 Replies
View Related
May 19, 2005
Hi,
Is it possible to count the number of consecutive days in a table?
I need a query to calculate for a particular customer the number of consecutive days thay have logged a record if its greater than 1
EG: Table layout:
CUST A RC1 05/05/05
CUST A RC2 06/05/05
CUST A RC3 07/05/05
CUST A RC4 08/05/05
CUST A RC5 15/05/05
CUST A RC6 18/05/05
Possible query results:
CUST A 4 Days First 05/05/05 Last 08/05/05
Is it possible to do something like this??
Thanks in Advance
View 1 Replies
View Related
Jan 2, 2013
Is it possible to make one column automatically count 21 days from the date of another column, and if so how?
View 1 Replies
View Related
Feb 1, 2006
I would like to count the number of working days between two dates, NOT the calendar days, if the workers only work Monday thru Thursday. I know we could use the DateDiff function, but HOW do I count excluding Fridays, Saturdays, Sundays?
Thank you for your help.
View 1 Replies
View Related
Oct 16, 2013
I am looking to do a calculated field. Not sure what is the best way to do this, but I would like to have a dates in a table with all of our employees that has what date they started with the company. From that I want to know not how many days they have been with the company, but just the years.
So if I have been with the company 364 days, it should only show up as 0, and if I have been with the company for 365, it will read 1. I would like this to be in a query somehow. We use the number of years they have been with the company for their pay, and need it to be years, and not days.
View 9 Replies
View Related
Oct 11, 2013
I have 2 dates that I need to count between. Easy enough just use the datediff right? Nope cause it won't count just the 5 workdays. I researched and found that the "w" in the function doesn't work the way I need it to. I found lots of code to make a module that will do it for me and they all include having a holiday table. Right now I don't need a holiday table I just want the simple dates in between.
View 2 Replies
View Related
Jan 26, 2007
I have a report due the first of each week in which I need the cases open and cases closed for the previous week, the week two weeks prior and the 2007 and 2006 year to date on two different types of cases. I have a case management table with a field for Type of Case, date assigned and date closed that I uses in my queries. Presently I have two query, one that generates only Type 1 cases from the Case Management Table and another for Type 2. I then use the Type 1 Query in another query that limits the results for Type 1 cases to those opened last week, one for those open two weeks ago, one for 2006 YTD and one for 2007 YTD. In these 4 queries I have one field [Type of Cases] and I have the query count. I then do this for Type 2 cases and then go through the whole process to do Closed Cases. All my queries have criteria to automatically filter the dates to the time periods mentioned above. I then have one report query that I put all the number in for my report. This query has 16 fields with the numbers for each period, last week open and closed, 2 weeks open and closed, etc. I then generated a report that takes these numbers from my report query and puts it in a report format automatically. As you can imagine this takes some time to go through each query to generate these numbers, so I was wondering how I may do this differently. Also, I have experienced a problem when a field produces no records I get a blank sceen with nothing under the Count of column and get the same thing for my report. How can I fix this.
View 1 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
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
