Dear all:
I need to count a specific expression in a text field in a table. I desire totals that says "copies" which is selected from a combo box. The total appears in a separate text box. Does anyone have any ideas on how I can count a non-number field?
Many thanks,
Dion
Dear all:
I have created a form with a bound combo box with 3 selections: OFFICIAL COPY, STUDENT COPY and ADVISEMENT COPY.
From there I want to be able to keep a running total for each selection in separate textboxes.
Any ideas will be grealty appreciated.
Thanking in advance,
Dion
Hi
I have a table with a column which has dates in it.
Eg
Column a
01/07/2004
19/07/2004
28/09/2004
06/10/2004
28/10/2004
17/11/2004
24/11/2004
30/11/2004
16/12/2004
10/01/2005
13/01/2005
19/01/2005
I need to count how many records are in each month
Ie 07/2004 = 2
09/2004 = 1
10/2004 = 2
11/2004 = 4
etc
I am using a group by and a count in a query but I think I need some sort of criteria which tells it only to work it out on the month & Year.
Sorry if this is an easy one!! I am not the best at Access but I am a tryer!!!!
TA
IAIN
Hi
I have a table with a column which has dates in it.
Eg
Column a
01/07/2004
19/07/2004
28/09/2004
06/10/2004
28/10/2004
17/11/2004
24/11/2004
30/11/2004
16/12/2004
10/01/2005
13/01/2005
19/01/2005
I need to count how many records are in each month
Ie 07/2004 = 2
09/2004 = 1
10/2004 = 2
11/2004 = 4
etc
I am using a group by and a count in a query but I think I need some sort of criteria which tells it only to work it out on the month & Year.
Sorry if this is an easy one!! I am not the best at Access but I am a tryer!!!!
TA
IAIN
I am working through a lot of data and this is basically what I'm trying to do.
Let's say we have billed occurances for ABC Pet Store. What I need to do, is to see how many "billed lines" appear in the data. So if there are 3 billing occurances (let's say bill amount greater than zero) - in the data I want the query to populate a "billing occurances" field with "3."
How would I do this?
Hi;
Summary: Using DCount in MS Access 97 to output the name of an event
and the total number of occurances per a user input date range.
e.g. the table is called EventData and it is a table of the types of car repair performed, and the fields are:
EventType Date Desciption other Time
======== ==== ============ ====
fix flat 17/01/06 comment.... 101pm
brakes 15/01/06 comment... 504pm
tuneup 02/01/06 comment... 725am
paint fender 03/01/06 comment.. 852am
My issue is to use a subquery for intermediate results and then another query.
I would like the user to enter the start and end dates, and then for output to be:
Output:
EventType Num Occurances
======== ============
fix flat 2
brakes 5
approach 1, use subquery called SummaryQuery:
SELECT EventData.EventType, EventData.Date
FROM EventData
WHERE (((EventData.Date) Between [Type the Start date:] And [Type the End Date:]));
This gives me output of all the events types in the date range and the time.
Then if I query this via:
SELECT SummaryQuery.EventType, DCount("*","SummaryQuery") AS TotalCount
FROM SummaryQuery
GROUP BY SummaryQuery.EventType
HAVING (((EventData.EventType) Between [Type the Start date:] And [Type the End Date:]));
Here I am using DCount to get the num of ocurrances.. but it is not working.
I get the error that "Access cannot find the name "type the start date" "
approach 2, Use only one query:
SELECT EventData.EventType, DCount("*","EventData") AS TotalCount
FROM EventData
GROUP BY EventData.EventType
HAVING (((EventData.EventType) Between [Type the Start date:] And [Type the End Date:]));
for this, I get no output, but the query runs.
Any comments or help with this is appreciated!!!!!!!!!!!!!!!
I have a table with four columns, each textural - boiler type, insulation type, build type, survey type. Of each coumn there are only a handful of options to select e.g five types of boiiler etc, 6 types of insulation etc. I'm trying to build a query whereby these data are summarised thus: Numbers of each boiler type, numbers of each insulation type etc. I've been messin around with 'counts' and 'sums' but my ultra-novice skills are lame here. Help!
View 2 Replies View RelatedWise one, I have looked for this throughout the forum but could not fiend an answer. Could you help? I have found a little script to display the number of characters entered into a text box so at to warn the user if they are getting close to the maximum number of characters allowed.
See http://www.databasedev.co.uk/counting_characters.html
I cannot get it to work. The problem is that I can only enter 1 character. It counts it but then highlights the character I just typed. I then have to click next to it to input the next character. Any Ideas?
Cheers,
Rene
Heelo all:
Code:
=DCount("[GRADUATE]&[ENGINEERING]","Query1")
I am using this code above to count occurances of "GRADUATE" and "ENGINEERING" from a query called "Query1"
The word graduate and engineering are in different columns.
It returns a # error........
Any ideas on how to fix?
Any help is welcome.
Dee
Can anyone tell me how to count (sum) text fields. My current query will search for:
1) Name
2) Ticket Number (ww*, beginning of one type of ticket)
And it works fine except that after running the query I have to manually add up how many of the ww tickets each person wrote. Is there a way to make it change the ww* tickets to a number, like 1, so that it can be added up or to add up by the persons name how many tickets they wrote. I'm using Access 2000.
Thanks
I am working on a report that has a text field with "YES" or "NO" in it. This is not a yes/no field. Is there a way to count the number of "YES"'s and "NO"'s? There will also be some records that will not have anything listed. See example below.
Failed Patients
Item 1 - YES
Item 2 - NO
ITEM 3 - NO
ITEM 4 -
ITEM 5 - YES
ITEM 6 -
Total Failed Patients - 4
I've asked this in a previous post but only got confused. Please forgive me, I'm a newbie at this. I have a table that I need to count a text field.
Table Name: ACE/ARB Table
Field Name: MR# (houses a 8 digit patient number)
Field Name: Failed Patient (houses a "TEXT" field with possible entries of "YES" "NO" or left blank.
Each MR# may have may different entries such as:
MR# 12345678 Failed Patient: YES
MR# 12345678 Failed Patient: NO
MR# 12345678 Failed Patient: NO
MR# 12345678 Failed Patient: (blank)
MR# 87654321 Failed Patient: NO
MR# 87654321 Failed Patient: YES
I need my report to count distinct MR#'s:
From the example above, I would need my report to show/count "2" medical records.
Any help on this would be GREATLY appreciated, I'm going crazy here. Also, if any suggestion has to do with SQL do I type what you would send me directly into the SQL statement from a query?
I'm trying to build a query that will count items in a text field.
How do I do this. I know the statement should read something like this - SELECT COUNT([fieldname]) FROM [tablename] WHERE [fieldname]=yes;
Is this statement correct and where do I place it? Does it go in that specific column. Any help would be greatly appreciated.
Thank you in advance for your assistance.
Hello there !
I had a few problems yesterday with Dcount in a text box in a form but with your help I cracked it
Below is the function I used
=DCount("[PFP Number]","MAIN Table Do not delete","[PFP Number] = '" & [PFP Number] & "'")
The form is based on a table called Invoice PFPs
The Table contains a list of PFP Numbers (bit like part numbers) there are no duplicates in this table
I also have a "Main Table do not delete" with 26000 claims in it each of which has a PFP Number.
The form has a text box in that matchs the PFP Number in the form with the same PFP Number in the MAIN Table and counts them. his works fine with the above function
What I now need is it to count if the PFP Number matchs only if the date specifyed in the function matchs the claim process date (e.g 01-Dec-06 or 01-Jan-07 etc..) in the Main Table
Hope this makes sense ?
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.
I have a question regarding counting of text values base on their status and using that result to a calculation.
Say, I have a table of Demand of Positions, wherein, I have a specific Job Title for a certain Department that have number of workers needed (demand quantity) and a table of candidates for that job title and their status, say, Arrived, Visa Processing, Visa Applied, Visa Issued, and With Ticket.
What I would want is to make a summary out of the two tables, where the query will count how many candidates are there in that specific job title and have a field of status say, field of Count of Arrived, Count of Visa Processing and etc., and a field where I can add all of the count of candidates per status and deduct the result to the demand quantity where that field would be named Balance.
The problem is that the status varies on every candidate on that specific job title because the status field is used to track the progress of each candidate and this scenario will make the query blank because there would be no such record due to their status.
I tried making a summary following my requirement and you will see that in the attached file together with the SQL code of that query that the balance field value is blank.
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.
View 1 Replies View Relatedi am trying to count the number of records based in a query
can some one send me in the right direction
I have a database where there are numerous fields but they all only have one three values Y, N, N/A.
how do i get something similar to Excels "countif" function to summarise the totals of Y's N's N/A's inach field ?
Thanks
I have a database where there are numerous fields but they all only have one three values Y, N, N/A.
how do i get something similar to Excels "countif" function to summarise the totals of Y's N's N/A's in each field ?
Thanks
How can I count the number of times an employee shows up on a report. The final result would be:
Employee 1: Reader1
Employee 1: Reader2
Employee 1: Reader3
Employee 1: Reader4
If an employee shows on a report 4 times, I need it to look like the example above in sequential order, not just a total.
Thanks for your help.
Hi
I have a form which displays which products are on sale but I want it to count stcok orders down, i.e. if there are 10 stock and someone orders 1 I want the system to automatically recognise that there are only now 9 in stock, any ideas? Help really would be appreciated!
Thanks
B
Hello,
I have a form called feedback which has three columns and each cell in a column has a combo box that holds (Yes/No/Empty), and I want to count each column's value for example let's supposed that:
feeback number: Column 1 (Are You happy) - Column 2 (Are you sad) - Column 3 (Are you hungry)
1 : Yes - No - Yes
2 : Yes - Yes - Yes
3 : No - No - Yes
4 : empty - No - yes
Counting: the next step is that I want to count:
values in Column 1, where number of Yes =2 , and number of No=1 the stum of Yes + No = 3 and empty = 1
values in Column 2, where number of Yes =1 , and number of No=3 the stum of Yes + No = 4 and empty = 0
values in Column 3, where number of Yes =4 , and number of No=0 the stum of Yes + No = 4 and empty = 0
So, how can I apply this concept in MS ACCESS (2000-2003).
I am waiting for you kindly reply.
hi
i have a number of query's (Current memberships, out of date memberships etc) all via a certain area/town.
i am trying to find out total figures (how many members how many non members, how many in certain area/town. these need to be updated continuoulsy.
do not no how to approach i ahve looked at the sigma sign and played with no luck.
should i be looking at another query for totaling or un update qurey, sorry very lost, would like this information also displayed on my record form.
searched all weekend with no luck any ideas.
Hi I have a table that looks like this:
Col1 Col2 Col3 Col4 Col5 Col6
1 A B C D E
2 A B C D F
3 A B C G H
4 A B J K L
5 A D M N P
Does anyone know how I can run an SQL query to count how many letters there are For example : 5 A's 4 B's 3 D's Etc
Cheers,
bikeboardsurf
Hi,
I wonder if someone could possibly help me.
I'm working on a database used to dispatch first aiders to events. The general structure is a form detailing the event with a subform (currently pulling info straight from a join table though I may change the data source to a query at a later date) containing details of attendees in list format.
I have a record in the events form that shows the number of first aiders attending which is currently updated manually. In the subform however, there is a tick box as to whether they attended as sometimes they enlist but have to cancel for whatever reason.
I wanted to implement something that will count the ticks and update the number attended field automatically.
I don't know alot about VB and have tried using the count expression function in the subform footer then setting the number attended field to equal the count field. The problem I find with this though is firstly there can sometime be a delay in updating this and secondly I need the number attended to appear in a report listing all the duties attended each month for expense claims.
I may be half way to hitting the mark with what I've tried but if anyone could suggest anything, I'd be very grateful.
Thanks in advance for the help
Ian