Count based on criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cecilia - 01 Nov 2007 17:02 GMT

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

Reply to this Message

KARL DEWEY - 01 Nov 2007 19:42 GMT

Post the SQL of query1.

Signature

KARL DEWEY
Build a little - Test a little

> I have the following query with the following:
>
[quoted text clipped - 17 lines]
>
> Any ideas???

Reply to this Message

Cecilia - 01 Nov 2007 21:00 GMT

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));

> Post the SQL of query1.
>
[quoted text clipped - 19 lines]
> >
> > Any ideas???

Reply to this Message

Michel Walsh - 01 Nov 2007 21:08 GMT

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

>I have the following query with the following:
>
[quoted text clipped - 17 lines]
>
> Any ideas???

Reply to this Message

Cecilia - 01 Nov 2007 21:23 GMT

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
[quoted text clipped - 3 lines]
> OnHand >=2, OnHand >=3
> 1010 3 3

1011 2 1

> 1012 0 1

> SELECT item, SUM(onHand), COUNT(*)
> FROM query1
[quoted text clipped - 48 lines]
> >
> > Any ideas???

Reply to this Message

Michel Walsh - 01 Nov 2007 21:32 GMT

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

Hoping it may help,
Vanderghast, Access MVP

> Item, Store No. are unique values. I don't want a sum of the OnHand I want
> to
[quoted text clipped - 75 lines]
>> >
>> > Any ideas???

Reply to this Message