Please help me as I am trying to create a query which will count up the total of various fields.
For example say I had a database of fruit. Now I wanted to count up how many apples I had and also how many oranges I had and display these in a report. I have managed to to get a query which does this using UNION but it means that the totals appear in the same column (only one column) in the query.
So it would say:
CountofFruitType
11
28
11 might be apples and 28 might be oranges but I want a separate column for each:
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.
how can i count the number of rows in a query. i have a query that return me number of rows. i want to count the rows. when i try to count it, it gave me a column that count only her column. which mean in every row it counts me '1'. i want to count all the rows.
I am trying to write a query for a hospital database which counts the number of patients who received various treatments throughout their stay in intensive care. I think I will have trouble explaining textually what I would like to achieve so I have attached a Word document which should illustrate my requirements sufficiently.
If anyone could help me I would be extremely grateful.
2000 German 213 2000 French 245 2000 Spanish 286 2100 German 289 2200 French 259 2200 Italian 246 etc...
Does anyone know how to count the specfic data in a field, I want to be able to count e.g. how many italian in tourID 2000? i have some idea of using the count function but how do u display zero??
I have a table Deliveries with 4 columns; CarNo (text), Name (text), DeliveryDate (number), ReceivingDate (number).
I need help with a query which summaries; total number of deliveries, deliveries on time (DeliveryDate = ReceivingDate ) late deliveries (DeliveryDate < ReceivingDate) early deliveries (DeliveryDate > ReceivingDate)
So the out put should be like;
CarNoNameOnTimeLateEarlyTotal 3Taxi1053 18
So far I only managed to get the figures from 4 separate queries, and its really time consuming to collect all figures in one report.
I have a query that I want to get some totals on in a certain date range.
The fields in the query are: SystemGroup Problem FaultCount Date
Under Fault Count there is a possibility of two answers: Cosmetic or Mechanical
What I would like to do in the query is after I run the Query between two dates I would like to see what my two totals are for Cosmetic Faults and Mechanical Faults.
I thought I had an expression that would work but it will not work. I am stumped.
So bottom line: after I run the query I would like to see: Total Faults = ?? Cosmetic = ?? Mechanical = ??
Which is fine, however there is another query as well which shows all the classes that didn't take place for these faculties, therefore I want to include this as well. So I have tried:
SELECT Audited_Week03.Faculty, Count(Audited_Week03.Faculty) AS CountOfFaculty, Summary_W02.Faculty, Count(Summary_W02.Faculty) AS CountOfFaculty1 FROM Audited_Week03, Summary_W02 GROUP BY Audited_Week03.Faculty, Summary_W02.Faculty;
But when I run the query I get the following data view:
When it should be: FacultyCountOfFaculty FacultyCountOfFaculty AAD3 AAD 2 BAL2 BAL 0 CSE51 CSE 8 HLS78 HLS 2 N&M42 N&M 8 TO22 TO 10
The other possibility is to create a separate query for classes that didn't take place then create another query which joins these 2 up. As you can see it will start to get confusing so it would easier if classes that are booked and classes that didn't take place were in one query.
Is this possible, because there are altogether 25 weeks of data that need to be counted?
I have a problem I need some help with. I have a table that keeps trades for the day: TDate TTime Type Amount. The type is L for Long, S for short. Multiple trades occur each day. I want to summarize each day to a single record:
Date TradeCnt Longs Shorts Amount
My problem is how do I count the number of Long (L) trades and the number of Short (S) trades. Heres where I'm at:
SELECT TDate, count(TDate), ??Longs Shorts?? Sum(Amount) FROM tblTrade GROUP BY TDate
How do I do the Longs and shorts, thanks in advance !!
Hi, I was wondering if it was possible to make a query count the number of times the same text has been entered into a column. For instance a name box, could a code be written to count the number of times john has been entered, without actually typing in the name that I want it to search for?
Is there any way that access could search the database for name entries and then display the name and number of times it has been entered? Thank you in advance!
Apologies if this is really stupid:- I am creating a database which is pretty simple but it is for a survey report. I have a table which will have data input via a switchboard form. There are 3 columns and each one has a drop down lookup menu with 10 items in it. All telephone calls received over a period will have the data entered in to the 3 columns. So far so good. I need to create a report using a query which will count the data in each column and have an individual total for each item. ( 3 columns x 10 items = 30values) I set up a query and input a total's row using the count query. In the criteria I entered one selection in inverted comma's. ie "Call in to change address details" but an error message came up. Any ideas and also what would the best way be to create a report. Would I be better off importing the details in to Excel?
I have a db which contains certain sizes of shirts. I need to count the total of each particular size of shirt. Ex. I have the column SHIRT and in this column I have L3, M3, and S3. I would like to count how many S3's I have. How do I do this?
Im havingsome trouble with a count query. I need to find a total from all records i.e Record 1 text 1 = 3, record 2 Text 1 =2. I need to add the numbers together. So I created a query and used Sum. My problem is that it dosen't seem to update the query unless I shut the DB down and reopen it. Im obviousley doing something wrong! Any ideas?
A "Text Box " does it for Reports, but how do you count total # records in a Query? How would I just count not null records in a Query? I am new to this, and MS Help is not working for this particular item-counting records in Query, though it works for items.
I'm trying to get a count, but want to combine the columns that have the same first 3 characters. Anyone have any suggestions. For instance, I have columns that say 25C5P, 25C5X, etc. I want to combine all 25C but I want to do this for all records, so I can't simply put in 25C**.
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
I am trying to simulate the data as it is presented in Excel, but with Access. This might sound stupid, but I have to draw a chart in Excel by Automation from the data I retrieve in Access. So far, I have made a query, which returns the following (see SourceData.jpg (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=10335&stc=1))
What I have to do next is present the data as follows (see ResultData.jpg (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=10336&stc=1))
How do I do this in SQL? I have a book at home which I am sure explains this in the form of a Count function, or a crosstab query, but will only be able to check it on Thursday. Could somebody maybe suggest something in the meantime?
What I do know, is that the bottom “Summe” row cannot be done by one query. But the column “Total” must be able to be done with a Count function. (Or am I wrong?) :confused:
Its so hot and humid here and no air-con that I cannot think, so please help!!!
I needed to get a report that is based on year. The default report doesn't group them by year. Right now I have a query that extracts the year from each date. I'm guessing the next step is to count the number of occurences for each year. Then finally take each distinct year and base the graph on the count for each year. While I have an idea on how this may work, I don't know how to implement it.
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
i have a simple Table something like this from a test station: -------------------------------- Serial_Number | Status | -------------------------------- A_____________Pass B_____________Fail C_____________Pass A_____________Fail B_____________Fail A_____________Pass
and i need to get a query that separate 2 colums from the Status and count the records for each Serial_Number, some thing like this,
I have a table that includes client account information. I want to create a query that will select all account numbers where the last updated date is less than or equal to a number specified by the user.
Table (ClientRequests)
AccountNumber LastUpdated 76869 5/10/2005 9:49:38 AM 22151 10/29/2005 10:35:23 AM
so for example: I would like to display all accounts where the differrence between todays date and the LastUpdated date is <=10. Where 10 would be a user defined number.
