My report has certain patient's listed. Under each patient there are different dates that the nurse visited these patients. I need to count how many days the nurse visited ALL the patients. the problem being that she will see many patients on the same day. As an example:
Patient Smith
1/1/07
1/5/07
1/9/07
Patient Jones
1/1/07
1/5/07
1/20/07
1/31/07
I need to know the number of distinct "days" during the month the nurse was visiting patients. The example above should show that the nurse had visited patients 5 days out of the month.
Okay, this is gonna be weird :o What I'm looking to do is count the dates a preticular training task was accomplished. However, I do not want the dates that are expired to be counted. I will be running this off of a quiry that shows the "due date" of each task. I really have no idea where to start :confused: . Many thanks, Tim
Here is my table: tbl_tc Name Date Tech_Name Issue Resolved Resolved_Date In Out
I need a query that will do the following: Total Number of: Tickets Open (IE, Date field is populated, but Resolved_Date is not) Tickets Closed (both Date fields are populated) Total In Total Out
Total By Tech_Name: Same as above but I need it seperated by the techs name.
Problem is that I only need it to pull up the Current Months tickets, I will pull additional months later on.
I am not really sure how to even start this... HELP PLEASE!
I have a report that lists a medical record number and under that number is a list of dates that the patient was seen. I need to know how to count the number of times the patient was seen. Example:
Medical Record # 12345678
Dates Seen 1/1/07 1/2/07 1/3/07
Total Days Seen: 3
So: I need to know, on my report, how to count the total number of days seen. Thanks for any help.
P.S. I'm learning so much from this forum, thanks!
I have a form with four fields, date1, date2, date3 and date 4. These have all a mm/dd/yyyy format. I need to add an unbound control which will count in months and years the difference between the dates.
Ex. date1: 01/01/1970 date2: 05/05/1980
date3: 06/06/1990 date4: 06/30/2001
Fields date3 and date4 may be blank, therefore the code should consider only counting the first two.
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?
I have an activity report which has multiple entries for the same date by club members. I have managed to build the database:
2 Tables 2 Queries 2 Forms 1 Report
Which list attendance's and activities by Member and Date and Prints each one starting on a fresh page..What I wish to do is count each Club Members total days attendance. I have tried using the Totals button but I don't know enough about expressions to get it to count multiple dates as 1.
Everywhere I have looked can tell me how to count between dates, around dates, workdays in a year etc. etc. etc. but none give me multiple dates the same counted as 1.
Basically I have chart in a report thats based on a query that counts the amount of entries per month between two dates inputted by the user.
It all works fine but the chart that is based on the query only shows months that have an entry.
Eg if it counts all dates between the two dates and say the only month that has an entry is July, the chart will only show July. What I want is the other months to show (Null values) as zero, so every month shows. I'm probably missing something basic but can anyone help?
[TextPriDate] is the start date [TextPriDate2] is the end date
This is the query code (QryDate) SELECT tblMain.ID1, tblMain.Dt FROM tblMain WHERE (((tblMain.Dt) Between [Forms]![frmSwitchboard]![TextPriDate] And [Forms]![frmSwitchboard]![TextPriDate2]));
This the code from the chart in the Report SELECT (Format([Dt],"MMM 'YY")) AS Expr1, Count(*) AS [Count] FROM QryDate GROUP BY (Format([Dt],"MMM 'YY")), (Year([Dt])*12+Month([Dt])-1);
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.
I'm creating a database for my wife to use in her work, one of the fields is dates visited and the user should enter "00/00/0000, 11/11/1111, 22/22/2222, 33/33/3333" in this format. The field is a large text format because there could be anything from 1 date to hundreds. What I need to be able to do is in my query I need to enter 2 dates and have access return a how many dates exist between those 2 date values. eg It will pop up a box asking for start date and then another asking for end date and then it has to return the count total of how many dates exist between those two date values. I don't know VB and have had only basic training in Java and C# none of which involved Access. The only way I can think of doing it is to have access count the number of commas within the date field required but I don't know the formula required assuming it can even be done.
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.
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
I have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)
I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date
So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.
Is there a way to do this?
So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :01-Apr-2015
(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)
Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?
I have a database with 5000 entries, corresponding to about 10 entries for about 500 people. Each of the entries is dated, and I need to calculate the time intervals between each person's sequential entries in the table.
One way of doing this is to create another column that contains the date of the previous entry. I can then use DateDiff to subtract one date from the other and give me the difference in days.
This approach falls down if I then work with only a subset of the entries - I would have to re-enter the previous entry dates as the time intervals would have changed.
What I really need is a way of subtracting the date from the date in the cell directly above it. Will Access let me do this, or is there a better way?
I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.
To explain the query I need, for example: table1 01/01/2014 15/01/2014 17/01/2014 30/01/2014
Explanation: Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates) Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2 Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2
I have a master table which shows all transactions per record (person) over a financial year.
Each record person has a seperate package period over which their spend needs to be measured. Therefore although I have all their transactions for the year, I only want to sum their transactions between their given [start date] and [end date] which are in columns.
I need to be able to create a field which sums all expenditure per record between the start and end dates
Name Start Date End Date Invoice Date Amount
Matt 15/5/11 15/9/11 1/11/11 £100 Matt 15/5/11 15/9/11 7/7/11 £200 Matt 15/5/11 15/9/11 12/12/11 £200
In this case I would only want to sum 7/7/11 as this is between the start and end dates
I want to write something like sumif([Invoice Date] is between [start date] and [end date] - not sure where or how exactly
(The start date and end date will always be the same per person)
Please bear with me here as it's a little involved.
I'm doing a staff profile website which includes a section where they can enter their annual/other leave details.
I decided to store their leave in two fields Start_Date | End_Date rather than each individual date that they took - the short and wide approach vs long and narrow.
This has left me needing to do a query that would return all the dates between the start and end dates inclusive.
I appreciate i could do this using some script to loop through a recordset and build an array of dates but i wondered/hoped that it could be done using SQL.
As it is an asp page i can't use user defined functions in a VBA module in Access so the solution would need to be pure SQL.
I have a scenario where the first three rows of date which have dates of 4/1, 4/4/ 4/6 with ndc 5513026701; next six rows that have dates from 4/8 to 4/20 with ndc 5513014801; next three rows that have dates from 4/25, 4/27, 4/29 with ndc 5513026701.
The issue I am having is I do not know how to have separate min/max dates for ndc 5513026701 since when I group by ndc 5513026701 min = 4/1 ; max = 4/29. I need to have min = 4/1 and max = 4/6 for one row and another row of min = 4/25 and max = 4/29.
Any easy way to sequentially create min/max for each ndc 5513026701? I wasn't sure how to verbalize this so I have attached a sample worksheet.....
I'm not sure if I am biting off more than I can chew. I have a text field in each record in my database (Inherited) The db has nearly 5,000 records. I would like to split the field into records in a seperate table. An Example of the table as is now;
Is this possible in one hit or do I need to process the records without dates first and then run another process to split those with Dates? I say dates but the field is a text field. About 15-20% of the records contain dates which are always enclosed in parenthesis.
