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