What expression do i use to count the number of patientID when there are duplicates (ie patient has come back for more than one visit but I just want to know the number of patients who visited (not how many times))
Count([patientID]) doesn't exclude duplicates
How can I run a query on the table attached to show the number of UR's that were operated on. As you can see some of the UR have more than two procedure dates so I want to exclude duplicates. i have tried "count" but that just gives a "1" value for each UR on each procedure date
View 1 Replies View RelatedI have a table with the following fields:
Customer
Month
Qty
Unit Price
Extended Price
I want to know, by month, what the total extended price is for all customers. But some customers appear more than once in a month. So I also want to know the total extended price for the month, but I only want to include the extended price for each customer once, using only the record with the highest unit price (or extended price, qty for each customer is consistent), and ignoring additional records with the same customer in that month.
I am an excel user, and I could quickly do this with pivot tables... but I have too many records for Excel to handle this efficiently (it just keeps crashing!), so I thought I'd move to access... but I don't know how to use access! So I may need a little hand-holding here.
I have duplicate data in a cell, I want to hide duplicate data and display only non-duplicate data.. I changed the property sheet to only show unique values, but it keeps showing data I don't want to see...
View 3 Replies View Relatedi 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.
I want to be able to find duplicate numbers in the "claim #" field that exceed 20 within the "date of service" field for a month. I have a date box which allows me to select data from only one month at a time or all. What I'd like to be able to do is group the results by month. Currently it is only matching numbers by day not month.
SELECT HMR.[Claim #], Count(HMR.[Org #]) AS Total, HMR.[Date of Service], Count(HMR.[Date of Service]) AS [CountOfDate of Service]
FROM HMR
GROUP BY HMR.[Claim #], HMR.[Date of Service]
[Code] ......
I'm trying to create ONE query which would fetch me the number of occurences on a particular value in a field while keeping duplicates.
For instance let's say i've got the following table
Color......Value
Red.........5
Red.........8
Orange....1
Then i would like to have the following result from my query
Color.........Value.......CountOfColor
Red............5..............2
Red............8..............2
Orange.......1..............1
I know i could achieve this by creating a view first. For instance by saving this expression as qryCountOfColors
Code:
SELECT tblColors.Color, Count(tblColors.Color) AS CountOfColor
FROM tblColors
GROUP BY tblColors.Color;
And then make the following query
Code:
SELECT tblColors.Color, tblColors.Value, qryCountOfColors.CountOfColor
FROM tblColors INNER JOIN qryCountOfColors ON tblColors.Color = qryCountOfColors.Color
However I'm woundering wheter or not I could achieve it without creating the qryCountOfColors.I'm myself into a mental meltdown . I've been playing with the idea of creating of creating a subquery to achieve this but im unable to get to work
Code:
SELECT tblColors.Color As BANANA, tblColors.Value, (SELECT Count(tblColors.Color) AS CountOfColor FROM tblColors GROUP BY tblColors.Color HAVING (((tblColors.Color)=BANANA))) AS Expr1
FROM tblColors;
want my query to count only records that are not duplicated based on a set of criteria.I have a table with customer IDs and Call dates and the employee ID. I want a count of the call dates for each customer ID for each employee. However I only want to count as one occurrence if multiple calls happened on the same day. For example
Cust_ID Call_Dt
12315 8/1/2013
12315 8/1/2013
12315 8/1/2013
14253 9/1/2013
Customer ID 12315 would only count a 1 call entry instead of 3 for the date of 8/1/2013
I am trying to loop through a table and count the duplicate records only once. Please see the attached file.
1) In the test table, I want to loop through all pt_accts.
2) In the countable column, if an acct repeats, I essentially just want to put "1" in one of the records. For the non-repeating accts, putting in a 1 is sufficient. Basically, I just don't want to count the duplicate ones multiple times.
In the example, acct "aaa" repeats, but there is just one "1" in the countable column. "eee" repeats as well, but there is just one "1" and the rest is 0.
Is this possible via VBA?
Hi there,
I have a field called Unit in my table. That field references another table which has about 5 entries. The user can select one on the form.
What I want to do is count how many times each one has been selected so that i can create a chart. The unit names are: North, West, East, South and Main.
Any help would be appreciated.
Thanks
i have a table which contains the attendance records for employees. It shows their supervisor their name aemp number and which kind of hours they worked basic, overtime, Been late and absent is all recorded here.
SupervisorEmp NoSurnameForenameCodeValue
JENNIFER BUCKLEY200McCormackBridgetOT14
JENNIFER BUCKLEY200McCormackBridgetOT21
JENNIFER BUCKLEY200McCormackBridgetBAS4
JENNIFER BUCKLEY200McCormackBridgetBAS5
JENNIFER BUCKLEY200McCormackBridgetBAS4
JENNIFER BUCKLEY200McCormackBridgetBAS5
JENNIFER BUCKLEY200McCormackBridgetBAS4
JENNIFER BUCKLEY200McCormackBridgetLTAM0.05
JENNIFER BUCKLEY200McCormackBridgetOT14
JENNIFER BUCKLEY200McCormackBridgetOT21
JENNIFER BUCKLEY200McCormackBridgetBAS4
JENNIFER BUCKLEY200McCormackBridgetBAS4
JENNIFER BUCKLEY200McCormackBridgetBAS4
this bben a sample from the table. I want to be able to have the employee name, NUMBER AND SUPERVISOR there just once with the appropiate heading for what type of CODe they have BAS, OT1 as a heading with a count of the number of times they done that particular code
Where do i begin any help
Hi
I have a table where is listed all happened faults from a month period. Every fault have start time and end time field and ip field which tells what device has been "broken". Now I'am trying to calculate how many faults has happened in every day. I have make a query from the table which looks like this:
"SELECT (DateValue(Table.[start time])) AS date, Count(Table.[IP adress]) AS [faults happened]
FROM Table
GROUP BY Table.[start time], Table.[IP adress];"
And I get this:
date faults happened
12.8 2
12.8 1
12.8 1
13.8 3
..
But how I can make my query count together how many faults has happened in every day? So it should look like this
date faults happened
12.8 4
13.8 3
..
Hi guys
I'm trying to create a query that will count records from 2 different tables that are both related to a 3rd master table. e.g. table 1 is the master table and contains records of employees, this then has 2 child tables: customers and products. I want to count the number of customers and the number of products that belong to that employee in the same query so that i can then produce a report without the hassle of sub reports etc. any advice on this would be great
Cheers
Hello!
i am trying to sum the total of ID's by corporate titles in query. In the totals row i have selected 'count' in the ID column and 'sum' in the title column but this doesn't work. I tried the reverse and it also was no good :confused:
Can anyone help - i'm sure its something simple
thanks
I wonder if someone could help me with the count function,being as I've never used it. I have two forms, one with a number box called:
Form1![CR2]
The other is called:
Form2![[TotalCR2]
I want to add all the records from Form1![CR2] and put them into
Form2![TotalCR2]
I know its going to be simple and I'm pretty sure I should be using DCount or DSum but I am unable to access the help files on my PC at work for some reason. Thanks!
Can anyone help? I'm running a report that shows conference delegates names and the various workshop options they are choosing for an event. What I am trying to do is work out if there is a way to count the number of people who will be in each workshop. I have set up the database so that when someone registers for a workshop, I show this through a Yes/No box (ie, put a tick in the box). So my end report looks something like this:
Name Workshop 1 Workshop 2 Workshop 3 Workshop 4
Person 1 [tick] [ ] [ ] [tick]
Person 2 [ ] [tick] [ ] [tick] ….and so on
I have managed to count each column by the following control source: =count([workshop 1]) ; =count([workshop 2]) etc. The problem is the number it gives me is the TOTAL number, not just the number of ‘yes’s’ or ticks.
Is there something I can add to my statement to get the formula to just count the ticks? I tried: =count([workshop 2]=YES (and TRUE), but it still counted the total boxes/lines. Basically I don’t want to include the ‘no' boxes in my count.
ANY HELP anyone can give would be HUGELY appreciated. Thanks :)
Hi I need to count the number of words in each cell/field in my access database. Is there some standard function that does this thing or can i write some code to do it for me? thanks
View 1 Replies View RelatedHELP PLEASE!!! There are two fields in my form that need the following relationship:
1. When I enter 1000 in my "spec no" field in my form, I need my "Number Field" to enter a "1"
2. When I enter 1000 in my "spec no" field in the next record, I need the "Number Field" for this record to enter "2" but "1" to remain on the first record.
3. If I enter 1001 in my "spec no" field in the next record, I need the "Number Field" to enter "1"
4. Continue this logic for all records without modifying the previous "Number Field" record.
I was told there was a way to accomplish this using DCount or Count function. Maybe there's an easier way??? Any help is greatly appreciated!! TX!
I have designed an attendance database, with fields for personal details and fields for the days of each month.
I need to have a query that will ask for a certain date(i.e. find the specific field) and then search on specific criteria (i.e. ON SITE) to see which staff are available.
What is the best way of doing this? Any help would be greatly appreciated
Greetings!
I'm trying to make an Attendance Report for my students. I want the report to show each student and how many times they've been "Present", "Late" or "Tardy" in a month and in one year.
I've set up my db with two tables.
Table 1 - contains Student ID and Student Names
Table 2 - contains Attendance ID, course id, student id and the Status ("Present", "Late" or "Absent")
I used the sum queries and it doesn't do the count function properly. This is a copy of my query.
Thanks :o
Surfette
Hi
I have a table which has information the count of students in classrooms around the university and I need to summarize the table by Faculty. Therefore, all I want to do is a count of students for each faculty i.e. Art and Design, Business and Law etc.
The query i put together is: SELECT Count([tbl_Audited Classroom for Week 02].Faculty) AS CountOfFaculty
FROM [tbl_Audited Classroom for Week 02]
HAVING (((Count([tbl_Audited Classroom for Week 02].Faculty))="BAL"));
So when I run the query I get the error message 'Data Type mismatch in criteria expression'. The Faculty field is a text field, so I don't know if that would make a difference.
Can you please help?
thanks
i'm trying to count the total number of records found for this query. i used the following expression (code) in the QED field row Total Incidents: Count([IncidentID]) which, result in an error message.
Can sum1 help me solve this problem. any help would be highly appreciated.
I have a query with 2 fields.
The first is called:TypeOfMedia: Left([MediaID],1)
Grouped BY
Result
C
D
G
The second is called: CountOfProducts: Count(*)
Expression
Result:
1
15
1
This query has counted the number of CDs, DVDs and Games entered. The first letter of the MediaID fields indicates what type of product it is. A typical DVD entry would be: D2387
I now need to implement the totals on a form with other fields such as the name of the DVD.
The other fields are:
Ptitle: text
Type: text
Cost: currency
Penalty: currency
The trouble I am having is to successfully implement a count procedure where I can have three fields each showing how many of the various products there are, open at the same time.
Would any of my esteemed colleagues out there have a solution to this problem?
Regards
Terence
I am using the “count function” on an MS Access 2003 Form field. The count function seems to be working fine except it is not placing the result into the table field.
Does anybody know what I am doing wrong?
Can someone show me the proper format for using the count function used on a form field, where the result is to be placed in a table field?
Please Help!!!
Seti UNIVERSE
http://www.setiuniverse.com/
All of the SETI (The SEARCH for EXTRA--TERRESTRIAL INTELLIGENCE) disciplines in one place.
(Radio, Optical, Microwave, and Amateur SETI)
Stop by Seti UNIVERSE and find out, what you didn't know about SETI.
I have a single table that has 20 columns. 19 columns (1 for each question) have cells with 1 of 6 distinct values, "SA", "A", "N", "D", "SD", or "DK". The 20th column has a value that identifies that records source. I am trying to create a report that would prompt an individual for the records source, and then once entered, calculate the number of individuals that answered that particular question with one of the six distinct values. It should also calculate what percentage of individuals specificied that distinct answer for that question.
I thought I would just create a query that prompted the user for the record source id and perform the necessary count functions for my particular arguments. However, I get an error saying the query is too complex.
I also tried using DCount directly in the report with the table as the source, but I got an error and none of the totals added up right.
Any and all help is greatly appreciated. If I am unclear in what is going on, I would be happy to post clarification. Thank you for your time.
I am using several queries with Region Codes coded in the query. My reports are also based on the individual queries and has a Record count statement as well --
=DCount("[State]","Reporting Query1"). All is working fine.
Now to minimize the number of queries I need to write, I want to prompt for the Region code. I can do that [Enter Region Code:]. It prompts and the report shows the expected output (only records for that region code) BUT my count statement ceases to work and I get #Error instead of the number of records. This happens no matter which field I set to prompt.