Reports :: Counting Repeated Dates As 1
Jul 14, 2014
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.
View Replies
ADVERTISEMENT
Oct 11, 2007
I have qrys that show current info and also repeated info. A request has been brought to me to now count how many times the repeated data has been repeated.
The data is updated daily, so the repeated record would need to increase in the number of it's occurances by 1 each day.
Any guidance on how to accomplish this is GREATLY appreciated! :)
View 5 Replies
View Related
Oct 11, 2014
I have created a query that is designed to return a count of how many records there are in various tables. There are 10 expressions in all, so when it is run I am expecting to see one row of data with each field populated with the number of records.
It does do this, but the data is repeated over line after line (see the attached picture)
View 4 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
Jan 5, 2006
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!
View 3 Replies
View Related
Mar 6, 2007
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!
View 8 Replies
View Related
Mar 23, 2007
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.
View 4 Replies
View Related
Jan 25, 2006
Friends,
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.
Thanks for any help.
View 14 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
Nov 29, 2007
Hi, I searched the forum for this but the only thread that came close to what I was looking for was this.
http://www.access-programmers.co.uk/forums/showthread.php?t=125240&highlight=null+chart
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);
Thanks
View 2 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
Feb 8, 2015
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.
View 1 Replies
View Related
Feb 14, 2014
When entering information on the form, there is a combo box with 4 options
Started
In Progress
Verified
Complete
There are several different areas on site here and i'd like to be able to set up a report which will count the amount of the above 4 possibilities for each section. For example i'd like the report to look something like below
Area A
Started 1
In Progress 6
Verified 3
Complete 5
Area B
Started 3
In Progress 9
Verified 21
Complete 11
So i'm displaying the number of jobs in each section and how far along they are at a glance
View 10 Replies
View Related
Dec 9, 2014
As part of the report I'd like to have a table that would list all different values in column in column A and the number of times they appear in the table in column B.I have a bit of a hard time how to populate the table the easiest way.I have all the values in a separate source table, so first column is easier. but do i have to write the dcount statement for each row for second column?Could I display results of query as subquery in report.If you have a bunch of textboxes with increasing names like NameBox1... for for/next loops in reports... is there an easy way to assign those names?
View 9 Replies
View Related
Feb 12, 2014
I have a report that is pulling data from a query and on the report, i have a controlled field that is supposed to count only data where a field says "no". Here is the string.
=Sum(IIf([Caller Used Resources]="No",1,0))
when i run the report i get the four results that say no, but i also get a blank field counting the times when this field is blank. If this field is blank, i don't want it to show anything.
View 1 Replies
View Related
Jun 7, 2013
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.
View 2 Replies
View Related
Oct 24, 2014
I am trying to use =DCount() on a report and its half way working, it does count records acording to the criteria I asign the problem is its using the whole universe of records in the table so I have a report displaying 10 records of which 953 are Red and 752 are Blue...
View 14 Replies
View Related
Jul 19, 2014
I have a report which is based on a query. The query combines information from TBLDwgReg and TBLDwgRegDtls. The unique field linking these two tables is DrawingNo. The query - QRYDtldDwgReg - is showing 99% of the information I want to see.
My problem is this - one drawing can be revised several times. I have my report grouped by IssuedBy and then grouped by DrawingNo. In the group footer for IssuedBy I want to count the number of drawings issued by a particular consultant. For example...the structural engineer has issued 17 drawings for a particular project but when I do a count it is returning a total 27 because some of those drawings have been revised.
I tried grouping in the query and counting the DrawingNo field there but that's not working either.
View 14 Replies
View Related
Jul 27, 2015
I remember reading a tut on how to count records using Running totals in a report - but I cannot remember where I read it. It went something like this.
In the section that you want to count, add a field with record source '=1' and make it a running total for the group.
In the header (one level up from where the running total resides) add a field with record source =Max(RunningTotalField)
Although the 'intellisense' sees my 'RunningTotalField' when I create the above mentioned formula, when I run the report, its asking me to input the value for the 'RunningTotalField'?
Have I confused some concepts or am I on the right track?
View 1 Replies
View Related
Jun 6, 2013
I've got a table with 23 columns. Column 1 is the ID row which has a unique client ID in it. Then we've got a column called 1st_Reason and one that goes with it called 1st_Transfer_Date. This pair repeats for 2 through 10. Each of the Reason columns can have a set value, for example "First Processor".
What I need to do is create a date with months on the Y axis and the 5 different reasons on the X axis. I need to count the number of "First Processor" across all 10 Reason columns for each month. I would need to repeat that for each other reason type, but if I can get one to work I can simply change the reason type.
Here's what I'm using to count May's total:
Code:
=Sum(IIf([Current_Reason]="First Processor" And [Current_Processor_Transfer] Between #4/30/2013#
And #6/1/2013#,1,IIf([1st_Reason]="First Processor" And [1st_Transfer_Date] Between #4/30/2013#
And #6/1/2013#,1,0)))+(IIf([2nd_Reason]="First Processor" And [2nd_Transfer_Date] Between #4/30/2013#
And #6/1/2013#,1,IIf([3rd_Reason]="First Processor" And [3rd_Transfer_Date] Between #4/30/2013#
[Code] .....
I get wildly inconsistent results. On one of the types, it's only counting where it's the 1st_Reason. Some of the others return the same value regardless of which month I have selected.
View 2 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
May 10, 2014
Suppose we have a report that outlines several fields, one field shows the City. In the report, this week, we see 10 records "New York", 8 records "Houston", 7 records "London", 3 records "Paris" and so on.
Next week's report outlines different cities and different number of records. I need to have in the report footer a "recapitulation" , a field that would say :
New York 10
Houston 8
London 7
Paris 3
Total 28
Next week cities and number of records might not be the same, we may have
Tokyo 12
Singapore 14
New York 6
London 7
Can i make my report in that way that it will count the values without using VBA ? I tried the count values option but it counts the overall report, does not take into consideration the different values.
View 2 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
Apr 22, 2014
I have been trying to create a report to count equipment tested between two dates:
I am using Access 2000 (old I know) and I have 21 different tables with the fields laid out the same. For the moment, I will list five of the tables: Servers, Laptops, Printers, Workstations, and Monitors. The criteria I draw from each table are the fields Model#, Part#, Serial#, Test Date, Retest Date, and Technician.
I can create a report from a query (say laptops). The criteria I is BETWEEN[Enter Start Date]AND[Enter End Date] under the TEST DATE field. Works great! In my report I use =Count(*)&" "&"Unit(s) tested" & "Between "&[Enter Start Date] & " and" & [Enter End Date].
That works great too.
I am trying to create ONE REPORT using ONE Date range and display how many units were tested:
Example:
Units Tested between March 1, 2014 and March 31, 2014
Servers 9
Laptops 23
Workstations 15
Printers 18
Monitors 31
If I can get these five tables, hopefully I can add additional tables to the same report as I need them. I am not savy with VB code, but will give it a go with some direction as to where to put it and how to add to it.
View 5 Replies
View Related
Jan 16, 2006
when i change a form so that instead of single form its multiple forms it seems to only want display 30 instead of the 62 that exist. is there a quick fix this?
if not is there a way to make it display the next 30?
as now it says "record 30 of 62" when it gets to the bottom of the form... ;/
any help is much appreaciated ta. <3
View 3 Replies
View Related
Mar 6, 2006
Hello All,
In my table, I have some records that are duplicated (aside from the primary key).
How can I select only one record from each set of duplicated entries? (There may be more than 2 of each of the duplicated records, but I only need to pull one of them...doesn't matter which one.)
i.e...Like a 'Find Duplicates' query, but only returning the top 1 primary key from each duplicated set.
Thanks,
Sarge.
View 2 Replies
View Related
