Count Unique Fields Only

[Joe]

Hello everyone,

I am working on a report that shows our company shipment attainment
percentage.
When a customer makes an order from us, we issue them a "Promise Date",
which is basically the date we "promise" to ship the product by.

My report works by querying for all the orders for a given "promise
date". It then queries the shipping tables for how many orders shipped
on/before our promise date. These orders (promised/shipped) are then
counted in a seperated query and divided by eachother to get our
attainment percentage.

However, there is a problem! Sometimes, we will ship 1 order in
multiple shipments.

For example, Order #:100 might be for 100 pieces but we ship 4 boxes of
25 on the promise date.
This then shows up in my query for shipped orders 4 times appearing to
be 4 unique orders when in reality it is only 1 order.

What I want to do is query and count ONLY THE UNIQUE Order ID.

How do I go about doing this?

 

[Michel Walsh]

Hi,



SELECT COUNT(*)
FROM ( SELECT DISTINCT id
FROM myTable)


will count the distinct id. You can add group:


SELECT f1, COUNT(*)
FROM (SELECT f1, id
FROM myTable
GROUP BY f1, id)
GROUP BY f1


Hoping it may help,
Vanderghast, Access MVP