Count number of orders and number of different itemnumbers

[Anne]

Hi

I have got a table with some sales transactions. It has got 3 fields
1) Ordernumber
2) Item number
3) Shippingdate

Now I want to make ONE (not two) query that shows how many different orders
quarterly, and at the same time how many different item numbers used in each
quarter.

Each record has a different ordernumber, so it is just to count the lines
and grouped by each quarterly, to find the number of orders, But my problem
is, how do I count the number of differt itemnumbers at the same time. In
other words I want to become the following result in one querry:

Quarter: # Orders: #Items
1 200 15
2 130 24
3 etc. etc.
4

Thanks in advance
Henry

[paii, Ron]

Use a Summation query grouped on Quarter, count Orders and Items.

"Anne" <av*@telmore.ru > wrote in message
news:3f******** *************** @dread16.news.t ele.dk...

Hi

I have got a table with some sales transactions. It has got 3 fields
1) Ordernumber
2) Item number
3) Shippingdate

Now I want to make ONE (not two) query that shows how many different orders quarterly, and at the same time how many different item numbers used in each quarter.

Each record has a different ordernumber, so it is just to count the lines
and grouped by each quarterly, to find the number of orders, But my problem is, how do I count the number of differt itemnumbers at the same time. In
other words I want to become the following result in one querry:

Quarter: # Orders: #Items
1 200 15
2 130 24
3 etc. etc.
4

Thanks in advance
Henry

[Anne]

Hi Ron

I'am not realy sure I understand.

An example:
I got theese 3 records:
[Date] [Ordernumber] [Itemnumber]

20031104 1234 12
20031104 4567 12
20031104 8910 9

Runing the query I want the following result:

Quarter # of orders # of different items
4 3 2

Best regards
Henry

"paii, Ron" <pa**@packairin c.com> skrev i en meddelelse
news:vq******** ****@corp.super news.com...

Use a Summation query grouped on Quarter, count Orders and Items.

"Anne" <av*@telmore.ru > wrote in message
news:3f******** *************** @dread16.news.t ele.dk...

Hi

I have got a table with some sales transactions. It has got 3 fields
1) Ordernumber
2) Item number
3) Shippingdate

Now I want to make ONE (not two) query that shows how many different

orders

quarterly, and at the same time how many different item numbers used in

each

quarter.

Each record has a different ordernumber, so it is just to count the lines and grouped by each quarterly, to find the number of orders, But my

problem

is, how do I count the number of differt itemnumbers at the same time. In other words I want to become the following result in one querry:

Quarter: # Orders: #Items
1 200 15
2 130 24
3 etc. etc.
4

Thanks in advance
Henry

[Roger]

assuming your table is called 'tblSales'

create a query "qryOrdersByDat e"
SELECT tblSales.shippi ngDate, Count(*) AS orderCount
FROM tblSales
GROUP BY tblSales.shippi ngDate;

create a second query "qryUniqueItems "
SELECT DISTINCT tblsales.shippi ngDate, tblsales.itemNu mber
FROM tblsales;

the final query "qrySales" will give you what you need
SELECT tbl1.shippingDa te
, (select orderCount from qryOrdersByDate as tbl2 where
tbl1.shippingDa te=tbl2.shippin gDate) as orderCount
, (select count(itemNumbe r) from qryUniqueItems as tbl3
where tbl1.shippingDa te=tbl3.shippin gDate) as itemCount
FROM tblSales as tbl1
GROUP BY tbl1.shippingDa te;
"Anne" <av*@telmore.ru > wrote in message news:<3f******* *************** *@dread16.news. tele.dk>...

Hi Ron

I'am not realy sure I understand.

An example:
I got theese 3 records:
[Date] [Ordernumber] [Itemnumber]

20031104 1234 12
20031104 4567 12
20031104 8910 9

Runing the query I want the following result:

Quarter # of orders # of different items
4 3 2

Best regards
Henry

"paii, Ron" <pa**@packairin c.com> skrev i en meddelelse
news:vq******** ****@corp.super news.com...

Use a Summation query grouped on Quarter, count Orders and Items.

"Anne" <av*@telmore.ru > wrote in message
news:3f******** *************** @dread16.news.t ele.dk...

Hi

I have got a table with some sales transactions. It has got 3 fields
1) Ordernumber
2) Item number
3) Shippingdate

Now I want to make ONE (not two) query that shows how many different orders quarterly, and at the same time how many different item numbers used in each quarter.

Each record has a different ordernumber, so it is just to count the lines and grouped by each quarterly, to find the number of orders, But my problem is, how do I count the number of differt itemnumbers at the same time. In other words I want to become the following result in one querry:

Quarter: # Orders: #Items
1 200 15
2 130 24
3 etc. etc.
4

Thanks in advance
Henry