I have a list of clients in a query field and I want get a count of the number of unique clients. Some records have duplicate clients. Lets say there are a hundred records but only 50 unique clients. When I apply "unique values" in the query's properties, and then enter "Count" in the Sum field, it gives me a count of 100 not 50. When I remove the Sum field, it lists the unique values correctly (50).
Thanks.
Is there anyway I can add a sort of count field to my query, for example what I require is the result of my query to include a column which counts the rows in the queries.
For example:
PositionID NumOfVotes Count
5 5 1
6 2 2
etc.
Any advice please?
Is there anyway I can add a sort of count field to my query, for example what I require is the result of my query to include a column which counts the rows in the queries.
For example:
PositionID NumOfVotes Count
5 5 1
6 2 2
etc.
Any advice please?
In forms, I frequenty use the following expression to get the results needed from a field's column:
=EmployeeID.column(1).
However, when I try to use the column function in a query it doesn't like it:
EmployeeName:[EmployeeID].Column(1)
What am I doing something wrong?
Along the same line, if I want to use =EmployeeID.column(1) for an unbound control in a form, why must you put the bound field on the form just to get the info? In this case, I want to do this to display the employee's name - not the primary key - without the scroll bar. Every time I do this, Access performance analyser tells me to use fewer controls, but you can't if you have to have the reference. The same thing seems to be required in queries, i.e. include the unaltered control to get an expression.
Help and advise, as always, will be appreciated!
Christine
I have a table tblBookings.
In this table it has a bookingID, CustomerID and some other none relevant details.
The CustomerID comes from table tblCustomer. i.e a customerID must exist in the customer table to be allowed in the bookings table tblBookings
A customer can exist in tblCustomer without existing in the booking table.
I am trying to write a query that will list each and every customer ID in the tblCustomer and count the number of bookings that that customer has (even if it is zero).
I have a query that will count the bookings if they exist in the booking table and display the number of times that a customer appears in the bookings table.
SELECT tblBookings.CustomerID, Count(tblBookings.CustomerID) AS NoOfBookings
FROM tblBookings
GROUP BY tblBookings.CustomerID;
How do I create a query that will do this but list all customers even if they don't exist in the bookings table (but obviously occur in the customers table)
I am trying to create a similar query where all bookings per hotel are listed even if no bookings are made for that hotel. I am guessing the answer is the same as above.
The Ritz. Bookings 0
The Hilton. Bookings 3
The Carlton. Bookings 0
The Lowry. Bookings 2
For every hotel.
That kind of thing.
If you need more information please shout.
The field SECL DDI has the users phone number unfortunately over time these have been entered in different formats so there are 5 digits, 6 digits, 7 digits etc...Can I run a query that counts the number of digits in each filed and then tell me how many of each exist
Ie
4 digits 3412
5 digits 5000
Hello All!
I'd really appreciate some help for what I fear is actually a very amature question...any assitance would be greatly appreciated!
I have the following table:
SubCount---------HomeDetailsID------FoodDetailsIDFK--PrimaryName
Ruteete--------------199------------------54-----------Amatehe
Ruteete--------------218------------------54-----------Amatehe
Ruteete--------------199------------------54-----------Amatehe
Ruteete--------------198------------------90------------Amoozi
Ruteete--------------204------------------90-------------Amoozi
Ruteete--------------192------------------20----------Banana Juice
By Subcounty, it shows what homes in the subcounty are consuming a particular food item. What I would like to do is count the number of times that food item is being consumed in each subcounty and generate a new table to compile this data.
And I am trying to use the count function to return the frequency of each Food being consumed. However, the count function counts all the records and returns a value of 6. What I would like is it to count the frequency of each food and ultimately build the following table:
SubCounty---NumHomesFreqBasedOn--FoodDetailsIDFk-PrimaryName---Freq
Ruteete----------------6------------------54-----------Amatehe------3
Ruteete----------------6------------------90------------Amoozi-------2
Ruteete----------------6------------------205--------Banana Juice----1
I was thinking that this may involve using a loop to get a count on each specific food but the logistics of doing this are beyond me at the moment. Does anyone have any suggestions?
Thanks!
How can I add a field that just populates "1" for each record so I can do a count. I know I can count based on a specific field but my boss wants to see a field that just shows "1" so I can SUM it up in another query.
View 9 Replies View RelatedI have a dataset with "Date", "Product ID", and "Revenue" fields. I'd like to create a query that rolls the data up at the daily level and reports "Count of Product ID" (unique product IDs) and "Sum of Revenue". I'm having trouble figuring out the logic.
View 1 Replies View RelatedI have attempted to create a chart in Access and it does work to a degree, but I get the strange sample chart in design view and can see charting the data in my database to be troublesome.
I've had a "brainwave".
I'd like the user to have a command button to bring up a form screen or a report screen and for it to have some data in it.
I have a field 'Reason' and it's text box is actually a combo box containing around 9 different values.
I'd like my form/report to list these values then next to it tells the user how many are displayed.
Below is just three things that is in the 'Reason' combo box. I have around 9 different ones in total.Notice on the right I have example numbers. Is there a way to do calculate this?
Thanks.
CFT: 45
RTA: 56
Obstruction:509
Hello!
I have a report with a field that returns data according to a date that the user enters when he opens the report for viewing (this is based on a parameter query). So for date 1 you have let's say 10 names and for date 2 you have 5 names. Now here is my question:
How do I create a box on the report wich returns a count of the names field? So for date 1 it should return the number 10 and for date 2 return 5. I don't particularly want to have this in the query but would prefer to have this just on the report...
Can anyone suggest a solution?
Thanx
I need to count a yes/no field. If the check box is checked I need it to count the checkmark. If it is blank I don't want it counted.
I am trying to put a calc in the Group Footer because I want it based on the group of checks for a certian person.
I know there has to be a way to do this and I am just not figuring it out.
Can someone lend me a hand is getting this going.
Thanks
I working on simple MS Access program which have 2 tables. As you can see in snap shot. What I am trying to achieve is. I would like to run a query which shows data between certain dates(It is done) and also to show me which Technician has done how many jobs(i have 5 technicians) and how much money a technician received by a payment method like Technician ID 1 did Cash Transactions 4 worth $300 similarly Credit Card transactions 2 worth 120$ and so on for other technicians.
The main theme is to get weekly summary report of technicians how many jobs they done and how many transactions each technician made by cash, by credit card along with their amount.
Is there any way to count how many lines of text are stored in a particular table field? How about counting how many times was the "enter" key pressed?
Thanks
Mafhobb
I have a large table (over 20,000 records) where the text fields were all set at 255 - even those requiring a single character entry. Is there a way to determine the highest existing character count for each field so I can set the text fields to a reasonable setting? After a compact and repair will existing records be set to the new setting?
View 1 Replies View RelatedMy company requires a query to aid distribution, however I am experiencing some problems implementing the system.
We have purchased a large database of addresses and need to be able to query a town/street to find out how many houses are in that street.
I have the query and form set up to display the street/town and list of house numbers when searched for, however the houses are grouped by postcode rather than street, with multiple house numbers in the same field (see example).
Street
Main Avenue
POSTCODE
HG23 0DF
Houses
89;91;93
POSTCODE
HG23 1DF
Houses 95;97;98
----
Basically, I need a total of how many individual houses there are in a street, regardless of postcode. In this case, it should treat the ";"s as dividers and return the result of '6'. Or in some cases ignore the semicolons as some fields look like ";;;;;;;;13". It should then add both the total for one postcode to the other postcode to display a complete total of houses in one road.
Can anyone make any suggestions?
Thanks in advance,
Matt
I have the query below and it returns the number of cases for eache of the case status (open, closed or private) Some of the cases have no status, the field is empty. Is there a way to count the number of cases which have no status in the status field?
Can someone tell me how to do this?
SELECT
Count([Report table part one].CaseStatus) AS CASES,
[Report table part one].CaseStatus AS STATUS
FROM [Report table part one]
GROUP BY [Report table part one].CaseStatus;
i have a crosstab query that contains employees and their sassocaited assignements he/she is working on. each assisgnment is associated with a start and end date. there are monthly columns that forecast until 2007.
i need to create a headcount report that provided a count "1" for each employee in a monthly column.
currently i there is a Vaule Field that Counts the number of Employee......yeilding the desired "1" BUT i want it to only populate a "1" if the Montly Column is Between the Start and End Date for the assocaited assignment.
is it possible to add a If Statement in the Employee Column to only Count if the Month from the column is between the assignments start and end?
if so, can you please provide?
gracias
I have 4 columns
PostCode
District
District Name
Count of Postcodes = Count(Left([hull_PostCode.PostCode],4))
Now some Postcodes display 4 lines as they cover 4 Districts, my questions is I only want to show the highest Count per postcode and disregard the rest, Ive tried many variations of the Max function but am a little stuck.
Can anyone help?
I have few of these columns in my query
Loc Company Pack RoundedNum SumTotal
2 ASDA AA 1
2 ASDA AA 5 6
1 Asda AC 2 2
1 ABC BB 10
1 ABC BB 1 11
2 XYZ AR 1 1
I am trying to achieve "SumTotal" column result. In the above query RoundedNum is an expression achieved from other columns(not shown above) from the same querytable. Now I want to insert this "SumTotal" column which will count for Total RoundedNum as per same type of Packs.
Thus the Total Field should look like the one shown above. Any idea how can i achieve this? :confused:
Thank you in advance.
Does anyone know the syntax to count the number of characters in a numeric field?
If not, how about counting the number of characters in a string?
I have a problem to count a multi columns into one field. For example, I have a table consist of these fields (simplified version):
- id
- block_name
- handle_type (lookup: big and small)
- kernel_type (lookup: big and small)
- fruit_set (lookup: 1, 2, 3 and 4)
I want to create a query to count a combination of handle_type and kernel_type columns as one column. So the query field would shows these fields:
- block_name
- count_of_big_handle_big_kernel
- count_of_big_handle_small_kernel
- count_of_small_handle_big_kernel
- count_of_small_handle_small_kernel
- count_of_fruit_set_1
- count_of_fruit_set_2
- count_of_fruit_set_3
- count_of_fruit_set_4
I have one field containing a couple of 6 digit codes and would like to count the number of codes in the field and write the number to a calculated field. Each code is separated by a comma and a space. How can I count the no of comma within the field?
View 3 Replies View RelatedI am trying to work out the expression that will first count the number of 'Yes' returns in a series of yes/no boxes and then display as a percentage.
To explain...The yes/no boxes represent attendance over a 20 lesson course. I have added a count for each lesson for student attendance..(grouping them in the footer) but I would like to add the number of attendance for each student.
I have an unbound field in a report that i'm trying to do the following:
=Count(IIf([Total Build Time]<=15,1,Null))
This gets me the closest to my answer. Tried it in many different variants, this will get me the total number of records, but will not use my criteria (<=15). The "total build time" field is formatted as short time 00:00 and i have tried my criteria in the same way...plus many more. This will count the records but will not limit the result to 15 or less.
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.