Counting number of records based on criteria - 7841

Hello,

(Try again to get the question in the google-groups. Perhaps not
pushed on the send button).

For counting records as expression in a report in ms.access I'm using
=count(*) which shows all records.

Now I also want to know how many of them has registered a specific
data in a specific field.

I'm trying to use =count([Field1]="999") to count how many of the
selection reported has registered data 999.
This option isn't work. Can somebody give me the correct solution.

thanks,
   Johan

0
johan
5/20/2010 1:42:28 PM
access 16762 articles. 3 followers. Follow

2 Replies
5056 Views

Similar Articles

[PageSpeed] 46

The 999 needs to go in the criteria for the query driving the report or the 
filter for the report.

You could also group by Field1 if you want to see the count for the various 
data in Field1.

You could also use DCount in a text field which would look something like:

=DCount("[Field1]", "TheTableName", "[Field1] = '999'")

Note that DCount, or any of the aggregate functions starting with "D", can 
be very slow when used like this.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"johan" wrote:

> Hello,
> 
> (Try again to get the question in the google-groups. Perhaps not
> pushed on the send button).
> 
> For counting records as expression in a report in ms.access I'm using
> =count(*) which shows all records.
> 
> Now I also want to know how many of them has registered a specific
> data in a specific field.
> 
> I'm trying to use =count([Field1]="999") to count how many of the
> selection reported has registered data 999.
> This option isn't work. Can somebody give me the correct solution.
> 
> thanks,
>    Johan
0
Utf
5/20/2010 3:47:01 PM
Either of these should do it:

=Sum(IIf([Field1]="999",1,0))

or:

=Count(IIf([Field1]="999",1,Null))

Ken Sheridan
Stafford, England

johan wrote:
>Hello,
>
>(Try again to get the question in the google-groups. Perhaps not
>pushed on the send button).
>
>For counting records as expression in a report in ms.access I'm using
>=count(*) which shows all records.
>
>Now I also want to know how many of them has registered a specific
>data in a specific field.
>
>I'm trying to use =count([Field1]="999") to count how many of the
>selection reported has registered data 999.
>This option isn't work. Can somebody give me the correct solution.
>
>thanks,
>   Johan

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201005/1

0
KenSheridan
5/20/2010 3:50:31 PM
Reply:

Similar Artilces:

Error: "This record is read-only in Microsoft CRM"...
We have recently implemented MS CRM 3.0 with the CRM-Outlook client (on-line mode). Whenever I have Outlook open, I am frequently annoyed by the following error message dialog: "This record is read-only in Microsoft CRM. Changes will not be synchronized to the Micrsoft CRM system." Other team members experienced this same message initially, but then it stopped for them. However, I continue to get this message. Note the following: 1. I have CRM - Administrator rights 2. Using CRM 3.0 3. Using Exchange 2003 5. Installed on-line version of CRM-Outlook 4. Have checked/un-check...

Padding a Number
I have a text column of numbers that range from 6 to 9 characters in length. I need to left pad with zeros so that all of the numbers are 9 characters in length. Can you tell me the function I should use and possibly provide an example? TIA Chris "Chris" <chris.brown@sheppard.af.mil> wrote in message news:0c8601c34b9a$fd2b5c70$a101280a@phx.gbl... > I have a text column of numbers that range from 6 to 9 > characters in length. I need to left pad with zeros so > that all of the numbers are 9 characters in length. Can > you tell me the function I should use and p...

HELP RE: DISPLAY CHECK NUMBER ON PAYEE REPORT WITH
How do I get a historic payment reoport for a specific payee including check number, date, amount, and payee name? ie: "Car Loan Payment history for the period 1/1/01 thru today" Payee: Check # : Date: Amount: (and perhaps a running total or a final total) I think I have tried all the built in reports and can't find one to do this or modify existing to do this. I see this information in the "view payee details" window, listed in the format I require but when I select PRINT all that prints is the bar graph that is also displayed on this page. I ca...

negative numbers in the allocation
I have a client that is getting negative numbers in allocation in inventory, how do I remove it. Thanks, Scott KnowledgeBase Article #850365 tells you how to fix the negative quantities allocated in inventory. Eleni "Scott Rusoff" wrote: > I have a client that is getting negative numbers in allocation in inventory, > how do I remove it. > > Thanks, > Scott > ...

How do I stop Excel from changing simple numbers to their 1/100th.
I'm working with the latest service pack of Excel 2000. Every number I enter into any blank cell becomes that number's 100th decimal value. For example, typing "2" in a cell results in "0.02" when I leave the cell. This did not used to happen, but I don't know what I did to make it happen, nor what to do to stop it from happening. CC hi goto 'Tools - Options - Edit' and uncheck 'Fixed decimals' -- Regards Frank Kabel Frankfurt, Germany Chris C wrote: > I'm working with the latest service pack of Excel 2000. > Every number I ente...

MS Access Copy record to another table based on a form input 10-08-07
Hello. I have 6 Tables, all with identical fields. I want to copy a record from one table into another table, which table depends on what table is selected in a form. So if the table names are One, Two, Three, Four, Five, Six, and the record that I want to copy and insert into another table has been selected by a query in a form. I want the form to direct where to insert the table. So if the form has table Two selected (from the drop down list) then it should insert the record into table Two. If the form has table Four selected then it should insert it into table Four. The table the record is...

Counting days between dates
I am trying to calculate working days late or early between two dates using =NETWORKDAYS(A2,B2) If I have a due date of 24 Sept and I ship on 24 Sep then I am neither late or early so I am on time i.e. 0 days - but my formula returns 1! If I have a due date of 24 Sept and I ship on 23 Sep then I am shipping 1 day early i.e. –1 – but my formula returns –2 formula due actual days early late should be 24-Sep 24-Sep 1 0 24-Sep 25-Sep 1 1 24-Sep 23-Sep -2 -1 What do I need to do...

Reverse engineering huge code base
Hi I have a huge code base in VC++, which I need to reverse engineer and come up wit a low-level design for. I understand that there might be some tools available to com with the class diagrams like Rational Rose or Visio. But can I come up with the sequence diagrams as well? What is the best possible way to reverse engineer suc huge code chunks TIA Sucharit Genitor sells such a product. I'm not taken by it (it imposes bizarre restrictions on how comments are placed; for example, it misinterprets my change log as a module description) but what I saw of its ability to reverse-enginee...

Invoice Number #3
I created my own invoice using a worksheet since I could not get the invoice template to do as I wanted. Can someone please tell me how to assign a changing invoice number cell? Can I make it start with whatever number I want or must it start with 1? Thank you in advance, this group has been extremely helpfull. Gary Hi Gary, http://www.mcgimpsey.com/excel/udfs/sequentialnums.html -- Kind regards, Niek Otten Microsoft MVP - Excel "R2D2" <someone@microsoft.com> wrote in message news:JzYHg.13382$Nz6.105@edtnps82... |I created my own invoice using a worksheet since I coul...

the end of long numbers turn to 0s how do i stop that?
5489 4861 3481 5448 8461 turns to 5489 4861 3481 5440 0000 how do i maintain those numbers? XL's stored precision is limited to 15 decimal digits. If you're not doing math on them, pre-formatting the cell(s) as Text or prepending the numbers with an apostrophe when you enter them will keep XL from parsing them and dropping the last digits. If you need to do math on them, you'd best use a different application. Otherwise you'll have to enter them as Text and use string manipulation to do the math piece-wise. In article <209F3B0E-80F6-41AD-A510-EEDAD5625797@microsof...

Ability to Change PO Number When Generating PO From Sales Order
When generating a Purchase Order using the SOP to POP link (from Sales Transaction Entry) would like the ability to change the next PO Number. ...

Number of copies when printing is 256, how can I set it back to 1?
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel It's only with one Excel document. I've already trashed the prefs and that didn't fix it. high quality Soccer jerseys NBA Jersey tracksuit and jackets, GHD hairstraightener supplier from www.willpa.com Are you a Retail businessman who bother by the purchase price? China Cheapest TOP wholesale website can help you we are specialize in replica sport goods manufacturing in china, we can offer you all kinds of soccer jersey, NBA jersey,shoes and so on. they are the best brand replica goods w...

De-Duping Customer Records
Hi, I'm new to the system but searched in the Help as well as this newsgroup. We have several customers that have called in and the clerk did not check for a previous record, created a new one. Now I have many many customers with duplicate records but not the purchase history. Is there a way to de-dupe the records and keep the history? -- Thanks,Jan There are no tools for this built into the system. This can be done, but it may be painful. If you are doing On-Account Tenders, it will be especially painful, as you will need to merge both the purchase history and account activ...

Query Criteria 02-10-10
I have a query in which I only want the top 10 stores for item velocity for multiple groups. I have used the top value and max criteria but it only brings back the top or max for the entire dataset, not for each group within the data. What am I missing??? Post your table and field names with datatype that you would use in the query along with sample data (need not be real but workable). -- Build a little, test a little. "gabbyccf" wrote: > I have a query in which I only want the top 10 stores for item velocity for > multiple groups. I have used the top...

Choosing unlocked new record
I am trying in a multi-user environment to have each user able to add data to the next available, unlocked record. I am thinking this is a query, but not sure. I am set to share the database with record-level locking. I have a query that finds the next available record, but when another user runs the query, it chooses the same record even though another user is entering data. Obviously it creates an error later, but I want to avoid the error altogether by having each sequential user that runs that query to enter data on the next, unlocked (unused) record. Can anyone help? I am no...

Restrict user access to their own records and associated entities
Hi all, I have a user - and they can correctly 'see' only their 'Contacts' But I have created other entities - lets say a 'product' that all users can see. And as each 'Contact' can be interested in more than one 'product' and each 'product' can have more than one 'Contact' interested I've created another entity called 'ContactProducts' to link the two. It basically has only two fields, a referential 'lookup field' to each of 'product' and 'Contact. The entities ('product' and 'ContactProduc...

Code 17 Dup Record on file FA_Retirement_Mstr
V. 7.00g using the FA module only (not interfacing with GL) Retired 600+ assets. Un-retired to correct proceeds. Re-retired and the first 390 went fine, now every asset we try to re-retire gives us this error. What can we do? Thank you, Robert ...

Publisher 2003 is missing page numbers when I convert to pdf
I have a publisher 2003 documents of 124 pages with several master pages. I can see and print page numbers centered at the bottom of the page using publisher, but when I convert to a pdf using Acrobat Pro 7.1.0 the page numbers don't convert. Updated both today prior to conversion. Several master pages in the doc. Any ideas what is going wrong? Be certain you don't have "Ignore Master Page" checked. Don't know what is going on. I've tried to duplicate this but I can't. I created a 200 page document, I put the page numbers on the bottom centered. I creat...

Calculate change from record to record
Dear All, in my database, I am recording projects in different categories. I have now created a crosstab query that sums up all project per category and year, including a total number of projects per year. How can I also show, how the total number is changing compared to the previous year? Basically, I am looking for something like (No of projects of previous record's year-No of projects of current record's year)/(No of projects of current record's year) at the end of each record. A B C Total Change Y1 3 2 5 10 0 % Y2 1 7 6 ...

Control to populate data based on code
Hi, I have created a form linked to the table - Master table. The form contains a control - Validation. I had given an event to this conttol - Private Sub Validation_Click() If [Report Currency] = USD Then Validation = YES End If However, I am not getting any results in the form nor is the result being carried to the table. Pls help In this line: If [Report Currency] = USD Then It is expecting [Report Currency] to be a control on your form and USD to be a variable. Is that what you are expecting? -- Dave Hargis, Microsoft Access MVP "Newbie_Sashi" wrote: > Hi, > I ...

Median with Criteria
Hello, I want to write a formula that calculates the median of a set of values specified by some criteria. For example, if you have the following in A and B: 1 1 1 2 2 3 1 7 2 6 1 8 1 9 2 4 2 3 2 4 1 5 1 6 2 3 I want to calculate the median of the values in column B that have a 1 in column A (in this case, median = 6). Does anyone have a good idea for how to do that? Thanks, Hi, Try this ARRAY formula =MEDIAN(IF(A1:A13=1,B1:B13)) This is an array formula which must be entered by pressing CTRL+Shift+Enter and not just Enter. If you do it correctly then ...

Last Day of a Week based on NOW()
Good evening all, Just a quick question. How would I work out what all the days in a wee are, based upon the current date. So, have 7 cells, which would work out what their date is, based o today. Example. Today is Thursday, 24th June. Monday- 21/06/2004 Tuesday - 22/06/2004 Wednesday - 23/06/2004 Thursday - 24/06/2004 Friday - 25/06/2004 Saturday - 26/06/2004 Sunday - 27/06/2004 Is this actually possible? With vague thinking, I beleive it might be but I'm not exactly sure how I'd go about working it into a formula Maybe a helper column that would place the the current date into th...

CountIf
I'm trying to use CountIf to count how many times in the below exampl Up occurs in Column A and Low occurs in column B. With the belo example it should return 2. Can someone help me out. Thanks. A B 1 Up Low 2 Up Low 3 Up High 4 Down Hig -- Message posted from http://www.ExcelForum.com Use a formula like the following: =SUMPRODUCT((A1:A4="Up")*(B1:B4="Low")) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Lintz >" <<Lintz.14wtej@excelforum-nos...

Number formating #2
I have a column of numbers, all 6 digit. Some of the numbers are six digit with a dash in between the third and fourth digit Ex. 123-456 122-451 122771 122458 233-564 ....and so on Is there a way to remove the dashes from these numbers that have dashes and leave the other ones alone? What I want is this column to not have any dashes. is concentate the formula to use?? Thanks On Tue, 29 Nov 2005 11:01:05 -0800, "croozer" <croozer@discussions.microsoft.com> wrote: >I have a column of numbers, all 6 digit. Some of the numbers are six digit >with a dash in bet...

Automate Account Numbers
Hi, I posted this but couldn't see it. So here it is again. I want to automate account number allocation when creating new accounts. Is this possible? Am I able to create rules for the account number? Also if everything is possible with luck, can we do so for the accounts we are going to migrate from Goldmine, though they didn't have any account numbers. Silly questions. Appreciate your help. Tony ...