Counting the Number of Sales on Items

D

Diggsy

Hello,

I have a very basic table in a database that that gives me the Item number,
The Store Number and The quantity sold of that item in each store. I am
trying to do a query that will tell me how many stores sold less than 24
items. Another column that says between 25 and 48 items. Another between 49
and 72 items. and Finally the count of all stores that sold over 72 items. I
have many items and many stores. Is there a way to do this. I tried using the
count tab ithe total section of the query.
This query is based on each item number totals and not all items for that
store

My table looks like this.

ItemNo Storeno Quantitysold
1234 2001 54
1234 2002 29
1234 2003 17
1234 2004 34
4321 2001 11
4321 2002 44
4321 2003 78

For instance,The item number 1234 would have a 2 in the column for the
quantity sold that is between 25 and 48

Than You
 
M

MGFoster

Diggsy said:
Hello,

I have a very basic table in a database that that gives me the Item number,
The Store Number and The quantity sold of that item in each store. I am
trying to do a query that will tell me how many stores sold less than 24
items. Another column that says between 25 and 48 items. Another between 49
and 72 items. and Finally the count of all stores that sold over 72 items. I
have many items and many stores. Is there a way to do this. I tried using the
count tab ithe total section of the query.
This query is based on each item number totals and not all items for that
store

My table looks like this.

ItemNo Storeno Quantitysold
1234 2001 54
1234 2002 29
1234 2003 17
1234 2004 34
4321 2001 11
4321 2002 44
4321 2003 78

For instance,The item number 1234 would have a 2 in the column for the
quantity sold that is between 25 and 48
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Here's one way:

SELECT ItemNo, StoreNo,
Count(IIf(QuantitySold < 24, 1)) As LessThan 24,
Count(IIf(QuantitySold BETWEEN 25 and 48, 1)) As Between25_48,
Count(IIf(QuantitySold BETWEEN 49 and 72, 1)) As Between49_72,
Count(IIf(QuantitySold > 72, 1)) As Over72
FROM <table name>
WHERE <criteria>
GROUP BY ItemNo, StoreNo

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQw+AA4echKqOuFEgEQKl8QCcDO9QBqITfC+8+76yoyoKVVfrkoQAn3+g
I4ae6o5IHDYtLkSn+holeNgE
=i/cH
-----END PGP SIGNATURE-----
 
D

Diggsy

Thank You for your help It works well especially The less Than and greater
than. My final problem is this. I actually need numbers that are <72 and
=48. When I try to do the between statement the numbers are not adding
Click to expand...
correctly. Is there another way? Thank you

Diggsy
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

BETWEEN X and Y means the values from X to Y, including X and Y;
therefore you'd change these lines:

Count(IIf(QuantitySold BETWEEN 25 and 48, 1)) As Between25_48,
Count(IIf(QuantitySold BETWEEN 49 and 72, 1)) As Between49_72,
Count(IIf(QuantitySold > 72, 1)) As Over72

to this:


Count(IIf(QuantitySold BETWEEN 25 and 47, 1)) As Between25_47,
Count(IIf(QuantitySold BETWEEN 48 and 71, 1)) As Between48_71,
Count(IIf(QuantitySold > 71, 1)) As Over71

(Since >= 48 and < 72 equates to BETWEEN 48 AND 71):

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQxNQs4echKqOuFEgEQIrrQCgoBV2qpzX0ZHE9pZfH+sQOGE81DAAoNYn
van6UngSE4PWUKqfqDh9DPOK
=UvrJ
-----END PGP SIGNATURE-----
 
Top