Count based on criteria

I have the following query with the following:

Query 1 has the following records:
Item
Store No. 
Ships
OnHand

ony records selected are those with ships >0

I'm trying to create another query from Query 1 
that lists the following:

Items     
OnHand (count stores >2)
OnHand (count stores >3)

Stores should not be displayed and I don't want to display values >2 or >3 
just the total count of stores that meet that criteria.

Any ideas??? 

 
0
Utf
11/1/2007 4:02:01 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
597 Views

Similar Articles

[PageSpeed] 18

Post the SQL of query1.
-- 
KARL DEWEY
Build a little - Test a little


"Cecilia" wrote:

> I have the following query with the following:
> 
> Query 1 has the following records:
> Item
> Store No. 
> Ships
> OnHand
> 
> ony records selected are those with ships >0
> 
> I'm trying to create another query from Query 1 
> that lists the following:
> 
> Items     
> OnHand (count stores >2)
> OnHand (count stores >3)
> 
> Stores should not be displayed and I don't want to display values >2 or >3 
> just the total count of stores that meet that criteria.
> 
> Any ideas??? 
> 
>  
0
Utf
11/1/2007 6:42:01 PM
SELECT item, SUM(onHand), COUNT(*)
FROM query1
GROUP BY item
HAVING COUNT(*) IN(2, 3)



That assumes that query1 has no duplicated couple { item, storeNo }

Since there can be 2, or 3 records, for one given item, you can get 2, or 3, 
values for onHand. I don't know which one of these you wanted. I assumed the 
sum was appropriate:

with query1 returning:


item        store        onHand
1010    aaaa            11
1010    bbbb            8
1010     cccc              9
1011     aaaa            5
1012      aaaa           1
1012    bbbb               4


the query will return:


1010           28       3
1012           5          2


the last column being the number of stores (which is either 2, either 3)



Hoping it may help,
Vanderghast, Access MVP



"Cecilia" <Cecilia@discussions.microsoft.com> wrote in message 
news:138ADCE9-4B0D-44FE-A91E-B7774F13C27B@microsoft.com...
>I have the following query with the following:
>
> Query 1 has the following records:
> Item
> Store No.
> Ships
> OnHand
>
> ony records selected are those with ships >0
>
> I'm trying to create another query from Query 1
> that lists the following:
>
> Items
> OnHand (count stores >2)
> OnHand (count stores >3)
>
> Stores should not be displayed and I don't want to display values >2 or >3
> just the total count of stores that meet that criteria.
>
> Any ideas???
>
> 


0
Michel
11/1/2007 8:08:01 PM
Item, Store No. are unique values. I don't want a sum of the OnHand I want to 
group by item and then show the number of stores that have at least 2 Onhands 
and the number of stores that have 3 on hands. 

So in the example below I would see:

item        store        onHand
> 1010    aaaa            11
> 1010    bbbb            8
> 1010     cccc              9
> 1011     aaaa            5
   1011      xxxx             2
> 1012      aaaa           1
> 1012    bbbb               4
> 
> 
> the query will return:
> 
>                 OnHand >=2, OnHand >=3
> 1010                3                    3    
   1011                2                    1
> 1012                0                    1 

"Michel Walsh" wrote:

> 
> SELECT item, SUM(onHand), COUNT(*)
> FROM query1
> GROUP BY item
> HAVING COUNT(*) IN(2, 3)
> 
> 
> 
> That assumes that query1 has no duplicated couple { item, storeNo }
> 
> Since there can be 2, or 3 records, for one given item, you can get 2, or 3, 
> values for onHand. I don't know which one of these you wanted. I assumed the 
> sum was appropriate:
> 
> with query1 returning:
> 
> 
> item        store        onHand
> 1010    aaaa            11
> 1010    bbbb            8
> 1010     cccc              9
> 1011     aaaa            5
> 1012      aaaa           1
> 1012    bbbb               4
> 
> 
> the query will return:
> 
> 
> 1010           28       3
> 1012           5          2
> 
> 
> the last column being the number of stores (which is either 2, either 3)
> 
> 
> 
> Hoping it may help,
> Vanderghast, Access MVP
> 
> 
> 
> "Cecilia" <Cecilia@discussions.microsoft.com> wrote in message 
> news:138ADCE9-4B0D-44FE-A91E-B7774F13C27B@microsoft.com...
> >I have the following query with the following:
> >
> > Query 1 has the following records:
> > Item
> > Store No.
> > Ships
> > OnHand
> >
> > ony records selected are those with ships >0
> >
> > I'm trying to create another query from Query 1
> > that lists the following:
> >
> > Items
> > OnHand (count stores >2)
> > OnHand (count stores >3)
> >
> > Stores should not be displayed and I don't want to display values >2 or >3
> > just the total count of stores that meet that criteria.
> >
> > Any ideas???
> >
> > 
> 
> 
> 
0
Utf
11/1/2007 8:23:01 PM

SELECT item, ABS(SUM(onHand >= 2)), ABS(SUM(onHand>=3))
FROM query1
GROUP BY item


Hoping it may help,
Vanderghast, Access MVP



"Cecilia" <Cecilia@discussions.microsoft.com> wrote in message 
news:421FAAF7-6CB1-4ACF-B904-C76628953301@microsoft.com...
> Item, Store No. are unique values. I don't want a sum of the OnHand I want 
> to
> group by item and then show the number of stores that have at least 2 
> Onhands
> and the number of stores that have 3 on hands.
>
> So in the example below I would see:
>
> item        store        onHand
>> 1010    aaaa            11
>> 1010    bbbb            8
>> 1010     cccc              9
>> 1011     aaaa            5
>   1011      xxxx             2
>> 1012      aaaa           1
>> 1012    bbbb               4
>>
>>
>> the query will return:
>>
>>                 OnHand >=2, OnHand >=3
>> 1010                3                    3
>   1011                2                    1
>> 1012                0                    1
>
> "Michel Walsh" wrote:
>
>>
>> SELECT item, SUM(onHand), COUNT(*)
>> FROM query1
>> GROUP BY item
>> HAVING COUNT(*) IN(2, 3)
>>
>>
>>
>> That assumes that query1 has no duplicated couple { item, storeNo }
>>
>> Since there can be 2, or 3 records, for one given item, you can get 2, or 
>> 3,
>> values for onHand. I don't know which one of these you wanted. I assumed 
>> the
>> sum was appropriate:
>>
>> with query1 returning:
>>
>>
>> item        store        onHand
>> 1010    aaaa            11
>> 1010    bbbb            8
>> 1010     cccc              9
>> 1011     aaaa            5
>> 1012      aaaa           1
>> 1012    bbbb               4
>>
>>
>> the query will return:
>>
>>
>> 1010           28       3
>> 1012           5          2
>>
>>
>> the last column being the number of stores (which is either 2, either 3)
>>
>>
>>
>> Hoping it may help,
>> Vanderghast, Access MVP
>>
>>
>>
>> "Cecilia" <Cecilia@discussions.microsoft.com> wrote in message
>> news:138ADCE9-4B0D-44FE-A91E-B7774F13C27B@microsoft.com...
>> >I have the following query with the following:
>> >
>> > Query 1 has the following records:
>> > Item
>> > Store No.
>> > Ships
>> > OnHand
>> >
>> > ony records selected are those with ships >0
>> >
>> > I'm trying to create another query from Query 1
>> > that lists the following:
>> >
>> > Items
>> > OnHand (count stores >2)
>> > OnHand (count stores >3)
>> >
>> > Stores should not be displayed and I don't want to display values >2 or 
>> >  >3
>> > just the total count of stores that meet that criteria.
>> >
>> > Any ideas???
>> >
>> >
>>
>>
>> 


0
Michel
11/1/2007 8:32:38 PM
Reply:

Similar Artilces:

Setting a cell's color based upon RGB values
Hello, I am trying to write a macro/function in Excel 2007 that will set a cell's color based upon RGB values passed in as arguments. So the function declaration will look like: Public Function SetCellColor(HEX2DEC(A1), HEX2DEC(A2), HEX2DEC(A3) ) ActiveCell.Cell.Interior.Color = RGB(100, 100, 100) End Function Then for a particular cell, say C12, I would set the cell to =SetCellColor( HEX2DEC(C1), HEX2DEC(C2), HEX2DEC(C3) ) Obviously, this doesn't work as the ActiveCell set's the active cell's color and not the cell that has the =SetCellColor set for it. Also, I ge...

How to create an "and" rule in Query Based Distribution Groups
Hi, With Exchange 2003 Query Based Distribution groups, is it possible to create an "and" rule? ie, all users who are based in "London" "and" have the first name "John"? Thanks, Curtis. -- Please reply to news group only. Thank you. Sure. (&(attribute1=blah)(attribute2=blah)) http://msdn.microsoft.com/library/en-us/adsi/adsi/search_filter_syntax.asp?frame=true -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "Curtis Fray" <xxx@xxx.com> wrote in message news:OjVc...

Access based enumeration ?
I have just turned this on for our 2003 file server... but am surprised that it seems to be working for only the immediate level under each share.... Is that a limitation of the tool - or do I have something set wrong??? I also have staff with shortcuts to files - (that are in folders they do not have rights to) - now with ABE enabled, they are getting a "can not find path" error - ANY recommendations would be greatly appreciated... any work arounds that we can do. We are also in the process of migrating the remainder of our Novell network over to AD soon - this w...

Simple Access counting queries
Hi, hoping someone can help a relative newbie with a pretty simple query. My database (Access 2007) has three tables: Customers Products Purchases (many-to-one links to both of the other tables, this is basically a linking table) I have two simple queries I'd like to get out of this database, but I'm a bit stuck on how to construct the SQL. Any direction you can give me would be helpful. 1. List of all customers who have purchased 2 or more products (or 3 or more products, or 4+, etc.) 2. List of all customers who have purchased both Product A and Product B (or A, B, and C, or B an...

What is 'Align Text to Base Line Guide'?
Trying to make sure all lines have the same amount of space between them I selected paragraph from the format menu and under the line adjustments there is a box 'align text to base line guides' Ok I did that, now there are lines at the margines of my newsletter. What are these? Also selecting this pushed my text down from the top edge of the text box. What is this? The baseline guide measurements are in the Arrange, Layout Guides, Baseline tab. There is help in the Help menu. -- Mary Sauer http://msauer.mvps.org/ "Go_Girl3647" <GoGirl3647@discussions.microsoft.com...

Word 2007: word count wrong?
Hello, I have an issue with some .doc files when opening in Word 2007. In some cases the word count in the status bar is different of the word count of the "Word Count"- window (CTRL+SHIFT+G) or the word count in Word 2003 Example file: http://go.microsoft.com/fwlink/?LinkId=79595 Word 2007 (status bar) show 61019 words Word 2007 (Word count window) show 61010 words Word 2003 also show 61010 words This issue I have not with all documents, but only with some files and it seems that I have this problem only with .doc files but not with .docx files. (installed ve...

Dsum Problem with date criteria
Hi all, Kindly solve my problem. I have 2 tables stock_received , stock_utilized. stock_received fields -- date,3pcs,4pcs,boxes. stock_utilized fields -- date,3pcs,4pcs,boxes. I have successfully made a query to get the the current stock in hand as select sum(3pcs)- dsum("3pcs","stock_utilized"), sum(4pcs)-dsum("4pcs","stock_utilized"),sum(boxes)-dsum("boxes","stock_utilized") from stock_received ; Here i have a complex problem, I cant make query with date criteria. for example 01/05/2010 to 30/05/2010 ...

Count # of cells b/w cells ...
Hello, I have the following data in a column: 7 0 0 0 7 0 0 0 0 0 7 0 0 7 0 0 0 0 0 0 7 etc. The number of zero's between the 7's is random. I want a formula tha would count the number of zeros between the 7's. Thanks, Ari Bar -- AriBar ----------------------------------------------------------------------- AriBari's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2504 View this thread: http://www.excelforum.com/showthread.php?threadid=38806 Assume A5:A20 is the data, try this: B5 = A5+B4 (copy formula down) Now make a table with 2 column...

Counting + and
Hi, I have a long column of #'s that consist of positive and negative #'s of differing values. I am looking for a simple formula that will count the number of #'s that are negative and divide that by the number of values that are positive. IS there an easy way? thanks! -- RalphSE ------------------------------------------------------------------------ RalphSE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29931 View this thread: http://www.excelforum.com/showthread.php?threadid=501185 =COUNTIF(Range,"<0")/COUNTIF(Range,">...

count if a match occurs
Hi! I have two rows - say Row 1 and Row 2. In the first row I have a answer key. In the second row I have answers from a student. I would like to write a formula where it counts how many answers student got write. Here is a example: Row 0: Q1 Q2 Q3 Q4 Q5 Q6 Row 1: 1 4 2 3 4 1 Row 2: 3 4 1 3 4 1 So student marked 4 questions correctly: Q2, Q4, Q5 and Q6. Hence, the formula should return 4. I know one way to do it but I am sure there is more efficient way to do it. The way I know: Use if statement to compare two corresponding entries and output 1 if...

counting nonblank rows
I'm trying to figure out how to integrate the number of nonblank rows within a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1), but that does not give the correct result. Any help that you all may offer would be greatly appreciated. Try this: =Counta(C6:Z29) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "stumped" <stumped@discussions.microsoft.com> wrote in m...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

count cells in autofilter
while working in an excel spreadsheet, i use the autofilter tool to sort data. on some spreasheets it will list the total number of items found from the autofilter (ie., 10 of 15 records found. How do i get this to display automatically each time? If your list has many formulas, the Status Bar might show "Filter Mode" instead of a count of the visible records. You can use the Subtotal function to count the visible rows. The Status Bar may also show 'Filter Mode' if a cell in the list is changed, after a filter has been applied. The following MSKB article has inform...

Count Age Grouping
I have an access 2k database in which I need to count groups of records of individuals by that age groups such as 14- 20 no of individuals 21-30 no of individuals 31-40 no of individuals 41-50 no of individuals 51-60 no of individuals 61-70 no of individuals 71-80 no of individuals 80+ no of individuals I have both DOB and Age fields in the table I have tried several queries but with no luck and ideas On 19 Mar 2007 16:51:49 -0700, "Nemesis_uk" <nemesis_uk@ntlworld.com> wrote: >I have an access 2k ...

Count the text in a column
I would like to count the text in a column then for it to add a figure in another cell if it meets the text criteria Thanks! Do you mean count the characters? If so =SUM(NOT(ISNUMBER(A1:A20))*LEN(A1:A20)) as an array formula (committed with Ctrl-Shift-Enter) -- HTH RP (remove nothere from the email address if mailing direct) "Peter Curtis" <PeterCurtis@discussions.microsoft.com> wrote in message news:94C093C4-38DC-4989-846A-9352F3298B7C@microsoft.com... > I would like to count the text in a column then for it to add a figure in > another cell if it meets the t...

Using function based on "Font Color"
Could you please inform me if there is a function which can count and/or summarize cells in excel based on "Font Color" Function CountCells(rng As Range, ci As Long) For each cell in rng If cell.Font.Colorindex = ci then CountCells = CountCells +1 End If Next cell End Function =CountCells(A1:B10,3) -- __________________________________ HTH Bob "danai_mp" <danai_mp@discussions.microsoft.com> wrote in message news:9AD54C60-3CFD-4CBB-B32F-37B5E1220358@microsoft.com... > Could you please inform me if there is a function w...

Enable/Disable a Form Control Based on Security Group Permissions
How do I enable or disable a control in a form based on a user’s security group membership? For example: If I have a checkbox on a form (call it box1), I want box1 to be enabled if the user who opened Access is a member of a security group called “Breaker Test Admin.” For members of any other group (except of course “Admins”), box1 should be disabled. Thank you, for your help! On Mon, 02 Jul 2007 18:57:13 GMT, "BenS" <u35527@uwe> wrote: >How do I enable or disable a control in a form based on a user�s security >group membership? For example: If I have a checkbox on a ...

filling fileds in a form based on criteria
Hi, I have a form that edits a table. What I am trying to do is when a certain filed is filled, two other fileds are fillled based on the first field. What it is exactly is a golf form so when I input the course id it fills the course name and tees played from. Table is like this Date Course ID Course Tees Player Score I enter a date and the a course id. what i want is when i enter the course id, that the course and tees fields be updated. The course id is linked to another table that has course name and tees. I can do it by using an iif staement in the ...

EXTRACTING UNIQUE RECORD BASED ON CONDITION
This is a multi-part message in MIME format. ------=_NextPart_000_0012_01C781BF.08FB92F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello everyone! I would like to extract unique records based on a condition. For = example, how to extract unique record from column 'B' when column 'A' = has "AP" or any other desired condition. The data is as follows: A B =20 MI 70056542=20 MI 70056543=20 AP PATRICK CUDAHY INCORPORATED=20 AP PATRICK CUDAHY INCORPORATED=20 AP SUGAR CREEK ...

Counting consecutive numbers in a list
I'm looking to take a list of twenty numbers and count the number of times in that list two or three consecutive numbers appear in it. Any ideas? Can you post some sample data and let us know what result you expect? -- Biff Microsoft Excel MVP "Wiseguy91" <Wiseguy91@discussions.microsoft.com> wrote in message news:FA5AD186-5CD1-4028-B0CA-111B7E67A110@microsoft.com... > I'm looking to take a list of twenty numbers and count the number of times > in > that list two or three consecutive numbers appear in it. Any ideas? ...

Counting the number of times more than 1 variable occurs
I need to determine how many Separations were processed by a particular salesperson. A | B 1 MIKE | SEPARATION 2 MIKE | LEAVE 3 SARA | SEPARATION 4 JOE | SEPARATION 5 JAMIE | LEAVE 6 MIKE | LEAVE 7 JOE | LEAVE 8 SARA | SEPARATION The only way I can think of is the COUNTIF Funtion *COUNTIF(-range,criteria-)* =COUNTIF(A1:A8,(A1:A8="MIKE")*(B1:B8="SEPARATION")) This does not work though. The results return 0 with no errors when the answer should be 1. does anyone else know of a wa...

Exchange 2003 and OWA broken after Base Line Security Analyzer run.
Over the weekend I ran the MS Baseline Security Analyzer 1.2 and it recomended MDAC and MSXML updates, removing Parent paths for websites in IIS, and deleting some items in IIS. After doing these things, users can no longer log into OWA. Symptoms: When going to the website, the login box pops up and we can enter our username and password. After that, there is no connection and I get a 404 - File not found error. I try to connect to OWA directly on the server and it will show the one vertical blue bar, but that is it. The IE throbber just keeps spinning until I press the stop button. Troubles...

Count down to Words / Text
Hello, Is there a way to have a cell that is counting down from "10" and when it hits "0" spell the word FULL and in Red? example: cell BB10 has a formula in it: =COUNTA(H10:AV10) cell BB8 has a formula in it: =SUM(10-BB10) NOTE: there are only 10 entities max in cells H10 through AV10 and that is why I subtract BB10 from 10. I want cell BB8 to reflect the word ALL when there is 10 and FULL when the countdown hits "0" Can this be done? Thanks, Champ Try this....... =IF(SUM(10-BB10)=10,"ALL",IF(SUM(10-BB10)=0,&qu...

count memo characters
Hi, how can count charactrs in memo filed? thanks In what context? In a query, you could type something like this into the field row in query design: Len([MyMemo]) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "--- MSh ---" <mashshab@hotmail.com> wrote in message news:u7kJor29HHA.5464@TK2MSFTNGP02.phx.gbl... > Hi, > how can count charactrs in memo filed? > thanks --- MSh --- wrote: > If Len(Me.CC) = Null Then > is it tr...

How can I count the number of months between 2 dates?
For instance, I am trying to compute how many months are left in leased equipment contracts. I have a spreadsheet with the ending dates for all the individual contracts. What I would like to be able to do is to calculate the number of months between now and the end of the contract. I appreciate any help. Thanks Tom One way: =DATEDIF(TODAY(), A1, "m") In article <DCBFA6A3-A4D6-4AAB-ABFE-5C4659CECED9@microsoft.com>, Tom Bradstreet <TomBradstreet@discussions.microsoft.com> wrote: > For instance, I am trying to compute how many months are left in leased &...