By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,710 Members | 1,613 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,710 IT Pros & Developers. It's quick & easy.

Count number of orders and number of different itemnumbers

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Use a Summation query grouped on Quarter, count Orders and Items.

"Anne" <av*@telmore.ru> wrote in message
news:3f***********************@dread16.news.tele.d k...
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

Nov 12 '05 #2

P: n/a
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**@packairinc.com> skrev i en meddelelse
news:vq************@corp.supernews.com...
Use a Summation query grouped on Quarter, count Orders and Items.

"Anne" <av*@telmore.ru> wrote in message
news:3f***********************@dread16.news.tele.d k...
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



Nov 12 '05 #3

P: n/a
assuming your table is called 'tblSales'

create a query "qryOrdersByDate"
SELECT tblSales.shippingDate, Count(*) AS orderCount
FROM tblSales
GROUP BY tblSales.shippingDate;

create a second query "qryUniqueItems"
SELECT DISTINCT tblsales.shippingDate, tblsales.itemNumber
FROM tblsales;

the final query "qrySales" will give you what you need
SELECT tbl1.shippingDate
, (select orderCount from qryOrdersByDate as tbl2 where
tbl1.shippingDate=tbl2.shippingDate) as orderCount
, (select count(itemNumber) from qryUniqueItems as tbl3
where tbl1.shippingDate=tbl3.shippingDate) as itemCount
FROM tblSales as tbl1
GROUP BY tbl1.shippingDate;
"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**@packairinc.com> skrev i en meddelelse
news:vq************@corp.supernews.com...
Use a Summation query grouped on Quarter, count Orders and Items.

"Anne" <av*@telmore.ru> wrote in message
news:3f***********************@dread16.news.tele.d k...
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


Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.