473,322 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Count number of orders and number of different itemnumbers

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
3 5083
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Prem | last post by:
To all gurus, I am developing an application in which i want to show the number of rows returned by the query. e.g. Select Categories.CategoryName, Products.ProductName,...
5
by: Mike Bannon | last post by:
Hi All We have an order processing database which includes the standard Order Header/Order Lines tables. I'm trying to write a query to get the average number of lines per order over a given...
2
by: PaulMac | last post by:
This seems so easy....change the join to show all records, but the zero records still do not show I want to join 2 tables....basically Customers and Orders....get the total number of orders for...
2
by: Paula | last post by:
A query contains the fields CustomerID, CustomerName, OrderID, OrderDate and ItemID. The query is based on a customers table, orders table, and order details table. The orders table has a foreign...
10
by: Robert | last post by:
How do you get an accurate count of the number of records returned from a query when using linked tables. I have an access 2003 database as a front end to another access 2003 database that...
15
by: Hexman | last post by:
Hello All, How do I limit the number of detail records selected in a Master-Detail set using SQL? I want to select all master records for a date, but only the first 3 records for the details...
2
by: rn5a | last post by:
A SQL Server 2005 stored procedure expects a parameter 'UserID' depending upon which it retrieves the no. of records & OrderIDs corresponding to the 'UserID' from a DB table (note that OrderID &...
5
by: whitsey | last post by:
Here is what I have: SELECT (SELECT COUNT(*) AS SEARCHES FROM SEARCHES INNER JOIN GROUPS ON SEARCHES.SITE_ID = GROUPS.SITE_ID WHERE
5
by: Nasher | last post by:
How do you create a field on a form that counts the selected records, only if a certain word is in the field. Records are selected via a combo box on a form header. I then have tabs below that...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.