Help with counting only one time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cam - 28 Feb 2008 14:27 GMT

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

Reply to this Message

Allen Browne - 28 Feb 2008 14:40 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi,
>
[quoted text clipped - 6 lines]
> 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

Reply to this Message

Cam - 28 Feb 2008 16:41 GMT

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.
[quoted text clipped - 21 lines]
> > 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

Reply to this Message

Allen Browne - 29 Feb 2008 00:34 GMT

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]);

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen,
>
[quoted text clipped - 60 lines]
>> > value
>> > only? Or how should I go about solving this? Thanks

Reply to this Message

Cam - 29 Feb 2008 13:41 GMT

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?
[quoted text clipped - 73 lines]
> >> > value
> >> > only? Or how should I go about solving this? Thanks

Reply to this Message

Allen Browne - 29 Feb 2008 14:30 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
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
[quoted text clipped - 15 lines]
>> FROM Table1 AS Dupe
>> WHERE Dupe.[Order] = Table1.[Order]);

Reply to this Message

Cam - 29 Feb 2008 15:48 GMT

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
[quoted text clipped - 18 lines]
> >> FROM Table1 AS Dupe
> >> WHERE Dupe.[Order] = Table1.[Order]);

Reply to this Message