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" <avi@telmore.ru> wrote in message news:<3fa76338$0$27422$edfadb0f@dread16.news.tele. dk>...[color=blue]
> 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" <paii@packairinc.com> skrev i en meddelelse
> news:vqctit2uqrog18@corp.supernews.com...[color=green]
> > Use a Summation query grouped on Quarter, count Orders and Items.
> >
> > "Anne" <avi@telmore.ru> wrote in message
> > news:3fa670c4$0$27362$edfadb0f@dread16.news.tele.d k...[color=darkred]
> > > 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[/color][/color]
> orders[color=green][color=darkred]
> > > quarterly, and at the same time how many different item numbers used in[/color][/color]
> each[color=green][color=darkred]
> > > quarter.
> > >
> > > Each record has a different ordernumber, so it is just to count the[/color][/color]
> lines[color=green][color=darkred]
> > > and grouped by each quarterly, to find the number of orders, But my[/color][/color]
> problem[color=green][color=darkred]
> > > is, how do I count the number of differt itemnumbers at the same time.[/color][/color]
> In[color=green][color=darkred]
> > > 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
> > >
> > >
> > >[/color]
> >
> >[/color][/color]
