Using "Count" in Queries

I'm having trouble with a query I'm working on. I am running it with multiple 
criteria and it works perfectly fine. However, when I change the "Total" row 
from "Group by" to "Count" the query comes up blank. The only time the query 
performs as it should, with the counts, is when I delete the criteria for all 
the other fields. How can I get the count to work while still keeping the 
specific criteria the same?

Thank you in advance for the help!

0
Utf
4/6/2010 6:35:24 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
854 Views

Similar Articles

[PageSpeed] 9

Tim wrote:

>I'm having trouble with a query I'm working on. I am running it with multiple 
>criteria and it works perfectly fine. However, when I change the "Total" row 
>from "Group by" to "Count" the query comes up blank. The only time the query 
>performs as it should, with the counts, is when I delete the criteria for all 
>the other fields. How can I get the count to work while still keeping the 
>specific criteria the same?


Post a Copy/Paste of your Select query's SQL view.  Include
an explanation of what you are trying to count.

-- 
Marsh
MVP [MS Access]
0
Marshall
4/6/2010 6:53:14 PM
On Tue, 6 Apr 2010 11:35:24 -0700, Tim <Tim@discussions.microsoft.com> wrote:

>I'm having trouble with a query I'm working on. I am running it with multiple 
>criteria and it works perfectly fine. However, when I change the "Total" row 
>from "Group by" to "Count" the query comes up blank. The only time the query 
>performs as it should, with the counts, is when I delete the criteria for all 
>the other fields. How can I get the count to work while still keeping the 
>specific criteria the same?
>
>Thank you in advance for the help!

Correct the error in the query.

If you would like help doing so please open it in SQL view and post the SQL
text here. Can't fix what we can't see!

I'm going to GUESS that you may want to use the "Where" operator on the totals
line on the fields you're using for criteria; if you also want to group by or
count them, you may need to include the field twice. But not knowing anything
about the table, the query, or the data it's hard to say for sure.

-- 

             John W. Vinson [MVP]
0
John
4/6/2010 7:01:20 PM
Reply:

Similar Artilces:

Inventory Count Unit of Measure
I would like the ability to specify a unit of measure for inventory stock counts. Because of rounding, consumption units, bill of material, and other issues base units of measure can be very different than the purchasing units which leads to counting confusion. Especially true of length, area, weight, and volume measures. For instance I have items purchased and inventoried by 55 gallon drum and consumed in milliliters. To avoid rounding and other issues, the base unit of measure is milliliters. I would like to be able to specify the inventory unit of measure for stock counts s...

When to use contact and account
We are working on MS CRM for a retail bank. It has both individual customers and corporate customers. Considering the requirement we thought of storing both of them in the Account entity. Is this a good approach or will it have any impact on the OOTB functionality. Should we be using contact for Individual customers? Thanks, MVJ On Jan 12, 7:47=A0pm, mvj123 <mvj...@discussions.microsoft.com> wrote: > We are working on MS CRM for a retail bank. It has both individual custom= ers > and corporate customers. Considering the requirement we thought of storin= g > both of them i...

Counting instances of a time in cells with date AND time.
Hi all, I'm kind of new and in a bind. I have one worksheet that has column of cells that have a date and time. For example. A 1 Time_Entered 2 4/5/04 9:00:00PM 3 4/6/04 7:40:00AM ... And it goes down. Now, in another worksheet (but you can assume th same worksheet for explaining's sake), I have to count how many time Column A had a time of 7:00:00AM. Any time between 7:00 and 7:59, i other words. I don't know how to count it with the date also in th way. I CAN'T make a new column that has HOUR(A:A) however, that's wha ...

Is it possible to Sort by Item Count after grouping?
When sorting by a column header in Outlook (i.e. Subject, From, etc...) the items are grouped by the header and the count of items is displayed as (x items). Is there then a way to then sort by count of items so the largest groupings with the most items are at the top? This could really help with cleaning up my Inbox as I could identify groups of messages with the same subject or from some person ordered by the largest grouping and work on those first. Any help is greatly appreciated!! -- sramsay As far as I know, no. "sramsay" <sramsay.34d99b8@outlookbanter.com> wro...

counting based on another criteria
I have two columns, one contains a list of job titles the other a lis of responses to a question: A B Clerk Clerk Analyst Clerk Analyst Enginee -- Message posted from http://www.ExcelForum.com Hi and what do you want to count?. You may have a look at the COUNTIF function or SUMPRODUCT. >-----Original Message----- >I have two columns, one contains a list of job titles the other a list >of responses to a question: >A B >Clerk >Clerk >Analyst >Clerk >Analyst >Engineer > > >--- >Message posted from http://www.ExcelForum.com/ > >. &...

Count function
I would like to know if it is possible to use the count function an have it not count all rows/columns that are hidden. Thank you all fo your help -- Message posted from http://www.ExcelForum.com If you are using a filter use =SUBTOTAL(2,Range) if not you need VBA, do a google search for [count hidden rows] -- Regards, Peo Sjoblom "starjay" <starjay.yabqy@excelforum-nospam.com> wrote in message news:starjay.yabqy@excelforum-nospam.com... > I would like to know if it is possible to use the count function and > have it not count all rows/columns that are hidden....

Retrieving Notes from an Account AND its Contacts using AF
Hi all, Can you please tell me if this can be done using Advanced Find in 3.0 or 4.0: I am simply trying to return all the notes from an account and also from its contacts in the same query. I guess this would require some kind of join to tell AF that the account and contacts are related. Could this maybe be done with the new report wizard in CRM 4.0 by entering the account name as a parameter? Any help is appreciated, thanks. Mohamed This isn't possible in V3 or V4 with advanced find. The V4 report wizard only allows you to join two entities - you need to join 3 entities - Account...

Replying with an alternate e-mail that uses the same gmail account
I work for several businesses and have multiple e-mail addresses that all forward to my personal gmail account. I have dealt with incoming mail forwarding to the proper folder depending on the e-mail address the sender uses, but when I reply I either have to type the right address into the "From" box (pain) or it shows up as coming from my gmail account even though they sent it to one of my aliases. Can I set up a rule that all replies to e-mails sent to an alias use the same alias for the response? Ideally I'd like to be able to have an e-mail conversation app...

Count Rows with Conditional Format?
Excel 2000 ... Approx 40 Columns x 5000 rows. Conditional Formatting in various Columns set to high-lite cells (pattern RED) containing suspect data requiring attention. Above said ... I need to count the number of Rows that contain any cells (single or multiple) containing a RED pattern activiated by Conditional Formatting. Regardless of how many cells may be high-lited in a single row ... I need the row to count as "1" ... so I can get a total count of rows with suspect data. If this can be done ... I know it can be done here ... Thanks ... Kha One suggestion: inse...

Counting Occurrences
I am trying to set up a formula than counts the number of times on one row that there is a cell containing a particular letter (s) with a blank cell to teh left of it. I have tried all the help that I can find, but no joy. Is there anyone out there with any bright ideas? Thanks "James Burstall" <burstallj@triage-services.com> wrote in message news:0cff01c3af80$23a29c80$a401280a@phx.gbl... > I am trying to set up a formula than counts the number of > times on one row that there is a cell containing a > particular letter (s) with a blank cell to teh left of it. >...

count #3
I have a table that shows all the employees that have been termed. I want to count how many different codes each manager has used in a given amount of time. ie: bob termed 10 employees using codes 2,3,5,9,12,2,5. what formula can I use to show me: two terms code 2, two term code 5 an so on for a total term of 7 employees Have a great day Your sample layout or try to count bob for reason 2 =sumproduct((a2:a22="bob")*(b2:b22=2)) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "PR Kid" <PRKid@discussions.microsoft.com> wrote in mes...

rename administrator account can't using smtp services
Dear all, i have a problem on ex2003, when i rename the "administrator" login to "roots". after install ex2003 is completed, however i did using pop3 server recevied email is ok, but when i try to send out email is not work, always prompt the password dialog box. i did using another account is work send out and recevied. Pls advise!!! I'm sorry, I don't understand your question. Perhaps if you asked it in your native language, someone could figure it out. "Stanley" <stanley@microosft.com> wrote in message news:eDMlPzLgGHA.4940@TK2MSFTNGP05....

Accounts used in Spending By Catagory
I have two checking accounts set up in Money Plus. One is my family checking and the other is one I use for business expenses. The spending tracker uses transactions from both accounts. How can I set the Spending by Catagory to only use my family checking? Thanks for any input. In microsoft.public.money, Billster15 wrote: >I have two checking accounts set up in Money Plus. One is my family checking >and the other is one I use for business expenses. The spending tracker uses >transactions from both accounts. How can I set the Spending by Catagory to >only use my famil...

Counting data in columns
ok ive struggled with this all morning and i cant do it i have 2 columns A and B A can contain any of the following RTB, In Progress, Live B contains a Date but can also be empty I want to count column B (dates) where Column A is equal to RTB but i have no idea how to do this Example of Spreadsheet A B RTB 01/01/2003 Live 01/02/2003 RTB 01/02/2003 RTB 01/12/2003 Live 02/12/2003 RTB RTB 01/01/2003 Live 01/10/2003 Total RTB count ...

counting total cells with text
Hi , I have a column with text and blanks and I am trying to count the total number of cells with text. I searched through this site and found something with COUNTA() - COUNT() but this doesn't work because I have no numerical values. Any ideas are sincerely appreciated Thank you -- peace ------------------------------------------------------------------------ peace's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27035 View this thread: http://www.excelforum.com/showthread.php?threadid=444459 One way =ROWS(A1:A20)-COUNTBLANK(A1:A20) -- bill k -...

Counting #5
I have problem in counting the total without the duplicate. For example, Fruit ------- Apple Apple Orange Banana Kiwi Pear Kiwi Banana I want to know if there is a formula in excel that can count how many fruits are in the column. It shoud exclude the duplicate items. Anybody can help. Thanks Eling Try the following... =SUMPRODUCT((A1:A8<>"")/COUNTIF(A1:A8,A1:A8&"")) OR =SUM(IF(A1:A8<>"",1/COUNTIF(A1:A8,A1:A8))) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article <#XdoIbFDFHA.3540@TK2MSFTNGP14.phx.gbl>, "e...

How to use COUNT using multiple ranges
I need the count of information based on criteria from differen columns. Example of columns: A B C D 0_F JJ 1 II 0_F JD 2 III 0_R JR 1 II 0_F JC 2 II Blank JP 1 III 0_F JK 1 III What is the count IF (Col A)=0_F and IF (Col D)=II I don't know how to combine the information to come up with the resul of 2 Thanks for any help -- Message posted from http://www.ExcelForum.com On 5/9/04 7:38 AM, in article LIZZIE.15zlef@excelforum-nos...

Counting Rows/Columns for Copying Formulas
I have two spreadsheets in a workbook as follows: Source Worksheet contains by customer each product it purchases and the sales for each product (see sample below). Not all customers buy each product. There are also new customers added/deleted each month and new products added/deleted each month. Column A Column B Column C January February..... Customer A Product Code 1 Customer A Product Code 1 100 Customer A Product Code 2 200 Customer A Product Code 3 300 Customer B Product Code 1 500 Customer B ...

Using COUNTIF (specific cells over multiple sheets) � can anyone help?
Hi I�d be really grateful if someone could assist me in how to �formula the below: I have twelve identically formatted worksheets (�A� through to �J�) In each of the cells B6:F6, B10:F10, B14:F14, B18:F18 & B22:F22 on eac sheet there will be a different one of 5 words (each word can and wil occur more than once within the above range). For the purpose of thi exercise I need to ignore the text in the other rows (eg 7-9, 11-1 etc). What I need to tally on a separate sheet within the workbook is ho many times each of the five words occur in the group above (eg B6:F6 B10:F10, B14:F14, B...

AVG(COUNT(timestamp))...this will not work, what am I doing wrong?
Please help me. I want to find the average of the number of timestamps that are in several tables. When i run the following, it will not work because I can only use avg with a number datatype. (at least, that's what i understand) I am just learning the syntax rules. the following works as long as i don't include avg. Please help me. Select avg( count(timestamp),area), datepart(hh,timestatmp), datepart(wd, timestamp) From lotsoftables where timestamp between 05/01/2007 and 06/20/2007 Group by timestamp,datepart(hh,timestatmp), datepart(wd,timestamp),area Thanks in advance. -Misty --...

Multiple Bins/Stock Count
Is there a way to not show zero quantities in Bin Quantity Inquiry and not be printed on the Stock Count forms? Do we have to individually remove items from bins (some items are no longer in certain bins)? Thanks in advance. Nevermind- we just need to use Tools > Utilities > Inventory > Remove Bins to purge the old data. ...

replying to messages using multiple email accounts
I just migrated to outlook 2003 from outlook express. I have about 5 seperate email accounts that I have configured in outlook 2003. My problem is that no matter which email address I receive email on, when I reply to the message it will always be sent from the default email account. I know that I can click on the accounts tab to select which email address I can send from, but I am forgetting to do this, and I am sending most of my replies from the default account. I want to be able to automatically reply to a message using the emaill address that the message was originally sent to. Does ...

FRx with Analytical Accounting using all the data
At the moment the only dimension codes that are pulled through are with data, it would nice if also include dimension codes with no data. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/de...

using hotmailaccount
Hi I want to use hotmail account e mail using Out lokk express See this archived post: http://groups.google.com/group/microsoft.public.outlookexpress.general/msg/c6d273087ccc0967 NOOR E ALAM wrote: > Hi > I want to use hotmail account e mail using Out lokk express Outlook Express can only access Hotmail accounts using pop3. Pop3 only downloads the Hotmail Inbox. -- Ron Sommer MS MVP-Mail "NOOR E ALAM" <protex.designs@gmail.com> wrote in message news:#mqQTSHlKHA.2160@TK2MSFTNGP02.phx.gbl... > Hi > I want to use hotmail account e mail u...

counting rows
I have 100 rows in a column A which are numbered 1 -100 in numical order. After I filter a particular item in column B which results in 20 rows (and these 20 item were scattered throughout the column), I would like column A to show 1 - 20 in numerical order. Try this... Assume the data in column B is in the range B2:B1010 with B1 being the header. Enter this formula in A2 and copy down to A101: =SUBTOTAL(3,B$2:B2) -- Biff Microsoft Excel MVP "Darryl" <Darryl@discussions.microsoft.com> wrote in message news:89190AE3-6D26-4F2C-9C2D-51EB5312CB1D@mi...