Help with counting only one time

[Cam]

Hi,

I am trying to run a query to count number of order received in the week
(date range). The problem is some order has more than 1 record with different
operation, so if the order 1100 has 10 operations, then it is counting 10
instead of 1.

Is there a way in query design to specify that the order is a unique value
only? Or how should I go about solving this? Thanks

 

[Allen Browne]

Use a subquery to get the unique records, and then count the number of
records in the subquery.

This example works in the Northwind sample database:
SELECT Count("*") AS HowMany
FROM (SELECT DISTINCT OrderID FROM [Order Details]) AS MySource;

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

If you want a function to return unique counts, see ECount() here:
Extended DCount()
at:
http://allenbrowne.com/ser-66.html

 

[Cam]

Allen,

Thanks for the suggestion, but I am clueless on what to do in the query even
after looking at the link you suggested. I thought maybe if I put some sample
data, then you might help me better with what I am trying to achieve.

Table with the following data:
Part Order Oper WC Date
AAA 123 1000 8951 1/2/08
AAA 123 2000 8951 1/3/08
AAA 123 3000 8951 1/7/08
AAA 123 4000 8951 1/3/08
ABC 205 5000 8951 1/4/08
ABC 205 6000 8951 1/8/08
ABZ 314 1000 8951 1/3/08
AAH 380 2000 8951 1/3/08

result of query data I like to achieve:
Part Order Oper WC Date
AAA 123 1000 8951 1/2/08
ABC 205 5000 8951 1/4/08
ABZ 314 1000 8951 1/3/08
AAH 380 2000 8951 1/3/08

Records that are excluded:
AAA 123 2000 8951 1/3/08
AAA 123 3000 8951 1/7/08
AAA 123 4000 8951 1/3/08
ABC 205 6000 8951 1/8/08

For order with multiple oper, it only returns one order with the lowest oper
number. Thank again.

Use a subquery to get the unique records, and then count the number of
records in the subquery.

This example works in the Northwind sample database:
SELECT Count("*") AS HowMany
FROM (SELECT DISTINCT OrderID FROM [Order Details]) AS MySource;

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

If you want a function to return unique counts, see ECount() here:
Extended DCount()
at:
http://allenbrowne.com/ser-66.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I am trying to run a query to count number of order received in the week
(date range). The problem is some order has more than 1 record with
different
operation, so if the order 1100 has 10 operations, then it is counting 10
instead of 1.

Is there a way in query design to specify that the order is a unique value
only? Or how should I go about solving this? Thanks

 

[Allen Browne]

Ah: so you don't just want to know how many of each row there are.
You want to exclude all but the first row for each order?

You will need a primary key in your table to achieve this. In the following
example, I will assume there is a primary key field named ID, and the table
is called Table1.

SELECT Table1.*
FROM Table1
WHERE ID = (SELECT Min(ID) AS MinOfID
FROM Table1 AS Dupe
WHERE Dupe.[Order] = Table1.[Order]);

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for the suggestion, but I am clueless on what to do in the query
even
after looking at the link you suggested. I thought maybe if I put some
sample
data, then you might help me better with what I am trying to achieve.

Table with the following data:
Part Order Oper WC Date
AAA 123 1000 8951 1/2/08
AAA 123 2000 8951 1/3/08
AAA 123 3000 8951 1/7/08
AAA 123 4000 8951 1/3/08
ABC 205 5000 8951 1/4/08
ABC 205 6000 8951 1/8/08
ABZ 314 1000 8951 1/3/08
AAH 380 2000 8951 1/3/08

result of query data I like to achieve:
Part Order Oper WC Date
AAA 123 1000 8951 1/2/08
ABC 205 5000 8951 1/4/08
ABZ 314 1000 8951 1/3/08
AAH 380 2000 8951 1/3/08

Records that are excluded:
AAA 123 2000 8951 1/3/08
AAA 123 3000 8951 1/7/08
AAA 123 4000 8951 1/3/08
ABC 205 6000 8951 1/8/08

For order with multiple oper, it only returns one order with the lowest
oper
number. Thank again.

Use a subquery to get the unique records, and then count the number of
records in the subquery.

This example works in the Northwind sample database:
SELECT Count("*") AS HowMany
FROM (SELECT DISTINCT OrderID FROM [Order Details]) AS MySource;

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

If you want a function to return unique counts, see ECount() here:
Extended DCount()
at:
http://allenbrowne.com/ser-66.html

Hi,

I am trying to run a query to count number of order received in the
week
(date range). The problem is some order has more than 1 record with
different
operation, so if the order 1100 has 10 operations, then it is counting
10
instead of 1.

Is there a way in query design to specify that the order is a unique
value
only? Or how should I go about solving this? Thanks

 

[Cam]

Allen, thanks for your help it looked like it is working, but for whatever
reason, it is taking a long time openning and running the query. Even when I
try to import this data from Excel.

Ah: so you don't just want to know how many of each row there are.
You want to exclude all but the first row for each order?

You will need a primary key in your table to achieve this. In the following
example, I will assume there is a primary key field named ID, and the table
is called Table1.

SELECT Table1.*
FROM Table1
WHERE ID = (SELECT Min(ID) AS MinOfID
FROM Table1 AS Dupe
WHERE Dupe.[Order] = Table1.[Order]);

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for the suggestion, but I am clueless on what to do in the query
even
after looking at the link you suggested. I thought maybe if I put some
sample
data, then you might help me better with what I am trying to achieve.

Table with the following data:
Part Order Oper WC Date
AAA 123 1000 8951 1/2/08
AAA 123 2000 8951 1/3/08
AAA 123 3000 8951 1/7/08
AAA 123 4000 8951 1/3/08
ABC 205 5000 8951 1/4/08
ABC 205 6000 8951 1/8/08
ABZ 314 1000 8951 1/3/08
AAH 380 2000 8951 1/3/08

result of query data I like to achieve:
Part Order Oper WC Date
AAA 123 1000 8951 1/2/08
ABC 205 5000 8951 1/4/08
ABZ 314 1000 8951 1/3/08
AAH 380 2000 8951 1/3/08

Records that are excluded:
AAA 123 2000 8951 1/3/08
AAA 123 3000 8951 1/7/08
AAA 123 4000 8951 1/3/08
ABC 205 6000 8951 1/8/08

For order with multiple oper, it only returns one order with the lowest
oper
number. Thank again.

Use a subquery to get the unique records, and then count the number of
records in the subquery.

This example works in the Northwind sample database:
SELECT Count("*") AS HowMany
FROM (SELECT DISTINCT OrderID FROM [Order Details]) AS MySource;

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

If you want a function to return unique counts, see ECount() here:
Extended DCount()
at:
http://allenbrowne.com/ser-66.html

Hi,

I am trying to run a query to count number of order received in the
week
(date range). The problem is some order has more than 1 record with
different
operation, so if the order 1100 has 10 operations, then it is counting
10
instead of 1.

Is there a way in query design to specify that the order is a unique
value
only? Or how should I go about solving this? Thanks

 

[Allen Browne]

Here's some other approaches to the same issue:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thanks for your help it looked like it is working, but for whatever
reason, it is taking a long time openning and running the query. Even when
I
try to import this data from Excel.

Ah: so you don't just want to know how many of each row there are.
You want to exclude all but the first row for each order?

You will need a primary key in your table to achieve this. In the
following
example, I will assume there is a primary key field named ID, and the
table
is called Table1.

SELECT Table1.*
FROM Table1
WHERE ID = (SELECT Min(ID) AS MinOfID
FROM Table1 AS Dupe
WHERE Dupe.[Order] = Table1.[Order]);

 

[Cam]

Allen,

I tried the cascading query method. Data looked right, but I am stilling
having problem of exporting/importing the data to/from Excel, taking long
long time to where it's not getting data.

Here's some other approaches to the same issue:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thanks for your help it looked like it is working, but for whatever
reason, it is taking a long time openning and running the query. Even when
I
try to import this data from Excel.

Ah: so you don't just want to know how many of each row there are.
You want to exclude all but the first row for each order?

You will need a primary key in your table to achieve this. In the
following
example, I will assume there is a primary key field named ID, and the
table
is called Table1.

SELECT Table1.*
FROM Table1
WHERE ID = (SELECT Min(ID) AS MinOfID
FROM Table1 AS Dupe
WHERE Dupe.[Order] = Table1.[Order]);