Count based on criteria

[Cecilia]

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???

 

[KARL DEWEY]

Post the SQL of query1.

 

[Cecilia]

SELECT [Top 50 Ships & Sales].[Item Nbr], [Top 50 Ships & Sales].[Store Nbr],
[Top 50 Ships & Sales].[UPC/Store Combo], [Top 50 Ships & Sales].[Range 2 POS
Qty], [Top 50 Ships & Sales].[Range 1 Gross Ship Qty], [Top 50 Ships &
Sales].[Range 1 Curr Str On Hand Qty]
FROM [Top 50 Ships & Sales]
WHERE ((([Top 50 Ships & Sales].[Range 1 Gross Ship Qty])>0));

 

[Michel Walsh]

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]

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

 

[Michel Walsh]

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


Hoping it may help,
Vanderghast, Access MVP